Relevance Question: Sum of Applicable Computer Counts and Group Membership

Reporting
1

I’m a newb when it comes to writing relevance and I’ve combed the bigfix.me and relevance statements within BigFix to get a jump start on writing some queries but I’ve hit a wall. I’m able to create a query that will total up the sump of applicable computer counts for critical vulnerabilities but I cannot for the life of me figure out how to only count these for computers which are part of a specific group. Here is my current relevance code:

sum of Applicable Computer Counts of bes fixlets whose 
(
   (
    name of site of it = "Enterprise Security" OR 
    name of site of it = "Patches for CentOS 6" OR
    name of site of it = "Patches for RHEL 4" OR
    name of site of it = "Patches for RHEL 5"
   )
    AND 
   (
    Category of it as lowercase contains "security" AND
    Source Severity of it as lowercase contains "critical" AND
    Applicable Computer Count of it > 0 AND
    name of it as lowercase does not contain "superseded" AND
    name of it as lowercase does not contain "corrupt patch"
   )
)

Any help would be greatly appreciated.

2

I’ve tried variations of adding

member of group 24913

to different areas with no results.

3

I think this will work:

(names of it, number of relevant fixlets whose (   (
    name of site of it = "Enterprise Security" OR 
    name of site of it = "Patches for CentOS 6" OR
    name of site of it = "Patches for RHEL 4" OR
    name of site of it = "Patches for RHEL 5"
   )
    AND 
   (
    Category of it as lowercase contains "security" AND
    Source Severity of it as lowercase contains "critical" AND
    Applicable Computer Count of it > 0 AND
    name of it as lowercase does not contain "superseded" AND
    name of it as lowercase does not contain "corrupt patch"
   )) of it) of elements of member set of bes computer group whose (name of it = "The Four Horsemen")

Replace “The Four Horsemen” with any computer group name of your choice and it should filter accordingly.

I get back:

W0828EN------02, 34
W0326EN------02, 50
W0328EN------09, 52
W0706ENOF10--02, 30

Fun fact: Four Horsemen is a stage in the excellent playstation 2 era fighter pilot simulator “Ace Combat 5”. It has excellent music and helps me write relevance at least 30% better…

1 Like
4

How long have you been waiting to use that URL? :slight_smile:

5

Not long TimRice! Just for the record I have many many many more URLs!

6

Thanks Dex, but that dumps a list of computer names rather than a count or sum of applicable computers. The client wants a sum of all relevant fixlets for computers in a particular group.

7

Ok, that’s easy to fix then… we go from my original query to:

sum of (number of relevant fixlets whose (   (
    name of site of it = "Enterprise Security" OR 
    name of site of it = "Patches for CentOS 6" OR
    name of site of it = "Patches for RHEL 4" OR
    name of site of it = "Patches for RHEL 5"
   )
    AND 
   (
    Category of it as lowercase contains "security" AND
    Source Severity of it as lowercase contains "critical" AND
    Applicable Computer Count of it > 0 AND
    name of it as lowercase does not contain "superseded" AND
    name of it as lowercase does not contain "corrupt patch"
   )) of it) of elements of member set of bes computer group whose (name of it = "The Four Horsemen")

Which should just get you 166, the sum of all the computer’s relevant fixlet count in a particular group.

1 Like
8

I’m doing something very similar, which I’ll retrieve tomorrow, time allowing.

What I want to add now is that you can approach this in two different ways :

number of relevant fixlets of bes computers

Or

Number of applicable computers of bes fixlets

(With appropriate filters on each). Which performs better may be a factor of the number of computers vs the number of fixlets.

1 Like
9

Ok, I was going to post an excerpt of a dashboard I wrote to show my patch status overview, but I build HTML code in the relevance statement and this forum won’t allow me to input the tags. I’ve mangled it up a bit, I’m hoping this will show ok.

I have a custom site called “CM_Windows”. You should replace that with a name of one of your custom sites. My Windows systems are subscribed to the site, and the site contains all of my Patch Baselines. What this statement does is to build an HTML Table displaying the patch statuses.

