Session Relevance to query relevant patches per computer? Better with Explorer filters?

Content Authoring
1

We have a request to provide the necessary data for an external Tableau dashboard to show a percentage of computers (in a subset of computers) that have critical patches that have been relevant for more than 14 days. While I’m pretty good with Client Relevance, Session Relevance still gives me the willies. :grimacing: Still, I managed to come up with this:

(name of computers of it, source severity of fixlets of it, name of fixlets of it, first became relevant of it) of results ( computers of results (bes computers, bes properties whose (name of it = "Department")) whose (value of it = "HR") , bes fixlets whose ( fixlet flag of it is true AND globally visible flag of it is true AND source severity of it as lowercase = "critical" AND applicable computer count of it > 0 AND ( name of site of it starts with "Patches" OR name of site of it starts with "Updates" OR name of site of it = "Enterprise Security") ) ) whose (relevant flag of it is true)

First question is if this can be made more efficient. It takes a couple minutes to run on a small test selection and I’m worried how long it will take for the intended target of several thousand computers. :open_mouth: I also tried doing AND ( it starts with "Patches" OR it starts with "Updates" OR it = "Enterprise Security" of (name of site of it)) but the first its kept referring to an earlier object instead of the (name of site of it). :confused:

Second question is whether or not this would be greatly improved by the 11.0.3 Explorer API and its Filters. :thinking:

Thoughts?

2

Good work on your starting point!

The trick is getting to filtered as soon as possible.

In this case, I would get to Fixlets from bes sites as the first hop and applicable computer count as second.

q: set of fixlets whose (fixlet flag of it AND globally visible flag of it AND source severity of it as lowercase = "critical" AND applicable computer count of it > 0  ) of  bes sites whose ( name of it starts with "Patches" OR name of it starts with "Updates" OR name of it = "Enterprise Security")

And I think for the computer part, starting with the property will probably be the fastest/most efficient…

q: set of computers of results whose (value of it = "HR") of bes properties whose (name of it = "Department")

Wrap with some sets then expand the computers back out to get the “computer stuff” And then expand out Fixlets for the fixlet stuff and Results.

q: (names of item 1 of it, source severities of item 0 of it, name of item 0 of it, (first became relevants of results (item 0 of it, item 1 of it) whose (relevant flag of it))) of  (elements of item 0 of it, elements of item 1 of it) of ((set of fixlets whose (fixlet flag of it AND globally visible flag of it AND source severity of it as lowercase = "critical" AND applicable computer count of it > 0  ) of  bes sites whose ( name of it starts with "Patches" OR name of it starts with "Updates" OR name of it = "Enterprise Security") ),(set of computers of results whose (value of it = "HR") of bes properties whose (name of it = "Department")))

(afternote - first became relevant of it is generally expensive no matter how you slice it - also, fixed a missing relevant flag of it)

4 Likes
3

Hmmm… I have some more reading to do. (/me searches for “what are bigfix relevance sets”…)

Also, I tried putting in the production group instead of my test group and hit Evaluate in the Session Relevance Tester… about an hour ago… it’s still running… :open_mouth:

I think I’m gonna kill the process and put in your suggestions before hitting Evaluate again…

Thanks!

1 Like
4

One of my favorite tricks for testing at scale is to add in some computer/filter fixlets with MOD function, like this.

bes computers whose (id of it mod 10000=0)
bes fixlets whose (id of it mod 10000=0)

Then you can slowly reduce the 10000 to 1000 and 100 and 10… and so on, to get larger and larger slices.

2 Likes
5

That’s brilliant. I’ve used mod to vary workloads (mod 10 = 0 today, then mod 10 = 1 tomorrow, mod 10 = 2 the next day) but wouldn’t have thought to use that to cut down the size of a group to a test slice. Thank you!

Also, I’ve run your optimized code… less than 2 minutes, or about the time it took to run my first test group with my code. :rofl:

Thanks again!

6

So, if I understand the logic correctly, my initial code without sets ended up asking many of the same questions repeatedly, whereas the code with sets collects the results from the database once and uses those local results repeatedly.

And, simplified, the skeleton would be:
(property of item 0 of it, property of item 1 of it, etc,
property of results (item 0 of it, item 1 of it) whose (condition of it))
of (elements of item 0 of it, elements of item 1 of it)
of (set of fixlets, set of computers)
with the sets being boxes of objects, the elements being those objects from inside each box, and the properties and results operating on the exposed elements, and the only time the database was really hammered against was to create the boxes (sets).

Is that right? (Or close enough? :rofl: )

1 Like
7

That is the core learning, yes!

A tuple is a cross product of “the things”
a set is 1 thing.
1 x 1 = 1 things big.

q: number of ((1;2;3) , (1;2;3))
A: 9
T: 0.077 ms
I: singular integer

q: number of (set of (1;2;3) , set of (1;2;3))
A: 1
T: 0.053 ms
I: singular integer
8

But busting them back out into elements of takes us back to 9, but even faster still?? :open_mouth:

q: number of ((1;2;3) , (4;5;6))
A: 9
T: 0.156 ms
I: singular integer

q: number of (set of (1;2;3) , set of (4;5;6))
A: 1
T: 0.127 ms
I: singular integer

