Looks like the strange date results above (e.g.: 01cb3f0bae5d5621) are because they are NTP timestamps…
NTP timestamps are represented as a 64-bit fixed-point number, in seconds relative to 0000 UT on 1 January 1900. (Note: Epoch time is relative to 1/1/1970-00:00h)
This format allows convenient multiple-precision arithmetic and conversion to Time Protocol representation (seconds), but does complicate the conversion to ICMP Timestamp message representation (milliseconds).
I have several hundred machines - the majority of which are Windows Server 2008 - which use this format. I’ve looked around a bit but can’t seem to find any convenient way of converting this to ASCII/human readable date format.
It looks like these aren’t NTP timestamps – they are simply 64-bit Windows timestamps
I don’t have any server 2008r2 boxes to try this on I don’t think, but – give this a try:
((hexadecimal integer (it) / 10000000) * second + "01 Jan 1601 00:00:00" as local time) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")
q: ((hexadecimal integer (it) / 10000000) * second + "01 Jan 1601 00:00:00" as local time) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Wed, 18 Aug 2010 17:59:20 -0700
A: Wed, 18 Aug 2010 17:59:20 -0700
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Thu, 13 Dec 2012 11:10:25 -0700
So now I need to combine all of this into one piece of relevance… I’m trying to get there myself but am getting stuck on “maximum of” for the hexadecimal integer date conversion… it’s giving me all of the dates (instead of the most recent one):
q: if (exists (string value of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it contains (regex "^[0-9a-fA-F]{16}"))) then ((maximum of ((hexadecimal integer (it) / 10000000)) * second + "01 Jan 1601 00:00:00" as local time) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")) else "N/A" as hexadecimal integer as string
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Wed, 18 Aug 2010 17:59:20 -0700
A: Wed, 18 Aug 2010 17:59:20 -0700
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Thu, 13 Dec 2012 11:10:25 -0700
A: Wed, 18 Aug 2010 17:59:21 -0700
etc…
I’m still relatively new to relevance and when it gets any way complex, my lack of knowledge shows.
My hope is that I can eventually combine this all into one analysis for Windows machines, e.g.:
q: if (exists (string value of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it contains (regex "^[0-9a-fA-F]{16}"))) then ((maximum of ((hexadecimal integer (it) / 10000000)) * second + "01 Jan 1601 00:00:00" as local time as string) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")) else (maximum of (it as date) of ((preceding text of last "/" of following text of first "/" of it ) & " " & (preceding text of first "/" of it as integer as month as three letters)& " " & (following text of last "/" of it as integer as string)) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != ""))
A: Wed, 10 Aug 2016
T: 40582.261 ms
I: singular date
q: exists (string value of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it contains (regex "^[0-9a-fA-F]{16}"))
A: True
T: 11425.551 ms
I: singular boolean
q: maximum of ((hexadecimal integer (it) / 10000000) * second + "01 Jan 1601 00:00:00" as local time) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")
A: Wed, 17 Aug 2016 10:01:01 -0700
T: 11530.816 ms
I: singular time
Combined:
q: if (exists (string value of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it contains (regex "^[0-9a-fA-F]{16}"))) then ((maximum of ((hexadecimal integer (it) / 10000000) * second + "01 Jan 1601 00:00:00" as local time) as string)) of ((string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")) else ""
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Wed, 18 Aug 2010 19:29:31 -0700
A: Wed, 18 Aug 2010 17:59:20 -0700
A: Wed, 18 Aug 2010 17:59:20 -0700
.
etc…
So the only difference between the individual and combined relevance is the “as string” after “as local time” which is required for the types to be compatible…
I don’t have a machine to test this on but can you try:
if (exists (string value of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it contains (regex "^[0-9a-fA-F]{16}"))) then ((it as string) of maximum of (((hexadecimal integer (it) / 10000000)) * second + "01 Jan 1601 00:00:00" as local time) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")) else "N/A"
I removed the as hexadecimal integer as string from the end and I moved the maximum of outside of the parenthesis, and I casted it to a string.
I think the original issue was here:
(maximum of ((hexadecimal integer (it) / 10000000) * second + "01 Jan 1601 00:00:00" as local time) as string))
When we do (this) of (those) we are essentially making an iterator, that is, we are doing an operation for each item in (those) – in this case the timestamps:
So when we do that one operation on each timestamp:
(maximum of ((hexadecimal integer (it) / 10000000) * second + "01 Jan 1601 00:00:00" as local time) as string))
We are only doing it to one timestamp. That means maximum of doesn’t mean anything because we are only passing it one timestamp. So the goal is to do maximum of to all of the returned timestamps. Once we have the maximum we do (it as string) to convert it to a string.
The types on your if then else have to match so if we do
if (true) then (time value) else (string)
We will get an error because the types have to be the same, so we can either have else return an old string like year 1601 or we can have then return the time value as a string:
if (true) then (time value as string) else (string)
Yes, this is incredibly helpful - both the fact that it returns a valid result, plus the explanation of where I was going wrong with the “maximum of” part of the query.
Combining the relevancies per your instructions above now gives working query which returns a set of results for all three scenarios (WS2003/standard date format/64-bit Windows timestamps):
if name of operating system contains "Win2003" then ((it as string) of (maximum of (date (local time zone) of time value of it) of (selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (exists time value of it))) else if (exists (string value of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it contains (regex "^[0-9a-fA-F]{16}"))) then preceding texts of parenthesized parts 1 of matches (regex "^([\S]+\s){5}") of ((it as string) of maximum of (((hexadecimal integer (it) / 10000000)) * second + "01 Jan 1601 00:00:00" as local time) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")) else ((it as string) of maximum of ((it as date)) of ((preceding text of last "/" of following text of first "/" of it ) & " " & (preceding text of first "/" of it as integer as month as three letters)& " " & (following text of last "/" of it as integer as string)) of (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != ""))
Machine running Windows Server 2003:
A: Thu, 25 Aug 2016
Machine returning 64-bit Windows timestamps:
A: Wed, 17 Aug 2016
Machine returning standard date format:
A: Wed, 10 Aug 2016
There’s probably a more elegant way to do it, but this is working.There’s an extraneous space after the middle result (64-bit Win timestamps) due to the “parenthesized parts” logic that removes the time/time zone data whilst leaving the 5th white space, but I can probably live with that.
Thank you all so much for your help - it is greatly appreciated.
we also see results similar to “01cb3f0bae5d5621” for the InstalledOn date on all our Win2008 base (non-R2) servers. It also looks bad in WMI though, so I don’t think it is a bigfix client issue.
@cstoneba - correct. This part of the relevance should take care of that though, converting the hex date into human readable format:
if (exists (string value of selects “InstalledOn from Win32_QuickFixEngineering” of wmi) whose (it contains (regex “^[0-9a-fA-F]{16}”))) then preceding texts of parenthesized parts 1 of matches (regex “^([\S]+\s){5}”) of ((it as string) of maximum of (((hexadecimal integer (it) / 10000000)) * second + “01 Jan 1601 00:00:00” as local time) of (string values of selects “InstalledOn from Win32_QuickFixEngineering” of wmi) whose (it as trimmed string != “”)) else “N/A”
this does return a date, but only a single (maximum) date. How can that be used in junction with what I use to pull back the HotFixID and the InstalledOn values?
if (exists wmi) then ((string value of property "HotFixID" of it & " | " & string value of property "InstalledOn" of it) of select objects "HotFixID, InstalledOn from Win32_QuickFixEngineering where HotFixID != 'File 1'" of wmi) as string else nothing
And does anyone know why this is only this way for Win2008 base? 2003, 2008R2, 2012 all seem to work fine when reading the InstalledOn value from wmi.
Good Morning, everyone I am trying to use the Relevance above, Thanks @it_cat !!!, but having trouble adding the description and Hotfix ID in the form of HotfixID, Description, patch time (it should be the last patch on the machine based on time as above and not a list of ALL the patches installed.
thanks…
Adding the relevance for completeness
if (not Windows of operating system) then “N/A” else if (exists wmi) then (maximum of (it as date) of (((it as integer as string) of preceding text of last “/” of following text of first “/” of it ) &" “& (preceding text of first “/” of it as integer as month as three letters)&” "& (following text of last “/” of it as integer as string)) of (((string value of property “InstalledOn” of it) of select objects “InstalledOn from Win32_QuickFixEngineering” of wmi))) as string else (“N/A”)