The four columns are “Header”, “Applicable Computer Count”, “Relevant Fixlet Count”, and “Fixlet Severities”.
The “Header” column just allows for row titles, they vary on each row. “Applicable Computer Count” is the sum of the numbers of applicable computers for the fixlets; “Relevant Fixlet Count” is the number of unique fixlets that have applicable computers. “Fixlet Severities” is a breakdown of the number of fixlets of each severity (Critical, Important, Moderate, N/A).

The first Row gives Totals - Total number of fixlets from “Patches for Windows” and “Updates for Windows Applications”.
The second row shows only the counts of those fixlets with Default Actions.
The third row is the counts for fixlets that do not have a Source Release Date specified.
The fourth row is those fixlets with source release dates less than 60 days old.
The fifth row is those fixlets between 60 and 90 days old.
The sixth row are those fixlets that are more than 90 days old.
The seventh row are those fixlets that are already contained by a Patch baseline in my custom site.

In all cases, any Fixlets that are contained by a Patch Baseline in our site, where the Baseline Name contains “ignore”, the Fixlets are not included in the counts.

To be clear, I have spaces between some of the lines for (an attempt at) readability, but the remainder of this post is a single long relevance statement:

(H3 of "Missing Fixlet Totals" & table "border=all" of (tr of (td of " " & td of "Applicable Computer Count" & td of "Relevant Fixlet Count" & td of " Fixlet Severities") & concatenation of trs of (td of (item 0 of it as string) & td of (item 1 of it as string) & td of (item 2 of it as string) & 

td of (table of concatenation of trs of ( td "align=right" of( multiplicity of it as string) & (td of  (html(" &nbsp &nbsp") & it))) of unique values of (source severities of elements of item 3 of it)

)) of (item 0 of it; item 1 of it; item 2 of it; item 3 of it; item 4 of it; item 5 of it; item 6 of it)) 

& (html ("< br >")) & H3 of "Not contained by a Baseline - With Default Actions" & table "border=all" of (tr of (td of "Computer Count" & td of "Severity" & td of "Fixlet Link") & concatenation of trs of((td of (number of applicable computers of it as string) & td of (source severity of it) & td of (link of it) ) of elements whose (exists default action of it) of item 7 of it) ) 
& (html ("< br >")) & H3 of "Not contained by a Baseline - No Default Actions" & table "border=all" of (tr of (td of "Computer Count" & td of "Severity" & td of "Fixlet Link") & concatenation of trs of((td of (number of applicable computers of it as string) & td of (source severity of it)  & td of (link of it) ) of elements whose (not exists default action of it) of item 7 of it) )  ) 

 of ( 
("Total",sum of number of applicable computers of (elements of it), number of elements of it, it),  
("Default Actions",sum of number of applicable computers of (elements of it), number of elements of it, it) of set of (elements of it) whose (exists default action of it),
  ((" Date Unspecified", sum of number of applicable computers of (elements of it), number of elements of it, it) of set of (elements of it) whose (not exists (source release date of it))),  
((" < 60 Days", sum of number of applicable computers of (elements of it), number of elements of it, it) of set of (elements of it) whose (exists (source release date of it) whose ((it <= 60) of ((date (local time zone) of now - it) / day)))),  
((" 60 - 90 Days", sum of number of applicable computers of (elements of it), number of elements of it, it) of set of (elements of it) whose (exists (source release date of it) whose ((it > 60 and it <= 90) of ((date (local time zone) of now - it) / day )))),  
((" > 90 Days",sum of number of applicable computers of (elements of it), number of elements of it, it) of set of (elements of it) whose (exists (source release date of it) whose ((it > 90) of ((date (local time zone) of now - it) / day)))), 
 (("In a Baseline",sum of number of applicable computers of (elements of it), number of elements of it, it) of (set of (elements of it) whose (it is contained by set of source fixlets of components of component groups of fixlets whose (baseline flag of it) of bes custom site whose (name of it = "CM_Windows")))),  
(it - set of source fixlets of components of component groups of fixlets whose (baseline flag of it) of bes custom site whose (name of it = "CM_Windows"))  )  of 

