How to filter on last non/relevant fixlets per computer

karan801 · January 31, 2022, 6:17pm

Hello, I have been trying to grab the fixlet result data on fixlets and the computers they are outstanding/deployed on. I am having trouble filtering down the relevant fixlet data per computer I get from the REST API using the below query. I think it’s just a small syntax issue since I’m new to using bigfix relevance, so hopefully a quick one! Any assistance would be highly appreciated - thank you in advance!

How would I filter by the timestamp of what’s last relevant/nonrelevant of the bigfix fixlet results (e.g. only the last 30 days of fixlets being relevant/nonrelevant to a computer)? FYI using this relevance bigfix fixlet result as reference (link has been removed for post editing).

(name of computer of it,id of computer of it, display name of site of fixlet of it, id of fixlet of it,name of fixlet of it,(if(exists first became relevant of it) then first became relevant of it as string else "Unspecified"),(if(exists last became relevant of it) then last became relevant of it as string else "Unspecified"),(if(exists last became nonrelevant of it) then last became nonrelevant of it as string else "Unspecified")) of results from (relevant fixlets of bes computers) of bes computers whose (id of it is 353682))

(353682 is an example here, i would be running this for all computers. My goal is to limit the size of the extract we get back for easier processing).
I’m struggling with defining the whose of the results clause, which should be the bigfix fixlet results.

JasonWalker · January 31, 2022, 10:39pm

relevant fixlets of bes computers whose (id of it = 353682) will only return the fixlets that are relevant, and still relevant right now. It would exclude a fixlet that was remediated yesterday (the result is no longer ‘relevant’)

results from (relevant fixlets of bes computers) of bes computers whose (id of it is 353682) is looking at every fixlet that is relevant on any computer in your deployment, and getting a result between that fixlet and the one computer you’re looking at; that result may be Relevant, Not Relevant, Fixed, etc.

I’m not sure which you’re trying to do…I’ll make an assumption that you want any fixlet that is currently, or has been, relevant on computer 353682 within the last 30 days. That requires checking the results of every fixlet on that computer, not just the ones currently relevant, so it can be an expensive query.

Very quickly we get into questions of efficiency, and for the authority on that I’d refer to Efficient Session Relevance Query for Computer Properties

The short version is that when I lookup all fixlet results on one computer, there are two basic ways to do it:

number of results (bes fixlets, bes computers whose (name of it = "BES-ROOT"))

number of (results (bes fixlets, it)) of bes computers whose (name of it = "BES-ROOT")

Both of these give me the same number, 95,487. The first query takes a little over 2 seconds to run, while the second only takes 21 milliseconds. The difference is in how the query is structured.

(bes fixlets, bes computers whose (name of it = "BES-ROOT")) creates a cross-product - for every BES Fixlet that exists, we perform a separate computer search across all computers to find the one named “BES-ROOT”. Since I have over 95 thousand fixlets, the first query will search across my BES Computers 95 thousand times, once for each fixlet.
In the second form of the query, I perform the ‘BES Computer’ search only once, and pass that result in as the ‘it’ reference. This is much faster, as I only perform the ‘BES Computer’ search once, and then reuse that result for each of the 95k Fixlet lookups.

However, out of those 95k results, almost all of them are going to be ‘not relevant’, and they’ve never been relevant. My Server has never been relevant to a Workstation fixlet, my Windows machine has never been relevant to a Linux fixlet, etc.

The next step, is that I’m only interested in those results that are relevant now, or have been relevant at some point in the past. For the ‘bes fixlet result’ object, we have the ‘relevant flag’ and ‘remediated flag’, which are faster to lookup than the ‘first became relevant’ and ‘last became’ relevant times, so I’d prefer to use those first.

(And here, a sidebar - the ‘remediated flag of it’, ‘last became relevant of it’, and ‘last became nonrelevant of it’ can only be used in Web Reports or REST API; these won’t give expected results in the Console Debugger or Console Dashboards).
I like to test using the Web Reports QNA page at https://servername:8083/webreports?page=QNA

Q: number of (results (bes fixlets, it)) whose (relevant flag of it or remediated flag of it) of bes computers whose (name of it = "BES-ROOT")
    A: 1127

