How can I filter Windows Patches and their results by Computer Group?

I have a report that returns a count of computers for individual patches (bes fixlets), but I also need to be able to filter the results of this report based on Computer Group. Without the filtering (returning results for all computers) this report runs perfectly, but when I try filtering by Computer Group the report will return patches that don’t apply to any computers in the group (that are neither remidiated nor relevant) as well as the ones that do. I want this report to only display the patches that contain either remidiated or relevant computer counts (It does do this in the version without Computer Group filtering). The information that it currently returns is not technically wrong (as the counts are all zero for patches that don’t apply), but the patches with no applicable computers shouldn’t be appearing at all.

here is my query with Computer Group filtering

( name of item 0 of it | "<none>", source severity of item 0 of it | "<none>", (((day_of_week of it) as three letters) & ", " & ((month of it) as three letters) & " " & ((day_of_month of it) as two digits) & ", " & ((year of it) as string) ) of source release date of item 0 of it, (item 1 of it) + (item 2 of it), item 1 of it, item 2 of it, ( if ((item 1 of it + item 2 of it) = 0) then ("N/A") else (((item 1 of it as floating point / (item 1 of it as floating point + item 2 of it as floating point)) * 100) as integer as string & "%25") ) ) of ( item 1 of it, number of results (elements of item 0 of it, item 1 of it) whose (remediated flag of it), number of results (elements of item 0 of it, item 1 of it) whose (relevant flag of it) ) of ( item 0 of it, elements of item 1 of it ) of ( ( set of items 0 of ( subscribed computers of bes sites whose (name of it = "Enterprise Security"), it) whose (id of item 0 of it = id of item 1 of it) of members of bes computer groups whose (name of it = "Laptops") ), ( set of fixlets whose ( globally visible flag of it = TRUE and ( exists source severity whose ( it is not "" and it does not contain "N/A" and it does not contain "Unspecified" ) of it ) and (source release date of it) > date "01 Apr 2015" and exists results whose (remediated flag of it or relevant flag of it) of it and exists members of bes computer groups whose (name of it = "Laptops") ) of bes sites whose (name of it = "Enterprise Security") ) ) 

I put the word “Laptops” in the query as a default computer group, but you can change it to whatever for testing purposes.

For reference here is the version that works without incorporating Computer Groups, and it works perfectly fine.

( name of item 0 of it | "<none>", source severity of item 0 of it | "<none>", (((day_of_week of it) as three letters) & ", " & ((month of it) as three letters) & " " & ((day_of_month of it) as two digits) & ", " & ((year of it) as string) ) of source release date of item 0 of it, (item 1 of it) + (item 2 of it), item 1 of it, item 2 of it, ( if ((item 1 of it + item 2 of it) = 0) then ("N/A") else (((item 1 of it as floating point / (item 1 of it as floating point + item 2 of it as floating point)) * 100) as integer as string & "%25") ) ) of ( item 1 of it, number of results (elements of item 0 of it, item 1 of it) whose (remediated flag of it), number of results (elements of item 0 of it, item 1 of it) whose (relevant flag of it) ) of ( item 0 of it, elements of item 1 of it ) of ( ( set of subscribed computers of bes sites whose (name of it = "Enterprise Security") ), ( set of fixlets whose ( globally visible flag of it = TRUE and ( exists source severity whose ( it is not "" and it does not contain "N/A" and it does not contain "Unspecified" ) of it ) and (source release date of it) > date "01 Apr 2015" and exists results whose (remediated flag of it or relevant flag of it) of it ) of bes sites whose (name of it = "Enterprise Security") ) ) 

Can anyone give me advice as to how to properly format the output so that Patches without computers in the Computer Group won’t show up in the query results?

I love your relevance. Shows good formation, you used sets effectively for performance.

I added in a little whose clause, I think it will meet your need:
whose (item 1 of it >0 or item 2 of it > 0)

( 
name of item 0 of it | "<none>"
, source severity of item 0 of it | "<none>"
, (((day_of_week of it) as three letters) & ", " & ((month of it) as three letters) & " " & ((day_of_month of it) as two digits) & ", " & ((year of it) as string) ) of source release date of item 0 of it
, (item 1 of it) + (item 2 of it)
, item 1 of it
, item 2 of it
, ( if ((item 1 of it + item 2 of it) = 0) then ("N/A") else (((item 1 of it as floating point / (item 1 of it as floating point + item 2 of it as floating point)) * 100) as integer as string & "%25") ) 
) of (
 item 1 of it
, number of results (elements of item 0 of it, item 1 of it) whose (remediated flag of it)
, number of results (elements of item 0 of it, item 1 of it) whose (relevant flag of it) 
) whose (item 1 of it >0 or item 2 of it > 0) of (
 item 0 of it
, elements of item 1 of it 
) of (
 ( set of subscribed computers of bes sites whose (name of it = "Enterprise Security") )
, ( set of fixlets whose ( globally visible flag of it = TRUE and ( exists source severity whose ( it is not "" and it does not contain "N/A" and it does not contain "Unspecified" ) of it ) and (source release date of it) > date "01 Apr 2015" and exists results whose (remediated flag of it or relevant flag of it) of it ) of bes sites whose (name of it = "Enterprise Security")))
1 Like

OH - Whoa! I edited the wrong version… Here is the correct one

( name of item 0 of it | "<none>", source severity of item 0 of it | "<none>", (((day_of_week of it) as three letters) & ", " & ((month of it) as three letters) & " " & ((day_of_month of it) as two digits) & ", " & ((year of it) as string) ) of source release date of item 0 of it, (item 1 of it) + (item 2 of it), item 1 of it, item 2 of it, ( if ((item 1 of it + item 2 of it) = 0) then ("N/A") else (((item 1 of it as floating point / (item 1 of it as floating point + item 2 of it as floating point)) * 100) as integer as string & "%25") ) ) of ( item 1 of it, number of results (elements of item 0 of it, item 1 of it) whose (remediated flag of it), number of results (elements of item 0 of it, item 1 of it) whose (relevant flag of it) ) whose (item 1 of it >0 or item 2 of it > 0) of ( item 0 of it, elements of item 1 of it ) of ( ( set of items 0 of ( subscribed computers of bes sites whose (name of it = "Enterprise Security"), it) whose (id of item 0 of it = id of item 1 of it) of members of bes computer groups whose (name of it = "Laptops") ), ( set of fixlets whose ( globally visible flag of it = TRUE and ( exists source severity whose ( it is not "" and it does not contain "N/A" and it does not contain "Unspecified" ) of it ) and (source release date of it) > date "01 Apr 2015" and exists results whose (remediated flag of it or relevant flag of it) of it and exists members of bes computer groups whose (name of it = "Laptops") ) of bes sites whose (name of it = "Enterprise Security") ) )

2 Likes