Relevance help; convert search and combine clauses

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.

1 Like

Never tried this until now but it looks like strftime might be of help to convert now to an epoch time then you can query where IssuedTime is greater than now - 15 days.

((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 IssuedTime > strftime('%25s', 'now') - 1296000 AND 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)

You could also cast the epoch time to datetime in the SELECT statement.

Q: (rows of statements "SELECT DateTime(IssuedTime, 'unixepoch', 'localtime'),ActionName, IssuerName FROM ACTION_HISTORY where IssuedTime > strftime('%25s', 'now') - 1296000 AND 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)

Try moving your time query into the select statement by calculating the seconds?

q: rows of statements ("SELECT IssuedTime,ActionName, IssuerName FROM ACTION_HISTORY where IssuedTime > " & (((now - 60*day) - "01 Jan 1970 00:00:00 -0000" as local time)/second) as string) of sqlite databases of files "ActionHistory.db" of folders "__Global" of data folders of client as string

Then move to parsing the smaller result set?

Also, you can use Columns of the Rows, if you don’t cast to string as early…

q: ((column 0 of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time,column 1 of it, column 2 of it, column 3 of it) of (rows of statements ("SELECT IssuedTime,ActionName, IssuerName, ActionID FROM ACTION_HISTORY where IssuedTime > " & (((now - 60*day) - "01 Jan 1970 00:00:00 -0000" as local time)/second) as string) of sqlite databases of files "ActionHistory.db" of folders "__Global" of data folders of client)

You can also keep with your initial plan and just add your Whose clause after your tuple:

q: ((column 0 of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time,column 1 of it, column 2 of it, column 3 of it) whose (now - item 0 of it < 60* day) of (rows of statements ("SELECT IssuedTime,ActionName, IssuerName, ActionID FROM ACTION_HISTORY") of sqlite databases of files "ActionHistory.db" of folders "__Global" of data folders of client)

one more “nicety” for us - you can reference the column names by name instead of by integer index and you can add your whose directly to the rows, to allow you to optionally filter outside of the SQL portion.

q: ((column "IssuedTime" of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time,column "ActionName" of it, column "IssuerName" of it, column "ActionID" of it) of rows whose ((now -((column "IssuedTime" of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time) < 15* day) AND (column "IssuerName" of it as string does not contain "ServerSigningCertificate") AND (column "ActionName" of it as string does not contain "Subscribe to Site")) of statements ("SELECT IssuedTime,ActionName, IssuerName, ActionID FROM ACTION_HISTORY") of sqlite databases of files "ActionHistory.db" of folders

1 Like

in re-reading your initial query - you wanted exactly 15 days ago, or today… updated to accomodate

q: ((column "IssuedTime" of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time,column "ActionName" of it, column "IssuerName" of it, column "ActionID" of it) of rows whose (((it/day = 0 or it/day = 15) of (now -((column "IssuedTime" of it as string as integer) * second + "01 Jan 1970 00:00:00 -0000" as local time))) AND (column "IssuerName" of it as string does not contain "ServerSigningCertificate") AND (column "ActionName" of it as string does not contain "Subscribe to Site")) of statements ("SELECT IssuedTime,ActionName, IssuerName, ActionID FROM ACTION_HISTORY") of sqlite databases of files "ActionHistory.db" of folders "__Global" of data folders of client

1 Like

Hi brolly, this is perfect, exactly what I needed. Thanks for your help.

Hi SLB, this is good as well, - 1296000 = 15 days in seconds so to decrease that I could do 60second * 60minute * 24hour * x numberday (5 for 5 days)? Yes?

One thing I did notice.SLB, your query is 3 times faster than Brolly. This will most likely be the deciding factor in using SLBs query, even though both are great answers

Yes, you can change the 1296000 to however many seconds meets your requirements. I think the evaluation time difference is only due the fact there is was more data from the SQLite query to process in Brolly’s example.

Rob, you nailed it - I took all the SQL where portion out and was flitering the much larger sql output after the query. Apparently SQLLite is faster at that step.

But, I wonder if the 1296000 seconds is to granular a filter? I think it might actually want
between 1296000 and 1382400 seconds, since a day is 86,400 seconds long?

Very good point…in fact I also hadn’t factored in the 15 days ago or today. This might cater for both with the SQLite query.

Q: ((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 ((IssuedTime > strftime('%25s', 'now') - 1382400 AND IssuedTime < strftime('%25s', 'now') - 1296000) or (IssuedTime > strftime('%25s', 'now') - 86400)) AND 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)

1 Like