Hey Jason, i’m trying to minimize the data by targeting sites whose name of it contains “patches” (basically all patch sites). I’m trying to formulate that along with the below query, however for some reason i’m not getting the appropriate result.
(names of computers of it, names of fixlets of it, categories of fixlets of it, first became relevants of it) of results ( (bes computers), ((bes fixlets) whose ((source severity of it = “Critical”) and (applicable computer count of it > 0) and (globally visible flag of it is true)) ) )
4.5 years later, I’m finally getting back into this.
Does anyone (@JasonWalker?) have any insight into querying only those Fixlets that are currently relevant? The above relevance snippet doesn’t do it.
Apparently I just need to ask questions in order to stumble upon the answers myself. 
tacking whose (relevant flag of it = TRUE) on the end of the results section does what I was looking for. So, I’m now looking at the following (or something similar):
(ids of computers of it, names of computers of it, names of fixlets of it, categories of fixlets of it, first became relevants of it) of results ( (bes computers) whose (last report time of it > (now - 30*day)) , ((bes fixlets) whose ((source severity of it as lowercase = "critical" OR source severity of it as lowercase = "high") and (applicable computer count of it > 0) and (globally visible flag of it is true)) ) ) whose (relevant flag of it = TRUE)
It feels like this is going to take forever to run…anybody have thoughts on making it more efficient? @jgstew?
As mentioned above (and a long time ago), I also need to exclude fixlets in particular sites (like “BES Support”… WHY ON EARTH IS THE “Install BigFix Relay” FIXLET “Critical”?!). What would be the best way to do that? I’m currently trying to figure out the syntax to do name of site of fixlet not in "list1","list 2,...
Would (not exists elements of intersection of (set of (names of sites of it); set of ("BES Support";"Platform Beta";"Some Dumb Site"))) be a good way to do it?
The main speed problem there is the cross-product generated by
( BES computers whose(), BES Fixlets whose() )
For each BES Computer, it repeats the query to list & filter all the fixlets. That can take a long time.
The trick is to build a set of bes computers, and a set of bes Fixlets. Since a “set” is just a single item, that makes the computer search and the Fixlet search run only once.
I’ll write some more on that when I get back to a computer, typing on the phone now.
1 Like
On my (very small) lab deployment, this query took over seven seconds to show me 184 results:
number of results (
(bes computers) whose (last report time of it > (now - 30*day))
,((bes fixlets) whose ((source severity of it as lowercase = "critical" OR source severity of it as lowercase = "high") and (applicable computer count of it > 0) and (globally visible flag of it is true)) )
) whose (relevant flag of it = TRUE)
Refactoring it slightly, so the ‘bes computers’ and ‘bes fixlets’ are each put into a set, so they’re only queried/filtered once, then pulling the results from those already-retrieved set elements, gives the same count in 184 milliseconds. See if this is a good start:
number of (
results (elements of item 0 of it, elements of item 1 of it)
) whose (relevant flag of it) of (
set of (bes computers) whose (last report time of it > (now - 30*day))
,set of ((bes fixlets) whose ((source severity of it as lowercase = "critical" OR source severity of it as lowercase = "high") and (applicable computer count of it > 0) and (globally visible flag of it is true)) )
)
Once we have the results, you can pull the properties from the result in the same way (I added some error-checking for missing values as well)
(id of computer of it, name of computer of it | "No Name", name of fixlet of it, category of fixlet of it | "No Category", first became relevant of it as string | "None") of (
results (elements of item 0 of it, elements of item 1 of it)
) whose (relevant flag of it) of (
set of (bes computers) whose (last report time of it > (now - 30*day))
,set of ((bes fixlets) whose ((source severity of it as lowercase = "critical" OR source severity of it as lowercase = "high") and (applicable computer count of it > 0) and (globally visible flag of it is true)) )
)
1 Like
This is great! Thanks @JasonWalker. Is there anywhere that explicitly describes what’s happening when you use the results of (set 1, set 2) construct? I would have expected there to be some defining of keys or joins or something…
I feel this pain. So often.
1 Like
There’s not a whole lot on this, but there’s a bit of description on how tuples create a cross-product at https://developer.bigfix.com/relevance/guide/basics/tuples.html
What could probably use a lot more is that when building a tuple of plurals, such as
( items 0, items 1 )
i.e.
( ("a"; "b"; "c"), (1; 2; 3) )
becomes
( “a”, 1 )
( “a”, 2 )
( “a”, 3 )
( “b”, 1 )
( “b”, 2 )
( “b”, 3 )
( “c”, 1 )
( “c”, 2 )
( “c”, 3 )
the full list becomes a cross-product of every ‘item 0’ to every ‘item 1’. If either of those take a long time to retrieve (like a list of fixlets or a list of computers), that long lookup is repeated for each result in the cross-product.
Using a ‘set’ for each of those items, a ‘set’ is considered a single element, so that (relative slow) query for fixlets or computers is only run once. After the sets are built, expanding them into their individual elements still ends up with the same number of total results, but looping through the elements of the set is much faster than repeating the fixlet/computer search.
1 Like
Tuples I kinda get… I was more curious about the specific relationship between the BES Computers and BES Fixlets sets that allows then to have properties like relevant flag and first became relevant between them.
Ah, I see, yes those are properties of a bes fixlet result. For those the properties are documented at https://developer.bigfix.com/relevance/reference/bes-fixlet-result.html
Particular interest is that ‘first became relevant of it’ is only available in Web Reports, not in Console Dashboards https://developer.bigfix.com/relevance/reference/bes-fixlet-result.html#first-became-relevant-of-bes-fixlet-result-time
Um… wut? I’m getting values in my REST API queries, so I think we’re good.
Yes REST API can get that as well (REST is serviced by Web Reports). It’s just a bit of an edge case that it does not work in a custom Console dashboard or in the Console Session Debugger
1 Like
In comparison, your code, run against my production system last night, returned nearly 42 million characters of XML in just under 12 minutes. That don’t seem too shabby.
Thanks again!
1 Like
Great to hear!
At a scale like that it may still be worth trying to get more efficient. See how this one does, it reduces the time by 7x on my lab, just by changing the order of the filters on ‘bes fixlet’.
(id of computer of it, name of computer of it | "No Name", name of fixlet of it, category of fixlet of it | "No Category", first became relevant of it as string | "None") of (
results (elements of item 0 of it, elements of item 1 of it)
) whose (relevant flag of it) of (
set of (bes computers) whose (last report time of it > (now - 30*day))
,set of (bes fixlets whose (globally visible flag of it and (applicable computer count of it > 0 ) and ((it = "critical" or it = "high") of (source severity of it as lowercase)) ) )
)
On the ‘bes fixlet’ filters, the ‘globally visible flag of it’ and ‘applicable computer count of it’ can be retrieved faster (and discard more fixlets) before the ‘source severity’ string comparisons are processed. And the ‘source severity of it’ only has to be lowercased once, and then compared to the two values “high” and “critical”, instead of being lowercased once to compare to “critical” and then lowercased a second time to compare against “high”. The string lowercasing & comparison is one of the slower parts of this query, so performing that against a smaller number of fixlets (the non-relevant ones already having been discarded) improves the speed.
2 Likes
This stuff right here is pure gold. Thank you!
1 Like
Happy to help! And…would love to hear if the new comparisons are faster at your scale
Oddly enough, the second query was actually slower. 
First query: <Time>708563.921ms</Time>
Second query: <Time>729886.131ms</Time>
Granted, they were run on different days and at different times of day, and it’s only 21 seconds difference, but there it is. I may try some different filters to thin things down to both test with smaller subsets and to reduce the initial load… for instance, maybe skipping all the BigFix Compliance fixlets?