Need a relevance code to pull the last date of a particular patch installed

Hi All,

I have an analysis to check if the KB package is installed in win 2003 servers or now by the following relevance
exists (string values of properties “HotFixID” of it) whose(it starts with “KB4500331”) of select objects “HotFixID from Win32_QuickFixEngineering” of wmis
can you please tell me the relevance I can use to check as to which date it was installed on a particular server and can fetch from web reports?

Here, try this relevance, it should answer your question directly.

if (exists wmi) then ((string value of property “InstalledOn” of it) of select objects “* from Win32_QuickFixEngineering WHERE HotFixID LIKE ‘KB4500331’” of wmi) as string else (“N/A”)

However, I would recommend looking in the “Patches for Windows” site and review the relevance of the KB4500331 fixlet thats in that site. It will probably give you some relevance that might be more accurate, or at the very least be faster to execute on your endpoints.

Another thing you could do as well is use the following relevance

exists relevant fixlets whose ((it = 458) of id of it) of sites whose (name of it = “Enterprise Security”)

Just need to replace the “it = 458” to be the ID of the fixlet published for the specific KB, and that will return a TRUE or FALSE for if the specific device is relevant to that specific task. Essentially indicating, TRUE (it does need to be patched) or FALSE (it is already patched)

I personally like the last method for the following reason:
If the fixlet is updated for any reason, as long as the fixlet ID doesn’t change, it will still be accurate and true.

1 Like

We have the relevance to check on which server this KB article is installed on but we need to know on which date this KB article was installed. Please suggest.

Please re-read my above post. The first quoted relevance should do what you want.

1 Like

Thanks a lot,
It worked on server 2008 R2 but on server 2008 standard it gave me the below mentioned output:-1:
q: if (exists wmi) then ((string value of property “InstalledOn” of it) of select objects “* from Win32_QuickFixEngineering WHERE HotFixID LIKE ‘KB4499149’” of wmi) as string else (“N/A”)
I: Current Selection Evaluation
A: 01d5155a252175eb
T: 19012.237 ms

Also when I checked the list of installed patches with dates installed with the below relenace, it gives me the details of all the patches installed with dates installed:-1:
q: ((if (exists property “HotFixID” of it) then (string value of property “HotFixID” of it) else ("")), (if (exists property “InstalledOn” of it) then ((if (exists hexadecimal integer (it)) then (((month of it as integer as string&"/"& day_of_month of it as integer as string&"/"&year of it as string) of ((january 1 of 1601)+((hexadecimal integer (it)/(864000000000))day))) of it) else (it)) of string value of property “InstalledOn” of it) else ("")), (if (exists property “Description” of it) then (string value of property “Description” of it) else (""))) of select objects " from Win32_QuickFixEngineering" of WMI
A: {25CD1DBE-0D2D-3E78-9FDC-794CC40B94AA}, 1/1/1601,
A: KB971512, 11/2/2016, Update
A: 944036, 5/28/2009, Update
A: 982861, 11/2/2016, Update
A: KB2079403, 4/8/2011, Security Update
A: KB2117917, 11/2/2016, Update
A: KB2124261, 4/8/2011, Security Update
A: KB2158563, 4/8/2011, Update
A: KB2207559, 5/4/2011, Security Update

It should give me the date output like above for a particular KB

q: if (exists wmi) then ((string value of property “InstalledOn” of it) of select objects “* from Win32_QuickFixEngineering WHERE HotFixID LIKE ‘KB4499149’” of wmi) as string else (“N/A”)
I: Current Selection Evaluation
A: 01d5155a252175eb
T: 19012.237 ms
But id did not. Please suggest on this.

“Current selection evaluation” indicates you had some part of the query selected/highlighted - and only that part is evaluated. Try clearing your mouse selection and try again.

OK, I tired again and it gave me the out put like this:-1:
Q: if (exists wmi) then ((string value of property “InstalledOn” of it) of select objects “* from Win32_QuickFixEngineering WHERE HotFixID LIKE ‘KB4467706’” of wmi) as string else (“N/A”)
A: 01d48c7f4f88155d
T: 13103.020 ms
It should show the dat at which the KB was installed.

Ok I think there is some confusion there…in the query that works, did you notice there is a lot of parsing of that value happening?

(if (exists property “InstalledOn” of it) then ((if (exists hexadecimal integer (it)) then (((month of it as integer as string&“/”& day_of_month of it as integer as string&“/”&year of it as string) of ((january 1 of 1601)+((hexadecimal integer (it)/(864000000000))day))) of it) else (it)) of string value of property “InstalledOn” of it) else (“”))

It like the install date is not just a string, but a large binary value that gets parsed.