q: number of (elements of item 0 of it, elements of item 1 of it) of (set of (1;2;3), set of (4;5;6))
A: 9
T: 0.068 ms
I: singular integer

I need to try this with bigger sets… :thinking:

9 
q: number of ((1;2;3;4;5;6;7;8;9;0) , (1;2;3;4;5;6;7;8;9;0))
A: 100
T: 0.194 ms
I: singular integer

q: number of (set of (1;2;3;4;5;6;7;8;9;0) , set of (1;2;3;4;5;6;7;8;9;0))
A: 1
T: 0.151 ms
I: singular integer

q: number of (elements of item 0 of it, elements of item 1 of it) of (set of (1;2;3;4;5;6;7;8;9;0), set of (1;2;3;4;5;6;7;8;9;0))
A: 100
T: 0.083 ms
I: singular integer

These timing results make no sense to me. :confounded:

10

Timing part 1:
I’m afraid there is a bug in the timing displayed in seveal versions of the Fixlet Debugger, including the current 11.0.2 version of the debugger. This has confused me before as well. I regard this as a bug but haven’t had much traction asking for a fix.

When there are multiple queries in the QNA tab, the time displayed on each query is the time to run that query plus every query that comes after it. There’s something I think is incorrect in the time offset calculation.

Take the following example - all these are in one QNA tab

// It does *not* take 210 ms to return 'version of client'
q: version of client
A: 11.0.2.125
T: 210.105 ms
I: singular version

 // It does *not* take 209 ms to count 54 files in c:\temp
q: number of files of folders "c:\temp"
A: 54
T: 209.715 ms
I: singular integer

// It does *not* take 207 ms to return the static integer '1'
q: 1
A: 1
T: 207.163 ms
I: singular integer

// Ah, calculating the sha1 values of those 54 files in c:\temp may take some time.  205 ms in fact.  This 205 ms is what makes all the queries above appear to take so long.  The 2.5 ms from the query after is also included to make the time display here 207 ms.

q: number of sha1s of files of folders "c:\temp"
A: 54
T: 207.137 ms
I: singular integer

// See, it *doesn't* take long to count the 54 files in c:\temp
q: number of files of folders "c:\temp"
A: 54
T: 2.588 ms
I: singular integer
1 Like
11

Timing Part 2:
With these static sets, it’s hard to see a difference in time doing it one way or the other. These values take almost no time at all to retrieve, since they’re just static integer values.

The thing to consider with cross-products, is that the time required to look up an element of the cross-product is part of what makes it so bad.
In your original query the start of the framework is basically

(bes computers whose (some filter), bes fixlets whose (some filter)

With this cross product, for each computer that matches the computer filter, it performs a lookup of every fixlet in the deployment, and then filters down to just the fixlets you want.

If it takes 1 second to lookup up all the fixlets and filter to the ones you want, but your computer query returns 100 computers, then it performs that fixlet lookup 100 times for a total 100 seconds of fixlet searching. The search and filter is repeated for each tuple result.

By instead putting those into a set first, we perform that fixlet lookup only one time. Once it’s in the set, unwinding the elements of the set does not require performing a new search across every fixlet and filter those to match the condition.

Take my small test deployment:

// looking up computer ids is pretty fast - I only have 11 computers and it takes 2 milliseconds
number of ids of bes computers
11
2 ms

// Looking up fixlet IDs is also pretty fast - I have 287,440 fixlets and it took 31 milliseconds to look them up.
number of ids of bes fixlets
287440
31 ms

// These are both pretty fast operations - but creating a tuple cross-product means the 31 ms Fixlet lookup is repeated 11 times.  I expected about 362 milliseconds, and the actual is pretty close...
number of (ids of bes computers, ids of bes fixlets)
3154030
314 ms

// Creating *sets*  for bes computers and bes fixlets means the lookups (and filtering!) only happens once instead of repeating 11 times.  Then retrieving the elements from the set is faster than repeating the lookup.
number of (ids of elements of item 0 of it, ids of elements of item 1 of it) of (set of bes computers, set of bes fixlets)
3154030
194 ms

It can be hard to measure the differences when “each time through the loop” is so fast, but it really makes a huge difference when we scale up the “number of times through the loop” (number of computers) and the “duration of each loop” (how long the lookups and filtering takes)

1 Like
12

Ah… that makes sense. As a test, I had commented out the first two and saw that the last one was still just as fast, but neglected trying either of the first two without the others to see if they were just as slow. Doing that now shows dramatically shorter times for the first two. Thanks!

13

Ah! This! So, the elements of sets construct is not preventing the cross product, it’s just doing the cross product with information that was only searched for once instead of count of elements of item 0 times. In a data set of 10 computers and 20 fixlets, it does 30 lookups instead of 200 (or, with 1,000 and 2,000, it does 3,000 instead of 2,000,000). Right?

1 Like
14

Yes, that’s pretty much it, but actually even better.

In the 10x20 case, it only performs two lookups - one lookup of computers with 10 results, and one lookup of Fixlets with 20 results. Only two searches have to happen.

When unwinding the sets, we can end up with 10x20=200 unique combinations, but retrieving those combinations from the set takes almost no time at all, compared with the searches that put those elements into the set to begin with.

1 Like