Query to return all applicable patches

Customizations
1

Hi All,

I am been trying to work on a query to applicable patches from the “Patches for Windows” site. I thought I had it going, but it seems to be returning more systems than the console shows as being relevant. Here are the criteria:

  1. From Patches for Windows (although, I want to use it with different sites also)
  2. Applicable Computer Count > 0
  3. Category is either “Security Update” or “Security Hotfix” (changed for the different sites)
  4. Source release date is over 30 days old

For testing purposes I also included a filter to only retrieve a patch with “MS11-025”. For this patch, the console shows 5 applicable systems, but the session relevance editor returns 35. I have approx 800 Windows servers and there does not seem to be a commonality for these servers. Some are 2008, some are 2012, there are also x86 and x64 systems.

Here is the query:
(name of computer of it & “|” & (name of fixlet of it) & “|” & (if (exists category of fixlet of it) then category of fixlet of it else “Unspecified” ) & “|” & (if (exists Source Severity of fixlet of it) then Source Severity of fixlet of it as string else “Unspecified”) & “|” & (Source Release Date of fixlet of it as string) & “|” & (Applicable Computer Count of fixlet of it as string) & “|” & (Display Name of Site of fixlet of it as string) ) of results of bes fixlets whose ((display name of site of it = “Patches for Windows”) AND (Applicable Computer Count of it > 0) AND (Category of it as string as lowercase = “security update” or Category of it as string as lowercase = “security hotfix”) AND (current date - source release date of it > 30 * day) AND (Name of it as string as lowercase starts with “ms11-025”))

I am also using the “|” as a delimiter as I cannot use a comma or colon as some sites use these in one of the fields I collect.

I have also tried running it from the Web REST API with the following:

https://:52311/api/query?relevance=%28name%20of%20computer%20of%20it%20%26%20%22%7C%22%20%26%20%28name%20of%20fixlet%20of%20it%29%20%26%20%22%7C%22%20%26%20%28if%20%28exists%20category%20of%20fixlet%20of%20it%29%20then%20category%20of%20fixlet%20of%20it%20else%20%22Unspecified%22%20%29%20%26%20%22%7C%22%20%26%20%28if%20%28exists%20Source%20Severity%20of%20fixlet%20of%20it%29%20then%20Source%20Severity%20of%20fixlet%20of%20it%20as%20string%20else%20%22Unspecified%22%29%20%26%20%22%7C%22%20%26%20%28Source%20Release%20Date%20of%20fixlet%20of%20it%20as%20string%29%20%26%20%22%7C%22%20%26%20%28Applicable%20Computer%20Count%20of%20fixlet%20of%20it%20as%20string%29%20%26%20%22%7C%22%20%26%20%28Display%20Name%20of%20Site%20of%20fixlet%20of%20it%20as%20string%29%20%29%20of%20results%20of%20bes%20fixlets%20whose%20%28%28display%20name%20of%20site%20of%20it%20%3D%20%22Patches%20for%20Windows%22%29%20AND%20%28Applicable%20Computer%20Count%20of%20it%20%3E%200%29%20AND%20%28Category%20of%20it%20as%20string%20as%20lowercase%20%3D%20%22security%20update%22%20or%20Category%20of%20it%20as%20string%20as%20lowercase%20%3D%20%22security%20hotfix%22%29%20AND%20%28current%20date%20-%20source%20release%20date%20of%20it%20%3E%2030%20%2A%20day%29%20AND%20%28Name%20of%20it%20as%20string%20as%20lowercase%20starts%20with%20%22ms11-025%22%29%29

And I get the same result. So there is something really wrong in my query, but I am missing it.

Any thoughts?

Thanks

Martin

2

Forgot to mention that it does show the 5 systems in that 35 that are applicable. Just seems to add a few more for whatever reason.

3

Haven’t tried this yet, but I think you’ll get ‘bes fixlet results’ for fixlets that are not relevant -

q: properties of type "bes fixlet result"
A: fixlet of : bes fixlet
A: computer of : bes computer
A: relevant flag of : boolean
A: remediated flag of : boolean
A: first became relevant of : time
A: last became relevant of : time
A: last became nonrelevant of : time

So you may be getting computers based on ‘bes fixlet result’ having a “remediated flag” or “first became relevant” or something else.

Try filtering for something like of results whose (relevant flag of it) of bes fixlets in your existing query, else rearrange and look at the applicable computers of bes fixlet property instead of results of bes fixlet.

1 Like
4

Hi Jason,

I will take a look at this and see if I can get what I need.

Thanks for the response.

Martin

5

Just in case anyone ever needs this type of query

(name of computer of it & “|” & (name of fixlet of it) & “|” & (if (exists category of fixlet of it) then category of fixlet of it else “Unspecified” ) & “|” & (if (exists Source Severity of fixlet of it) then Source Severity of fixlet of it as string else “Unspecified”) & “|” & (Source Release Date of fixlet of it as string) & “|” & (Applicable Computer Count of fixlet of it as string) & “|” & (Display Name of Site of fixlet of it as string) ) of results whose (relevant flag of it = true) of bes fixlets whose ((display name of site of it = “Patches for Windows”) AND (Applicable Computer Count of it > 0) AND (Category of it as string as lowercase = “security update” or Category of it as string as lowercase = “security hotfix”) AND (current date - source release date of it > 30 * day))

3 Likes