Need Help to make my Relevance Query Faster

Reporting
1

I have a report based on the Microsoft Patch Compliance Report, but it’s taking too long to load when there’s a large number of computers in the network it’s checking. Does anyone have any tips to make this report load faster? I have 117 computers on the network I’m testing my queries on and it’s taking 40 - 50 seconds to load this report. I need this query to load much faster (like 1 - 5 seconds).

The report is supposed to produce a list of computers that includes the total number of patches available for each computer, the total number of installed patches (non-relevant patches) for each computer, the total number of outstanding patches available (relevant patches) for each computer, and the compliance percentage of the computer (installed patches / total patches) * 100.

I have created 2 versions of this report. One, that produces individual counts for each patch on each computer, then the totals of which are summed up after the query is produced in javascript. The other version sums up all of the totals inside the query using multiplicities. Both versions are taking too long for a large set of computers.

Here’s the first version that doesn’t sum up the total patch counts in the query (saving that functionality for the javascrpt).

((id of computer of it as string & id of fixlet of it as string), (id of computer of it as string), name of computer of it | "<none>", operating system of computer of it | "<none>", (if exists ip addresses of computer of it then (concatenations (html "<br>") of (ip addresses of computer of it as string)) as string else html "" as string ), (if exists last report time of computer of it then (last report time of computer of it) as string else ("<none>")), (if (exists first became relevant of it) then ("1") else ("0")), (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0"), (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0") ) of results of (bes fixlets whose ((name of site of it) = "Enterprise Security" and source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ))

Here’s the 2nd version that does sum the total patch counts in the query as well as calculates the compliance percentage.

( id of item 0 of it, name of item 0 of it | "<none>", operating system of item 0 of it | "<none>", ( if (exists ip addresses of item 0 of it) then ((concatenations (html "<br>") of (ip addresses of item 0 of it as string)) as string) else (html "" as string) ), item 1 of it, item 2 of it, item 3 of it, (((item 2 of it) as floating point / (item 1 of it) as floating point) * 100) as integer as string & "%25") of ( it, number of ( ( results from ( fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ) of bes sites whose (name of it = "Enterprise Security") ) of it ) whose (exists first became relevants of it) ), multiplicities of unique values of ((((relevant flags of it) whose (it = FALSE)) as string) of (results from ( fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ) of bes sites whose (name of it = "Enterprise Security") ) of it ) whose (exists first became relevants of it) ), multiplicities of unique values of ((((relevant flags of it) whose (it = TRUE)) as string) of (results from ( fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ) of bes sites whose (name of it = "Enterprise Security") ) of it ) whose (exists first became relevants of it) ) ) of bes computers

Any tips you can give me to make either of these run much faster than they currently do would be very appreciated.

1 Like
2

The starting point is that you have 3 times the evaluation of this relevance

(results from (
fixlets whose (
source severity of it does not contain “Unspecified” and
source severity of it does not contain “N/A” and
source severity of it is not “” and
(number of actions of it) as integer > 0 and
globally visible flag of it = TRUE and
(source release date of it) > date “01 Jan 2000”
)
of bes sites whose (name of it = “Enterprise Security”) ) of it )
whose (exists first became relevants of it) ) of bes computers

Create a single array and then you can extract data from it by using the multiplicity and number of clauses. If you create it once and then you reference to “it” for the operations you may get a 1/3 of the evaluation time.
Then you may try to reduce repetition of “source severity” evaluation (3 times).

1 Like
3

I take it this is windows only?

The first thing I would do is instead of using bes computers is I would use only the computers that are subscribed to the patching site: (Since these are the only computers that would have valid data)

number of subscribed computers of bes sites whose (name of it = "Enterprise Security")

(Note: I’m using number of just to show that this is returning results)

1 Like
4

Tuples and Sets will cure your woes! It will take me a while to refactor this, but the approach I will try is to collect a set of BES Fixlets as well as a set of BES Computers at my base tuple, then do all the calculations based on those sets.