I understand your point here but my confusion is that it is working perfectly on server 2008 R2 servers, check the output below:-1:
q: if (exists wmi) then ((string value of property “InstalledOn” of it) of select objects “* from Win32_QuickFixEngineering WHERE HotFixID LIKE ‘KB4493472’” of wmi) as string else (“N/A”)
A: 5/26/2019
T: 23897.625 ms

Oh sorry I misunderstood.
So…the inquiry is “Why has Microsoft chosen to not be consistent with how patch install data is stored across OS and across patches”?

The data source where WMI is retrieving that is, I believe, in the registry and is likely not stored consistently there either. It’s been a while since I looked at that though.

Using wmic.exe for the same query would be interesting. I’m not sure how wmic handles it - either it would return inconsistent data (like the qna queries you’re trying), or it is reformatting the data to normalize it (like the original query snippet I quoted).

The snippet was built to handle both cases - if the date is stored as a simple string, that is returned; if the date is stored in the uint64, “number of 100-nanosecond increments since 1/01/1601”, then that is parsed back into a human-readable date.

Does the snippet give you the right answer?

1 Like

More on that Windows FileTime format:

Seems around Windows XP vintage the WMI query forInstalledOn from Win32_QuickFixEngineering started giving a FileTime instead of the older, human readable format.

Hence the use of that little “decoder” piece of relevance. Which converts your 01d5155a252175eb into May 28th, 2019

q: ((january 1 of 1601)+ (hexadecimal integer (it)/864000000000*day)) of ("01d5155a252175eb")
A: Tue, 28 May 2019

Sounds great but how can I include this piece in my already running query? Kindly suggest on it.

@tarwaniv You already posted it in post #6 above, but there are 2 missing * characters from the relevance you posted.

Please use the </> tool to mark things as “code” when posting these samples.

here is the corrected relevance which will pull all of the hotfixes.

q: ((if (exists property "HotFixID" of it) then (string value of property "HotFixID" of it) else ("")), (if (exists property "InstalledOn" of it) then ((if (exists hexadecimal integer (it)) then (((month of it as integer as string&"/"& day_of_month of it as integer as string&"/"&year of it as string) of ((january 1 of 1601)+((hexadecimal integer (it)/(864000000000))*day))) of it) else (it)) of string value of property "InstalledOn" of it) else ("")), (if (exists property "Description" of it) then (string value of property "Description" of it) else (""))) of select objects "* from Win32_QuickFixEngineering" of WMI

Your other query from post #8 shows that you know how to modify a WMI string

q: select objects "* from Win32_QuickFixEngineering WHERE HotFixID LIKE 'KB4467706'" of wmi

I will leave it to you to put these 2 together. Please post your working code when you solve, so others may benefit.

Welcome to the forum!

If you want deeper relevance training, there is a great self guided course with all the basics:
http://support.bigfix.com/fixlet/documents/CustomAuthoringTrainingGuide-6-17-2005.pdf

1 Like

I saw your link for the training guide. I’m sure a lot of it can still be used but at the same time its 14 years old ha ha… Is there a updated guide available somewhere? If not is there somewhere we can go to request such a thing?

Thanks!

I guess they wrote it right the first time :slight_smile:slight_smile:

Edit: the relevance guide is still the best way to learn the relevance language structure and operations from scratch. That’s a good foundation, after that check the reference at https://developer.bigfix.com to find the newer Inspections and ActionScript commands available.

1 Like

While the if it ain’t broke don’t fix it is always a valid point its something I had to ask.

I actually don’t believe I ever found that guide prior. Best way I learned relevance and actionscript was to look at the out of box tasks / analysis’ that come from Windows Patches, etc…

Even though there is a ton of custom things to BigFix, when your first starting out there are wizards and such that will give you a template to work with if nothing else.

Please check the updation made to relevance and I cannot get rid of (') sign at the end. Please suggest on this and also with the link provided I will learn relevance making to the best of my ability.

How about:

((if (exists property "HotFixID" of it) then (string value of property "HotFixID" of it) else ("")), (if (exists property "InstalledOn" of it) then ((if (exists hexadecimal integer (it)) then (((month of it as integer as string&"/"& day_of_month of it as integer as string&"/"&year of it as string) of ((january 1 of 1601)+((hexadecimal integer (it)/(864000000000))*day))) of it) else (it)) of string value of property "InstalledOn" of it) else ("")), (if (exists property "Description" of it) then (string value of property "Description" of it) else (""))) of select objects "* from Win32_QuickFixEngineering where hotfixid like 'KB981332'" of WMI

If you’re trying to filter on the HotFix ID in the WMI query itself, that must be within the double-quotes of the WMI select statement.

2 Likes

Bingo…It seems to exactly working the way I wanted, Thanks a TON!!!

1 Like