I am using the following WMI relevance query for “Last Install Date of a Patch”:
q: 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")
Mostly, this works a treat, e.g.:
A: Wed, 10 Aug 2016
But for about 2,700 (out of our 42,000) Windows machines we get a result like this:
E: Singular expression refers to nonexistent object
Removing the “maximum” date restriction and date formatting - to just give all results - i see entries like this:
q: if (exists wmi) then ((string value of property "InstalledOn" of it) of select objects "InstalledOn from Win32_QuickFixEngineering" of wmi) as string else ("N/A")
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)
I added:
whose (exists time value of it)
After the WMI statement to remove the blank entries.
Also simplified the WMI statement and the date translation.
I’d recommend putting this in an analysis with relevance of, “Windows of Operating System” instead of doing the first if (windows of operating system) thing if possible.
Thanks for the response But while that is much more succinct, this is the “future” result I get on my local laptop:
A: Thu, 01 Dec 2016
And on a London-based VM:
A: Tue, 08 Nov 2016
Old results were (respectively):
A: Wed, 10 Aug 2016
A: Thu, 18 Aug 2016
I see there is the “local time zone” element in there, but could time zones be interfering with this at all? Do you get an accurate result on your machine?
So even though my machine’s OS regional settings/system locale are UK, the dates returned via Fixlet Debugger are US… so; are WMI dates always in US format?
I ran this on a very patched machine and didn’t get this
Q: maximum of time values of (selects "InstalledOn from Win32_QuickFixEngineerin
g" of wmi) whose (exists time value of it)
A: Thu, 18 Aug 2016 00:00:00 -0700
T: 13464167
Q: number of selects "InstalledOn from Win32_QuickFixEngineering" of wmi
A: 428
T: 12386423
@strawgate I see you already asked the question but it does seem like there has to be a value in there that is in the future
Same for me on a UK machine - “time values of (selects “InstalledOn from Win32_QuickFixEngineering” of wmi) whose (exists time value of it)” gives me dates in October, November & December of this year.
GWMI in PowerShell shows that it is returning string values in the format Month/Day/Year that are not zero padded.
It appears that dates in PowerShell shown as 8/10/2016 appear in the Fixlet Debugger “Sat, 08 Oct 2016 00:00:00 +0100” but 8/17/2016 translate to “Wed, 17 Aug 2016 00:00:00 +0100” and 7/13/2016 translates to “Wed, 13 Jul 2016 00:00:00 +0100”
Dates that are ambiguous with no reference to country codes are assumed Month, Day, Year. Dates where the day number is greater that 12 do get displayed correctly.
So these dates look ok; nothing “future” as they haven’t been converted… I am ok with a date in US format as long as it’s uniform…is this even possible to get the “maximum” of these though, considering how they are translated?
@trn; since the dates in WMI are correct in US-format, is it not the dates where the day (2nd place number) is less than 12 that do not get displayed correctly (as they are related back in UK-format)?
I.e.:
8/17/2016 translate to “Wed, 17 Aug 2016 00:00:00 +0100”
→ this is correct
7/13/2016 translates to “Wed, 13 Jul 2016 00:00:00 +0100”
→ this is correct
8/10/2016 appear in the Fixlet Debugger “Sat, 08 Oct 2016 00:00:00 +0100”
→ this is actually incorrect as the date displayed is the 10th of August (not the 8th of October)
Well, with our hopes and dreams crushed I guess that brings us back to how you were originally doing it plus a check to make sure the value actually exists:
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 != "")
Yes thanks @strawgate; this is what i will use. It’ll be a big improvement on what I had.
Just a question… why use an analysis (targeting Windows) rather than using an RP with “if (not Windows of operating system) then “N/A””? Is it more efficient?
Also, one last piece of weirdness and then I’ll leave you alone
Here is what I get on a Windows Server 2008 Standard R2 box for just the base values:
q: (string values of selects "InstalledOn from Win32_QuickFixEngineering" of wmi) whose (it as trimmed string != "")
I looked in the code, and found the structures in use. My original comment wasn’t correct as there is no “country” type of formatting within the WMI for the date.
If you have this in an analysis with relevance of true then all computers – Macs, Linux, anything non-windows will report N/A
If you put it in an analysis with relevance of, “windows of operating system” the query won’t run on all of your non-windows machines and the only machines you’ll get results for are your Windows boxes.