1 Like
5

next step is to get the computer set and the list of fixlets just once:

number of items 1 of (subscribed computer sets of it, fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "") of it ) of bes sites whose (name of it = "Enterprise Security")

Again, I’m using number of just to show something, you would drop it to continue forward.

Then from here, probably look at: https://developer.bigfix.com/relevance/reference/bes-fixlet-result.html

1 Like
6

And also observe that remediated flag of it is much faster than “exists first became relevants of it” and should still accomplish what you’re looking for.

2 Likes
7

I’ve been looking at the same…sadly there’s no “bes fixlet result set” type, only a “bes fixlet set” so there is still much iterating to do.

8

I’m not sure this is exactly the right approach, because you’d probably want to collapse the results in a per computer or per patch basis, but this would be broken out individually computer * patch:

number of (results it) whose(remediated flag of it OR relevant flag of it) of (subscribed computers of it, fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "") of it ) of bes sites whose (name of it = "Enterprise Security")

In this case, (remediated flag of it OR relevant flag of it) should be the results that matter, meaning any fixlet that is currently relevant, or was once relevant. Fixlets that were never relevant include many that don’t apply to the system in question at all, while others would be those that were remediated before bigfix measured it as being relevant.

If you run the above session relevance with number of in front, you should get a best case idea of how long it takes to get the raw data for a particular deployment / root server / etc… You’ll most likely never be able to compile the data using a single session relevance query any faster than what the most optimal version of the above returns. If you are going to try to get the most optimal version of this, then you have to use relevance like the above and measure it when filtering to the right set of fixlets in different ways. ( like changing the order or the approach for source severity )

This approach seems slightly faster than the above in this post:

number of (results it) whose(remediated flag of it OR relevant flag of it) of (subscribed computers of it, fixlets whose ( exists source severity whose(it does not contain "Unspecified" AND it does not contain "N/A" AND it is not "") of it AND globally visible flag of it) of it ) of bes sites whose (name of it = "Enterprise Security")

Meanwhile, this option appears to be much slower:

number of (results it) whose(remediated flag of it OR relevant flag of it) of (subscribed computers of it, fixlets whose ( exists source severity whose(it is not contained by set of ("Unspecified";"N/A";"") ) of it AND globally visible flag of it) of it ) of bes sites whose (name of it = "Enterprise Security")

Also, anything that can be calculated using JavaScript, like the compliance %, probably should be. DataTables can do this in a way similar to Excel where the values of one column are calculated based upon the others. DataTables can also collapse duplicates in a column. I generally use DataTables for all of my console dashboards & web reports: https://github.com/jgstew/bigfix-content/tree/master/dashboards

FYI a console dashboard can do anything a custom web report can do, and more.

I’ll have to pull apart the relevance in the original post some other time to wrap my head around it. It would be helpful if you could provide a screenshot or an example of what you want the final data to look like.

1 Like
9

I took an approach similar to @jgstew’s but I kept the individual computers so you could pull more properties from them. The key is to create a set of bes computers, and a set of bes fixlets (that we care about). We are going to have to iterate through the bes fixlet results for each computer and each fixlet, so we’d like to keep those two sets as small as possible at the front of the query. The following two queries evaluate quite quickly on my deployment (I have a little over five thousand computers in my deployment, 1,465 of which are Windows):

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 actions of it and exists source severity whose (it does not contain "Unspecified" and it does not contain "N/A" and it is not "") of it and (source release date of it) > date "01 Jan 2000" and exists results whose (remediated flag of it or relevant flag of it) of it) of bes sites whose (name of it = "Enterprise Security")

This reduces my fixlet count down to 1,703 and my computer count to 1,465.

I generate those two sets first, and then iterate through each computer to find the result counts for each computer:

