Comparing a Date/Time String to now

Content Authoring
1

I’m trying to find systems that have not had a successful BigFix Inventory Scan in the last 7 days.

I’m thinking of using "Time of Last Successful Scan for Software Identification Tag Files (Software Scan Status)"
from Software Scan Status Analysis from BigFix Inventory External Site for comparison. That gives me date/time string in the format:

YYYY-MM-DD hh:mm:ss UTC

and comparing it to now - 7 * day

I did the following to try to get the string to a time format, but this is as far as I got…

q: (first 4 of it as integer * year) of "2019-08-09 05:23:15 UTC"
A: 2019 years
I: singular number of months

q: ((last 2 of preceding texts of last "-" of it) as integer * day) of "2019-08-09 05:23:15 UTC" 
A: 8 days
I: singular time interval

q: ((first 2 of following texts of last "-" of it) as integer * month) of "2019-08-09 05:23:15 UTC"
A: 9 months
I: singular number of months

q: ((first 2 of following texts of first " " of it as integer * hour) + ((first 2 of following texts of first ":" of it) as integer * minute) + ((first 2 of following texts of last ":" of it) as integer * second)) of "2019-08-09 05:23:15 UTC"
A: 05:23:15
I: singular time interval

Does anyone have an idea of what can be done to get this comparison working?

I’m trying to find a way to run another scan on machines that were missed in the last 7 days without scanning everything again (~100,000 machines)

Thanks for any help,
Bob_K

1 Like
2

You need to get a string like this:

"2019-08-06 03:15:19 UTC"

to a string like this:

"06 Aug 2019 03:15:19 +0000"

The final relevance:

Q: now - (((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by "-" of (tuple string item 0 of it))) of it & " " & (tuple string item 1 of it & " " & universal time zone as string)) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC")) as local time > 7*day
A: True
I: singular boolean

Here’s how it breaks down:

Create the date component:

Q:  ((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by "-" of (tuple string item 0 of it))) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC"))
A: 06 Aug 2019
I: singular string

Create the time and timezone component:

Q: ((tuple string item 1 of it & " " & universal time zone as string)) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC"))
A: 03:15:19 +0000
I: singular string

Combine to create the desired string:

Q: (((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by "-" of (tuple string item 0 of it))) of it & " " & (tuple string item 1 of it & " " & universal time zone as string)) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC"))
A: 06 Aug 2019 03:15:19 +0000
I: singular string

Cast the string as local time:

Q: (((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by "-" of (tuple string item 0 of it))) of it & " " & (tuple string item 1 of it & " " & universal time zone as string)) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC")) as local time
A: Mon, 05 Aug 2019 20:15:19 -0700
I: singular time

Finally, check if the difference from now is greater than 7 days

Q: now - ( ((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by "-" of (tuple string item 0 of it))) of it & " " & (tuple string item 1 of it & " " & universal time zone as string)) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC")) as local time > 7*day
A: True
I: singular boolean
2 Likes
3

Thanks very much for the detailed description. I have a similar problem and tried to work with the above solution. Unfortunately, “local time” seems not to be working in webreports ?

4

@itsmpro92

Thank you!!!

@matthias.wulkow

Try taking off as local time and see if that works in Web Reports (might have to tweak a bit since the time will be in UTC, but you won’t be getting an error)

Thanks,
Bob_K

5

@matthias.wulkow, are you using a custom report?

Try this:

 <?relevance (((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by "-" of (tuple string item 0 of it))) of it & " " & (tuple string item 1 of it & " " & universal time zone as string)) of (concatenation ", " of (substrings separated by " " of "2019-08-06 03:15:19 UTC")) as local time ?>

Results of Preview:

Mon, 05 Aug 2019 20:15:19 -0700
6

Thanks for your replies. Yes, I’m working with a custom report. For now I got stuck on replacing an inspector value with the value of a custom property:

Using:
(value of results from (bes property whose (name of it = “System Last Boot Time”)) of it) of bes computers
Sun, 16 Dec 2018 01:14:34 +0200

the output format looks similar to output from
now
Thu, 22 Aug 2019 13:36:11 +0200

Trying an inspector works fine:
((now - last report time of it) / day ) of bes computers
0

Trying to replace “last report time of it” with my “value of …” drives me crazy:

(
(
(now -
(value of results from
(bes property whose
(name of it = “VWFS System Last Boot Time”)
)
of it)
/ day)
)
) of bes computers

The operator “divide” is not defined. I’ve tried to play around with brackets or formating the value “as time” but without success :disappointed_relieved:

7

You’re encountering an area where, well, I think maybe is a little bit of a cheat.

BES Property Result values are always a simple type (I don’t recall whether they’re either strings or integers, or only strings). But no ‘time’, those all are stored as string.

