Maintain node's last connected timestamp in the db (#15979)

* Maintain node's last connected timestamp in the db

* Rebase -- switch to version database v14

5 files changed, 35 insertions, 9 deletions

diff --git a/database/rrd.h b/database/rrd.h
index b8a9a69c21..20f170de1c 100644
--- a/database/rrd.h
+++ b/database/rrd.h
@@ -1178,6 +1178,7 @@ struct rrdhost {
     // ------------------------------------------------------------------------
     // streaming of data from remote hosts - rrdpush receiver
 
+    time_t last_connected;                          // last time child connected (stored in db)
     time_t child_connect_time;                      // the time the last sender was connected
     time_t child_last_chart_command;                // the time of the last CHART streaming command
     time_t child_disconnected_time;                 // the time the last sender was disconnected
diff --git a/database/rrdhost.c b/database/rrdhost.c
index 5d54b90045..2dac61de91 100644
--- a/database/rrdhost.c
+++ b/database/rrdhost.c
@@ -336,6 +336,7 @@ int is_legacy = 1;
 
     if (likely(!archived)) {
         rrdfunctions_host_init(host);
+        host->last_connected = now_realtime_sec();
         host->rrdlabels = rrdlabels_create();
         rrdhost_initialize_rrdpush_sender(
             host, rrdpush_enabled, rrdpush_destination, rrdpush_api_key, rrdpush_send_charts_matching);
@@ -662,6 +663,8 @@ static void rrdhost_update(RRDHOST *host
     if(!host->rrdvars)
         host->rrdvars = rrdvariables_create();
 
+    host->last_connected = now_realtime_sec();
+
     if (rrdhost_flag_check(host, RRDHOST_FLAG_ARCHIVED)) {
         rrdhost_flag_clear(host, RRDHOST_FLAG_ARCHIVED);
 
diff --git a/database/sqlite/sqlite_db_migration.c b/database/sqlite/sqlite_db_migration.c
index a0815d4feb..901a9e8817 100644
--- a/database/sqlite/sqlite_db_migration.c
+++ b/database/sqlite/sqlite_db_migration.c
@@ -104,6 +104,11 @@ const char *database_migrate_v12_v13_hash[] = {
     NULL
 };
 
+const char *database_migrate_v13_v14[] = {
+    "ALTER TABLE host ADD last_connected INT NOT NULL DEFAULT 0;",
+    NULL
+};
+
 static int do_migration_v1_v2(sqlite3 *database, const char *name)
 {
     UNUSED(name);
@@ -365,6 +370,17 @@ static int do_migration_v12_v13(sqlite3 *database, const char *name)
     return rc;
 }
 
+static int do_migration_v13_v14(sqlite3 *database, const char *name)
+{
+    netdata_log_info("Running \"%s\" database migration", name);
+
+    if (!column_exists_in_table("host", "last_connected"))
+        return init_database_batch(database, &database_migrate_v13_v14[0]);
+
+    return 0;
+}
+
+
 static int do_migration_noop(sqlite3 *database, const char *name)
 {
     UNUSED(database);
@@ -421,6 +437,7 @@ DATABASE_FUNC_MIGRATION_LIST migration_action[] = {
     {.name = "v10 to v11",  .func = do_migration_v10_v11},
     {.name = "v11 to v12",  .func = do_migration_v11_v12},
     {.name = "v12 to v13",  .func = do_migration_v12_v13},
+    {.name = "v13 to v14",  .func = do_migration_v13_v14},
     // the terminator of this array
     {.name = NULL, .func = NULL}
 };
diff --git a/database/sqlite/sqlite_functions.c b/database/sqlite/sqlite_functions.c
index dd224045b7..a89fb93de2 100644
--- a/database/sqlite/sqlite_functions.c
+++ b/database/sqlite/sqlite_functions.c
@@ -4,7 +4,7 @@
 #include "sqlite3recover.h"
 #include "sqlite_db_migration.h"
 
-#define DB_METADATA_VERSION 13
+#define DB_METADATA_VERSION 14
 
 const char *database_config[] = {
     "CREATE TABLE IF NOT EXISTS host(host_id BLOB PRIMARY KEY, hostname TEXT NOT NULL, "
@@ -14,7 +14,7 @@ const char *database_config[] = {
     "memory_mode INT DEFAULT 0, abbrev_timezone TEXT DEFAULT '', utc_offset INT NOT NULL DEFAULT 0,"
     "program_name TEXT NOT NULL DEFAULT 'unknown', program_version TEXT NOT NULL DEFAULT 'unknown', "
     "entries INT NOT NULL DEFAULT 0,"
-    "health_enabled INT NOT NULL DEFAULT 0);",
+    "health_enabled INT NOT NULL DEFAULT 0, last_connected INT NOT NULL DEFAULT 0);",
 
     "CREATE TABLE IF NOT EXISTS chart(chart_id blob PRIMARY KEY, host_id blob, type text, id text, name text, "
     "family text, context text, title text, unit text, plugin text, module text, priority int, update_every int, "
diff --git a/database/sqlite/sqlite_metadata.c b/database/sqlite/sqlite_metadata.c
index 0751026e7a..30725aac7c 100644
--- a/database/sqlite/sqlite_metadata.c
+++ b/database/sqlite/sqlite_metadata.c
@@ -20,13 +20,14 @@
 
 #define DELETE_DIMENSION_UUID   "DELETE FROM dimension WHERE dim_id = @uuid;"
 
-#define SQL_STORE_HOST_INFO "INSERT OR REPLACE INTO host " \
-        "(host_id, hostname, registry_hostname, update_every, os, timezone," \
-        "tags, hops, memory_mode, abbrev_timezone, utc_offset, program_name, program_version," \
-        "entries, health_enabled) " \
-        "values (@host_id, @hostname, @registry_hostname, @update_every, @os, @timezone, @tags, @hops, @memory_mode, " \
-        "@abbrev_timezone, @utc_offset, @program_name, @program_version, " \
-        "@entries, @health_enabled);"
+#define SQL_STORE_HOST_INFO                                                                                            \
+    "INSERT OR REPLACE INTO host "                                                                                     \
+    "(host_id, hostname, registry_hostname, update_every, os, timezone, tags, hops, memory_mode, "                     \
+    "abbrev_timezone, utc_offset, program_name, program_version,"                                                      \
+    "entries, health_enabled, last_connected) "                                                                        \
+    "VALUES (@host_id, @hostname, @registry_hostname, @update_every, @os, @timezone, @tags, @hops, @memory_mode, "     \
+    "@abbrev_timezone, @utc_offset, @program_name, @program_version, "                                                 \
+    "@entries, @health_enabled, @last_connected);"
 
 #define SQL_STORE_CHART "insert or replace into chart (chart_id, host_id, type, id, " \
     "name, family, context, title, unit, plugin, module, priority, update_every , chart_type , memory_mode , " \
@@ -364,6 +365,10 @@ static int store_host_metadata(RRDHOST *host)
     if (unlikely(rc != SQLITE_OK))
         goto bind_fail;
 
+    rc = sqlite3_bind_int(res, ++param, (int ) host->last_connected);
+    if (unlikely(rc != SQLITE_OK))
+        goto bind_fail;
+
     int store_rc = sqlite3_step_monitored(res);
     if (unlikely(store_rc != SQLITE_DONE))
         error_report("Failed to store host %s, rc = %d", rrdhost_hostname(host), rc);