q: (name of item 0 of it, number of results (item 0 of it, elements of item 1 of it) whose (relevant flag of it), number of results (item 0 of it, elements of item 1 of it) whose (remediated flag of it or relevant flag of it)) of (elements of item 0 of it, 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 actions of it and exists source severity whose (it does not contain "Unspecified" and it does not contain "N/A" and it is not "") of it and (source release date of it) > date "01 Jan 2000" and exists results whose (remediated flag of it or relevant flag of it) of it) of bes sites whose (name of it = "Enterprise Security") )
A: computer1, 5, 32
A: computer2, 5, 41
A: computer3, 1, 9
A: computer4, 0, 51

Rather than retrieving the name of item 0 of it you could leave that as a computer object and break out the results in an additional step such as

q: (name of item 0 of it, concatenation ";" of (ip addresses of item 0 of it as string), item 1 of it, item 2 of it, (100 - (item 1 of it as floating point / item 2 of it as floating point * 100)) as integer as string & "%25") of (item 0 of it, number of results (item 0 of it, elements of item 1 of it) whose (relevant flag of it), number of results (item 0 of it, elements of item 1 of it) whose (remediated flag of it or relevant flag of it)) of (elements of item 0 of it, 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 actions of it and exists source severity whose (it does not contain "Unspecified" and it does not contain "N/A" and it is not "") of it and (source release date of it) > date "01 Jan 2000" and exists results whose (remediated flag of it or relevant flag of it) of it) of bes sites whose (name of it = "Enterprise Security") )
A: computer1, 192.168.1.1, 5, 32, 84%
A: computer2,192.168.1.2, 5, 41, 88%
A: computer3,192.168.1.3;192.168.1.4, 1, 9, 89%
A: computer4, 10.10.1.1, 0, 51, 100%
3 Likes
10

I should also say, this query

q: (name of item 0 of it, concatenation ";" of (ip addresses of item 0 of it as string), item 1 of it, item 2 of it, (100 - (item 1 of it as floating point / item 2 of it as floating point * 100)) as integer as string & "%25") of (item 0 of it, number of results (item 0 of it, elements of item 1 of it) whose (relevant flag of it), number of results (item 0 of it, elements of item 1 of it) whose (remediated flag of it or relevant flag of it)) of (elements of item 0 of it, 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 actions of it and exists source severity whose (it does not contain "Unspecified" and it does not contain "N/A" and it is not "") of it and (source release date of it) > date "01 Jan 2000" and exists results whose (remediated flag of it or relevant flag of it) of it) of bes sites whose (name of it = "Enterprise Security") )

gave me results through the Web Reports QNA page in about 12 seconds. @jgstew, on your first query to measure performance I’m seeing times of three minutes or more:

number of (results it) whose(remediated flag of it OR relevant flag of it) of (subscribed computers of it, fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "") of it ) of bes sites whose (name of it = "Enterprise Security")

I believe this is because the tuple (subscribed computers of it, fixlets whose ()...) operates as two loops…in script language style, this would work like

for (each of the 1,400 subscribed computers)
  for (each of the 13,277 fixlets in the site)
     // This part evaluates 1400x13277 times, for 18,587,800 comparisons
     
     determine whether fixlet is interesting (severity checks)
     
     if the fixlet is interesting based on the severity check...
          get the results for this fixlet on this computer
     end if
  next // next fixlet
next // next computer

The issue with that, is that if there are a thousand computers in the deployment, it has to loop a thousand times through the site just to see which fixlets are interesting.

By using (set of computers, set of fixlets) we create exactly one result - and have only looped through the list of computers one time, and only looped through the fixlets in the site one time.

When I examine the results (elements of set of computers, elements of set of fixlets), at this point I’m only examining the 1,703 fixlets that were once relevant, instead of examining all 13,277 fixlets in the site.

…come to think of it, we should probably also include fixlet flag of it in the fixlets filter, so we can exclude all of the Tasks.

4 Likes