Except for ‘Last Report Time’. That one is automatically typed as a time when you try to use it. Everything else must use ‘as X’ to cast its type.

Assuming your result fits the time format, try

(
(
(now -
((value of results from
(bes property whose
(name of it = “VWFS System Last Boot Time”)
)
of it) as time)
/ day)
)
) of bes computers
1 Like
8

Same issue “The operator “divide” is not defined.”

9

What is the actual value returned from “VWFS System Last Boot Time”?

10

Sun, 16 Dec 2018 01:14:34 +0200

11

Sorry - I should have seen it in your earlier post :man_facepalming:.

12

Try changing results to result

((now - value of result from (bes property whose (name of it is "VWFS System Last Boot Time")) of it as time) / day) of bes computers

I had challenges with this if I didn’t put a filter on bes computers to eliminate one that hasn’t reported back on my test property yet.

13

Unfortunately, no difference using “result” or “results”. Seems like working with bes properties in webreports is not the best idea.

14

You’re creating a custom report, right? This is what I worked out in my environment, using a similar property.

<?relevance

(it, (now - it as time) / day) of (value of result from (bes property whose (name of it is "TImeValue")) of it) of bes computers

?>

Results:

( Sat, 15 Dec 2018 16:14:34 -0700 ), 261
( Sat, 15 Dec 2018 16:14:34 -0700 ), 261
...
1 Like
15

Works like a charm, thanks very much.
It’s a little bit confusing, when to use singular “value of result” and plural “values of property results”.

16

So we haven’t done much explanation in this thread, just “try this and try that” so I can understand your frustration.

What it comes down to is handling the edge cases. If we use a singular “(value of result of property “X” of it)”, there must be exactly one result for each “it” (presumably each computer).
If a computer hasn’t yet reported a result for that property, we’d have an error message “Singular expression refers to non-existant object”, and the query would return nothing at all - not even results for those computers that had reported.

Whereas if we use plurals ‘values of results of properties “X”’, a plural can be any number of results (including 0). A computer that had not reported for the property, would have 0 results and be simply discarded from the query.

So that’s why we often use plurals in these queries. But then you get into a situation where a lot of manipulations don’t make sense on a plural value. You can’t interpret an operation like (1;3;5) + 10 ; but you can split it out into singular operations by manipulation such as (it + 10) of (1;3;5). Likewise now - ("Sat, 15 Dec 2018 16:14:34 -0700" as time; "Sat, 08 Dec 2018 15:14:34 -0700" as time) can’t be interpreted, but using a parentheses and “it” substitution allows you to handle the plural results in a loop, doing one result at a time.

When our query specifies a plural result like ‘values of…’, the interpreter won’t guess whether there’s actually only one result for each, it simply goes into plural operations mode and you have to handle each result as if it were plural, even if the result count is 1.

4 Likes
17

Thanks for this very good description, I hope to have a better understanding now.

18

This is a great thread. I’m attempting to do something similar, but an stuck on the syntax. My goal is to be able to compare log files based on the string in their name, not the file modification or creation dates. There may be one or multiple files on a given date. To do that, I need to first convert to a date/time format so I can then do a comparison. Here is what I have so far. I get the right answer for a single file, but not for the list of files. Having a brain fart, what am I doing wrong?

File name format is somefile-year-month-day.digit.log

Q: ((tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of (concatenation ", " of substrings separated by “-” of (tuple string items 0 of it))) of (concatenation ", " of (substrings separated by " " of (substrings after “somefile-” of substrings before “.” of names of files whose (name of it starts with “somefile-”) of folder “D:\somefolder”)))
A: 09 Feb 2022
T: 40.377 ms

q: substrings after “somefile-” of substrings before “.” of names of files whose (name of it starts with “somefile-”) of folder "D:\somefolder"
A: 2022-02-09
A: 2022-02-10
A: 2022-02-11
A: 2022-02-12
A: 2022-02-13
A: 2022-02-14
A: 2022-02-15
A: 2022-02-15

19

Is this the format of the string result you are after @JonL?

Q: (tuple string item 2 of it & " " & tuple string item 1 of it as integer as month as three letters & " " & tuple string item 0 of it) of ((concatenation ", " of substrings separated by "-" of it) of (substrings after "somefile-" of substrings before "." of names of files whose (name of it starts with "somefile-") of folder "C:\TEMP\files"))
A: 01 Mar 2022
A: 02 Mar 2022
A: 03 Mar 2022
A: 04 Mar 2022
A: 05 Mar 2022
A: 06 Mar 2022
A: 07 Mar 2022
A: 09 Mar 2022
T: 1.872 ms
I: plural string
1 Like
20

Yes, thank you @SLB!