From 77c6d9e12e2e7b76e2d8265cd28282f46bdbd5a9 Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@baculasystems.com>
Date: Thu, 8 Apr 2021 10:37:34 +0200
Subject: [PATCH] Add UnixTime to JSON output of the .jlist events command

---
 bacula/src/cats/sql_cmds.c |  9 +++++++++
 bacula/src/cats/sql_cmds.h |  1 +
 bacula/src/cats/sql_list.c | 12 ++++++++++++
 3 files changed, 22 insertions(+)

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index d892e1e23..43c6c2629 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -1048,6 +1048,15 @@ const char *expired_volumes[] = {
      " %s "
 };
 
+const char *to_unix_timestamp[] = {
+   /* MySQL */
+   "UNIX_TIMESTAMP(%s)",
+   /* PostgreSQL */
+   "EXTRACT(EPOCH FROM %s)",
+   /* SQLite */
+   "strftime('%%s', %s)" 
+};
+
 const char *expires_in[] = {
    /* MySQL */
    "(GREATEST(0, CAST(UNIX_TIMESTAMP(LastWritten) + Media.VolRetention AS SIGNED) - UNIX_TIMESTAMP(NOW())))",
diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h
index bac92eaee..fc13a05cd 100644
--- a/bacula/src/cats/sql_cmds.h
+++ b/bacula/src/cats/sql_cmds.h
@@ -45,6 +45,7 @@ extern const char CATS_IMP_EXP *create_temp_basefile[];
 extern const char CATS_IMP_EXP *create_temp_new_basefile[];
 extern const char CATS_IMP_EXP *del_MAC;
 extern const char CATS_IMP_EXP *drop_deltabs[];
+extern const char CATS_IMP_EXP *to_unix_timestamp[];
 extern const char CATS_IMP_EXP *expired_volumes[];
 extern const char CATS_IMP_EXP *fill_jobhisto;
 extern const char CATS_IMP_EXP *get_restore_objects;
diff --git a/bacula/src/cats/sql_list.c b/bacula/src/cats/sql_list.c
index ef7dc87d7..65b8ea203 100644
--- a/bacula/src/cats/sql_list.c
+++ b/bacula/src/cats/sql_list.c
@@ -606,6 +606,18 @@ void BDB::bdb_list_events_records(JCR *jcr, EVENTS_DBR *rec,
         where.c_str(),
         rec->order ? "DESC" : "ASC",
         str_limit.c_str());
+
+   } else if (type == JSON_LIST) {
+      Mmsg(tmp2, to_unix_timestamp[bdb_get_type_index()], "EventsTime");
+      Mmsg(cmd,
+   "SELECT EventsTime AS Time, %s AS UnixTime, EventsCode AS Code, EventsDaemon AS Daemon, EventsRef AS Ref, EventsType AS Type, EventsSource AS Source, EventsText AS Events "
+     "FROM Events "
+    "%s ORDER BY Events.EventsTime %s %s",
+        tmp2.c_str(),
+        where.c_str(),
+        rec->order ? "DESC" : "ASC",
+        str_limit.c_str());
+
    } else {
       Mmsg(cmd,
    "SELECT EventsTime AS Time, EventsCode AS Code, EventsDaemon AS Daemon, EventsRef AS Ref, EventsType AS Type, EventsSource AS Source, EventsText AS Events "
-- 
2.47.3