1127 fixlet results is a much more manageable size and this query is still pretty responsive. Next is to filter for your ‘30 days’

Q: number of (results (bes fixlets, it)) whose ((relevant flag of it or remediated flag of it) AND (now - last became relevant of it < 30 * day or now - last became nonrelevant of it < 30 * day)) of bes computers whose (name of it = "BES-ROOT")
A: 78

We could make a shorter query by using something like (now - maximum of (last became relevant of it; last became nonrelevant of it) < 30 * day), but spelling it out th elong way with the ‘or’ is slightly more efficient because we don’t have to evaluate the ‘last became nonrelevant’ of it at all if the ‘last became relevant of it’ is satisfied.

Now we have the ‘bes fixlet results’ we want and just need to retrieve the properties to display. I prefer using the pipe operator " | " to handle an error-trap in case the property we’re retrieving doesn’t exist -
last became nonrelevant of it as string | "Unspecified" just looks cleaner to me than if exists (last became nonrelevant of it) then (last became nonrelevant of it as string) else "Unspecified"
I also like to include the ‘relevant flag of it’, which gives a True/False for whether the fixlet is relevant right now…

Q: (name of computer of it | "No Name", id of computer of it, display name of site of fixlet of it, id of fixlet of it, name of fixlet of it, first became relevant of it as string | "Unspecified", last became relevant of it as string | "Unspecified", last became nonrelevant of it as string | "Unspecified", relevant flag of it) of (results (bes fixlets, it)) whose ((relevant flag of it or remediated flag of it) AND (now - last became relevant of it < 30 * day or now - last became nonrelevant of it < 30 * day)) of bes computers whose (name of it = "BES-ROOT")
A: BES-ROOT, 1582295, BES Inventory and License, 1, Install or Upgrade Scanner (9.2.23), ( Sat, 16 Oct 2021 12:05:20 -0600 ), ( Tue, 25 Jan 2022 11:06:37 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BES Inventory and License Test, 1, Install or Upgrade Scanner (9.2.23), ( Wed, 22 Dec 2021 14:40:23 -0600 ), ( Tue, 25 Jan 2022 11:06:37 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 1, Install or Upgrade Scanner (10.0.7.0), ( Sat, 16 Oct 2021 12:05:20 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 2, Initiate Software Scan (10.0.7.0), ( Fri, 22 Oct 2021 11:49:12 -0600 ), ( Tue, 25 Jan 2022 10:59:32 -0600 ), ( Tue, 25 Jan 2022 10:26:25 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 3, Upload Software Scan Results (10.0.7.0), ( Fri, 29 Oct 2021 10:19:17 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), ( Tue, 18 Jan 2022 10:53:40 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 4, Uninstall Scanner (10.0.7.0), ( Fri, 22 Oct 2021 11:49:12 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 6, Change Scanner Cache Folder (10.0.7.0), ( Fri, 22 Oct 2021 11:49:12 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 17, Configure Scan Cache (10.0.7.0), ( Fri, 22 Oct 2021 11:49:12 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 18, Install or Upgrade Scanner in private mode on Windows (10.0.7.0), ( Sat, 16 Oct 2021 12:05:20 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), True
A: BES-ROOT, 1582295, BigFix Inventory v10, 90, Update the Log4j library in VM Manager Tool to version 2.17.1 - (10.0.7.0.1), ( Tue, 11 Jan 2022 11:48:06 -0600 ), ( Tue, 11 Jan 2022 11:48:06 -0600 ), Unspecified, True
A: BES-ROOT, 1582295, BigFix Inventory v10, 114, Interrupt BigFix Inventory Software Scans, ( Fri, 29 Oct 2021 09:49:41 -0600 ), ( Tue, 25 Jan 2022 10:27:28 -0600 ), ( Tue, 25 Jan 2022 11:01:13 -0600 ), False

…

The next problem I see here is that I’m showing results for a bunch of fixlets I don’t want. I have some “Uninstall” things in there, some Tasks, some things that maybe can’t be fixed (no Default Actions), so it’s time to add some filters on which BES Fixlets I want to include.

You might have any number of criteria like ‘category’ or ‘severity’, for brevity (as if brevity were even possible at this point), I’ll just use the ones that have ‘fixlet flag of it’ and have a default action, by adding a filter at the ‘bes fixlets’

Q: (name of computer of it | "No Name", id of computer of it, display name of site of fixlet of it, id of fixlet of it, name of fixlet of it, first became relevant of it as string | "Unspecified", last became relevant of it as string | "Unspecified", last became nonrelevant of it as string | "Unspecified", relevant flag of it) of (results (bes fixlets whose (fixlet flag of it and exists default action of it), it)) whose ((relevant flag of it or remediated flag of it) AND (now - last became relevant of it < 30 * day or now - last became nonrelevant of it < 30 * day)) of bes computers whose (name of it = "BES-ROOT")

…and that…got a lot slower. The problem is that I’m now looking for the ‘fixlet flag’ and ‘default action’ on all 95k fixlets and not just on the ones that are relevant to this computer…so it’s actually faster to apply those filters onto the ‘bes fixlet result’, right after we check for the relevant/remediated flags and times…

Q: (name of computer of it | "No Name", id of computer of it, display name of site of fixlet of it, id of fixlet of it, name of fixlet of it, first became relevant of it as string | "Unspecified", last became relevant of it as string | "Unspecified", last became nonrelevant of it as string | "Unspecified", relevant flag of it) of (results (bes fixlets, it)) whose ((relevant flag of it or remediated flag of it) AND (now - last became relevant of it < 30 * day or now - last became nonrelevant of it < 30 * day) AND (fixlet flag of fixlet of it and exists default action of fixlet of it)) of bes computers whose (name of it = "BES-ROOT")

A: BES-ROOT, 1582295, Updates for Windows Applications Extended, 200101, Update: 7-Zip v21.07 - Windows (x64), ( Mon, 06 Dec 2021 15:28:24 -0600 ), ( Fri, 07 Jan 2022 15:39:56 -0600 ), ( Mon, 06 Dec 2021 15:31:13 -0600 ), True

A: BES-ROOT, 1582295, Updates for Windows Applications, 4001200, Notepad++ (x64) 8.1.9.3 Available (Superseded), ( Thu, 09 Dec 2021 09:46:04 -0600 ), ( Thu, 09 Dec 2021 09:46:04 -0600 ), ( Tue, 25 Jan 2022 09:53:40 -0600 ), False

A: BES-ROOT, 1582295, Updates for Windows Applications, 4001204, Notepad++ (x64) 8.2.1 Available, ( Tue, 25 Jan 2022 09:55:22 -0600 ), ( Tue, 25 Jan 2022 09:55:22 -0600 ), Unspecified, True

A: BES-ROOT, 1582295, Updates for Windows Applications Extended, 11500101, Update: Wireshark v3.6.1 - Windows (x64), ( Mon, 06 Dec 2021 15:28:24 -0600 ), ( Fri, 07 Jan 2022 15:41:33 -0600 ), ( Mon, 06 Dec 2021 15:32:14 -0600 ), True

karan801 · February 1, 2022, 4:22pm

Thanks so much Jason!! This is very helpful and educational on how Relevance queries/works efficiency wise, on top of solving my problem.

However, I am noticing that some of the results I get back for certain computers come with this response of ‘Plurality’ but no evaluation response (but not erroring). Some objects do return values, but it does make me wonder about why some computers don’t return any values?

I tried to play around with the plurality of the objects called, but realized that it could be something else based off this post - Query returning "Plural" and no other results - #4 by JasonWalker. Would no results confirm that the device doesn’t have any fixlets to return with the filters applied?

JasonWalker · February 1, 2022, 4:24pm

It would seem to, or perhaps it’s worth checking those computers in the Console or their client logs to be sure they’re reporting correctly and are receiving updated sites.

One “interesting” thing about this query is that it’s only concerned with fixlets that became relevant in the last month. The machine may be missing a hundred patches, but if they are all two months old they drop off the list.

karan801 · February 16, 2022, 6:48pm

Yes, that’s true! As of now though we are only interested to report on those within the last month, and over time we’ll collect the data of all patches. Also this query works great and we are getting the information we want (in terms of closing the original question).

Technically though, with relevance, is it not that relevance is run every time someone looks at a particular patch? which would then update last became relevant and get grabbed in this query?