Hi all
I am trying to figure out, how to get the following output
I have SQl query, looking at the actions taken by BigFix admins, which are listed in epoch time,
So I want to convert the time stamp from epoch to Human readable, search for only those actions which are N days old (today and 15 days ago)
and I wish to output both the time stamp AND the action history for those time stamps.
I have this, which will output ALL lines with the time stamp desired and the actions, however I want ONLY those in bold (15 days ago OR today)
((unique values of (it * second + “01 Jan 1970 00:00:00 -0000” as local time) of (it as integer) of (preceding texts of firsts “,” of it)), (following texts of firsts “,” of it)) of (rows of statements “SELECT IssuedTime,ActionName, IssuerName FROM ACTION_HISTORY where IssuerName not like ‘%25ServerSigningCertificate%25’ AND IssuerName not like ‘%25C074_%25’ AND ActionName not like ‘%25Subscribe to Site%25’” of sqlite databases of files “ActionHistory.db” of folders “__Global” of data folders of client as string)
A: ( Tue, 02 Oct 2018 10:00:05 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:06 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:07 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:08 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:09 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:11 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:13 +0100 ), (ACTION, ID Name )
A: ( Tue, 02 Oct 2018 10:00:14 +0100 ), (ACTION, ID Name )
A: ( Fri, 05 Oct 2018 14:48:16 +0100 ), (ACTION, ID Name )
A: ( Fri, 05 Oct 2018 14:49:40 +0100 ), (ACTION, ID Name )
A: ( Fri, 05 Oct 2018 14:52:03 +0100 ), (ACTION, ID Name )
A: ( Wed, 17 Oct 2018 04:31:48 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 12:55:57 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 12:58:53 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 13:16:48 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 13:48:11 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 13:50:23 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 14:05:48 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 14:12:27 +0100 ), (ACTION, ID Name )
A: ( Tue, 23 Oct 2018 14:14:03 +0100 ), (ACTION, ID Name )
A: ( Fri, 02 Nov 2018 07:50:29 +0100 ), (ACTION, ID Name )
I have this which will output the TIME STAMPS I want (in this case 15 days ago)
Q: (it) whose(it contains (current date as string) OR it contains ((current date - 15 *day) as string)) of (unique values of (it as string) of (it * second + “01 Jan 1970 00:00:00 -0000” as local time) of (it as integer) of (preceding texts of firsts “,” of it)) of (rows of statements “SELECT IssuedTime,ActionName, IssuerName FROM ACTION_HISTORY where IssuerName not like ‘%25ServerSigningCertificate%25’ AND IssuerName not like ‘%25C074_%25’ AND ActionName not like ‘%25Subscribe to Site%25’” of sqlite databases of files “ActionHistory.db” of folders “__Global” of data folders of client as string)
A: Tue, 23 Oct 2018 12:55:57 +0100
A: Tue, 23 Oct 2018 12:58:53 +0100
A: Tue, 23 Oct 2018 13:16:48 +0100
A: Tue, 23 Oct 2018 13:48:11 +0100
A: Tue, 23 Oct 2018 13:50:23 +0100
A: Tue, 23 Oct 2018 14:05:48 +0100
A: Tue, 23 Oct 2018 14:12:27 +0100
A: Tue, 23 Oct 2018 14:14:03 +0100
However, I am trying to figure out how to combine the timestamps I want (15 days ago) and combine them with the ACTION, ID output which is given with this
), (following texts of firsts “,” of it))
I have tried adding this line to the second relevance, however it gives me singular expression required
any help would be greatly appreciated.