set of (fixlets of bes sites whose (name of it is contained by ("Enterprise Security|Updates for Windows Applications"))) whose (fixlet flag of it AND applicable computer count of it > 0 AND name of it as lowercase does not contain "superseded" and it is not contained by set of source fixlets of components of component groups of fixlets whose (baseline flag of it and name of it as lowercase contains "ignore") of bes custom site whose (name of it = "CM_Windows"))
3 Likes
10

Thanks for the script! Works great! I now would like to transform the returned numbers to percentage.
But I’m unable to get the number of fixlets used to calculate the sum of applicable computer. So I can’t calculate the percentage.
Thanks for your help

11

I think you could do the percentage too (I’m guessing you mean # relevant / total available pieces of content)… untested, but it’d be something like:

 sum of (number of relevant fixlets whose (   (
    name of site of it = "Enterprise Security" OR 
    name of site of it = "Patches for CentOS 6" OR
    name of site of it = "Patches for RHEL 4" OR
    name of site of it = "Patches for RHEL 5"
   )
    AND 
   (
    Category of it as lowercase contains "security" AND
    Source Severity of it as lowercase contains "critical" AND
    Applicable Computer Count of it > 0 AND
    name of it as lowercase does not contain "superseded" AND
    name of it as lowercase does not contain "corrupt patch"
   )) of it) of elements of member set of bes computer group whose (name of it = "The Four Horsemen")
/
sum of (number of relevant fixlets whose (   (
    name of site of it = "Enterprise Security" OR 
    name of site of it = "Patches for CentOS 6" OR
    name of site of it = "Patches for RHEL 4" OR
    name of site of it = "Patches for RHEL 5"
   )
    AND 
   (
    Category of it as lowercase contains "security" AND
    Source Severity of it as lowercase contains "critical" AND
    name of it as lowercase does not contain "superseded" AND
    name of it as lowercase does not contain "corrupt patch"
   )) of it) of elements of member set of bes computer group whose (name of it = "The Four Horsemen")

Orrr something like that.

12

Thanks for your answer. This always gives 1 as result. Because we are dividing “number of relevant fixlets”/“number of relevant fixlets”.
What I am looking for is “Count of unique relevant fixlets” instead, but I don’t know how to write it.
I think I have to use something like:

number of  ((name of computers of it,name of fixlet of it) of results from (bes fixlets whose ((display name of site of it contains "Patches for Windows" or display name of site of it contains "Updates for Windows Applications") and  Category of it as lowercase contains "security" and name of it as lowercase does not contain "superseded" and Source Severity of it as lowercase contains "critical" and name of it as lowercase does not contain "corrupt patch")) whose (relevant flag of it = true) of elements of member set of bes computer group whose (name of it as lowercase contains "windows 8"))

This works, but I need to eliminate fixlets names duplicates. I tried using “unique” but without success.

My goal is to get: (total number of relevant fixlets / (number of computers * number of unique fixlet)) * 100

I already have “total number of relevant fixlets” and “number of computers”, just need to find the “number of unique relevant fixlets”.

Thanks for your help

13

How about

size of set of fixlets whose (number of applicable computers of it > 0 AND [other filters]) of sites whose ([match the desired sites])

Using a “set” should eliminate duplicates fixlets.

I’m not sure how that really helps though. Suppose you have 100 fixlets, 100 computers, and one fixlet relevant on each; then each computer would show “1% Unpatched”? But if instead you have 1 fixlet relevant on all 100 computers, it appears as “100% Unpatched”?

…
Maybe something that might be useful -

sum of numbers of applicable computers of fixlets whose ([filters]) of sites whose ([filters])

…which, come to think of it, I included in my earlier Dashboard post as “Applicable Fixlet Totals”.

In my first example (100 fixlets / 1 computer each), that would be able to tell you that each computer is responsible for 1% of the unpatched vulnerabilities; in the second example, each computer is still responsible for 1% of the unpatched vulnerabilities. Where it gets more interesting is where one computer is responsible for a larger percentage.

I later wrote a dashboard which shows every computer, along with the count of applicable fixlets (filtered to security patches) for that computer. In my Dashboard I have JavaScript that allows me to sort it, so I can see if there are any outlier machines with more unpatched vulnerabilities than average. I’ll try to post that next week if that would be helpful.

1 Like