Fixlet Remediation History By Computer Name

[JasonWalker]

What I like to do is to get the query working and optimized in the QNA page first, before putting any HTML formatting on it at all. I’ve shortened the query to

q: ((name of computer of it | "Name Not Reported") ,name of fixlet of it , (if (exists source release date of fixlet of it) then ((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of source release date of fixlet of it) else ("Unspecified")) , (if (source severity of fixlet of it as lowercase = "<unspecified>") then ("Unspecified") else (source severity of fixlet of it))  ,((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of  date(local time zone) of it & " "& (two digit hour of it as string & ":" & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last became nonrelevant of it) of results from (bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true)) whose (exists last became relevant of it and relevant flag of it = false and ((date (local time zone) of last became nonrelevant of it) >= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "01-01-2021") as date)) and (date (local time zone) of last became nonrelevant of it) <= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "02-03-2021") as date)) of members of bes computer groups whose (name of it = "Windows Servers")

and I’ll look at error trapping and optimizing now.

Then, when done, I’d put the table header in the HTML itself, and at the front of the query just add concatenation of trs of (concatenation of tds of (item 0 of it as string; item 1 of it as string; item 2 of it as string)) of ...

[JasonWalker]

Try this in your QNA page, and then we’ll narrow down to see which property might be giving you a problem – replace the computer group name with your real group

q: number of results from (bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true)) whose (exists last became relevant of it and relevant flag of it = false and ((date (local time zone) of last became nonrelevant of it) >= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "01-01-2021") as date)) and (date (local time zone) of last became nonrelevant of it) <= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "02-03-2021") as date)) of members of bes computer groups whose (name of it = "Windows Servers")

[Austin4778]

This returned 0

[JasonWalker]

Something definitely strange there.

Can you see if it’s retrieving your computer group properly?

Q: number of members of bes computer groups whose (name of it = "Your Group Name")

See if it’s retrieving the fixlets properly?

Q: number of bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true)

See if it’s getting results (without the time filters - this one might take a LONG time)

Q: number of results from (bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true))  of members of bes computer groups whose (name of it = "Windows Servers")

[Austin4778]

Can you see if it’s retrieving your computer group properly?

• Confirmed member count came back correct. A: 14

See if it’s retrieving the fixlets properly?

• Confirmed. A: 15177

See if it’s getting results (without the time filters - this one might take a LONG time)

• Confirmed. A: 212478

[JasonWalker]

This stub of the query is many times faster. It works by looking up the computer list once, the fixlet list once, and then retrieving results for each fixlet/computer pairing.

The original query repeats the fixlet list lookup for every lookup.

q: number of (results (item 0 of it, item 1 of it)) whose (exists last became relevant of it and relevant flag of it = false and ((date (local time zone) of last became nonrelevant of it) >= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "01-01-2021") as date)) and (date (local time zone) of last became nonrelevant of it) <= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "02-03-2021") as date)) of (elements of item 0 of it, item 1 of it) of (item 0 of it, elements of item 1 of it) of ( set of bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true), member set of  bes computer groups whose (name of it = "Windows Servers") )

[JasonWalker]

…are you sure you have at least one patch remediated on one of these computers between 1-01-2021 and 2-03-2021 ?

I wonder whether you have some regional settings in effect? The query is expecting the dates in the form MM-DD-YYYY, does your locale put day before month?

[Austin4778]

Yes. I just went back and tripled check too :). Checked on the endpoint itself, past BF action, ensured this server is a member of this group. Also tried creating a new group in the master site, adding this specific server into that group to see if that had any play, and still no dice. It’s a strange one for sure.

[JasonWalker]

Ok, then, getting the 212478 results is a really good sign anyway.

Are there any that have been fixed?

q: number of results from (bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true)) whose (exists last became relevant of it and not relevant flag of it ) of members of bes computer groups whose (name of it = "Windows Servers")

What if we expand the date range? This one goes back to Jan 2020

q: number of results from (bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true)) whose (exists last became relevant of it and relevant flag of it = false and ((date (local time zone) of last became nonrelevant of it) >= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "01-01-2020") as date)) and (date (local time zone) of last became nonrelevant of it) <= (((last 2 of first 5 of it & " " & first 2 of it as integer as month as three letters & " " & last 4 of it) of "02-03-2021") as date)) of members of bes computer groups whose (name of it = "Windows Servers")

Another thing to keep in mind, is that for this to give a result, the computer must have reported the fixlet relevant at some point. If the computer has been reset, i.e. BES Client reinstalled after applying the patches, or if these are new computers that were already up-to-date before they onboarded, they would have never reported ‘relevant’ on the fixlet and thus won’t show up as remediated now

[Austin4778]

Are there any that have been fixed?

• This came back with 0. Tried 3 or 4 different groups I know have also been patched, but still 0 as a result.

What if we expand the date range? This one goes back to Jan 2020

• This comes back with 0 as well

Completely understand your last point as well. We know for certain that the machines here have not been rebuilt or anything like that. Most of these machines have been around for quite some time. We ran the last statement against a group of 20 or more servers and it is also returning 0. The last known change in our env was the implementation of of cloud plugin. I know that introduces correlation IDs n such to the native agent. I have somewhat of a feeling like that may be related here. We know it’s a reporting discrepancy because these machines have been patched via BF.

[Austin4778]

I did some digging in the DB for one of the endpoints in a problematic group and it’s showing results for the “Patches for Windows” aka “Enterprise Security” site. At least the data is there

[JasonWalker]

It certainly could be something there…I’m trying to get down to the simplest case I could hand off to a developer. Trying to zero-on on whether it’s a problem with group membership, or the remediation flags

If we stop looking for a specific group, do you get anything?

q: number of (results (item 0 of it, item 1 of it)) of (elements of item 0 of it, item 1 of it) of (item 0 of it, elements of item 1 of it) of ( set of bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true), set of bes computers )

Also, have any been remediated? ‘remediated flag of it’ should give the same result as ‘exists last became relevant of it and relevant flag of it = false’

q: number of (results (item 0 of it, item 1 of it)) whose (remediated flag of it) of (elements of item 0 of it, item 1 of it) of (item 0 of it, elements of item 1 of it) of ( set of bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true), set of bes computers )

(edit: in the post I deleted, I said I was going to go simpler by removing the computer group…and then I didn’t )

[JasonWalker]

I’m going to have to step away for a while - possibly the night if I shut down my rather loud server - but I’ve started a thread with the internal devs to see if any of us can repro your problem. Just wanted to let you know I’ll be offline for a while.

[Austin4778]

If we stop looking for a specific group, do you get anything?

• This returned results. A: 123707727

Also, have any been remediated? ‘remediated flag of it’ should give the same result as ‘exists last became relevant of it and relevant flag of it = false’

• This came back with results too. A: 326589

[Austin4778]

No problem at all. Greatly appreciate your assistance. Picked up a few things along the way so far too Ill continue digging and provide updates if we happen to get anywhere.

[JasonWalker]

I’m…not even sure where to say the problem might lie now…I was expecting to tear down the query and find some piece of it doesn’t report correctly, but all the individual pieces seem to be working. I’ll definitely toy with this some more later or possibly early tomorrow.

Last thing I could think to check, is to pull up fixlet details regardless of whether remediated, and then try to find some examples where the result is not correct - where you know a fixlet has been corrected. Update the computer group here, and, ideally, if you know a fixlet that has been fixed, add it to the fixlet filter for ‘name of it as lowercase’ to get faster results

q: (id of computer of it, id of fixlet of it, relevant flag of it, remediated flag of it) of (results (item 0 of it, item 1 of it)) of (elements of item 0 of it, item 1 of it) of (item 0 of it, elements of item 1 of it) of ( set of bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true), member set of bes computer group whose (name of it = "Windows Servers") )

@brolly33 any thoughts?

[Austin4778]

I ran this and it returned a ton of results. I combed through server’s BES action history and obtained the IDs of the patches applied from those previous actions. All align with the remediation flag being present with those fixlets. The only other item to me seems like the date filter not being in the right format. All other properties are returning.

[JasonWalker]

But…I thought this earlier query had 0 results? I was testing for the date formats by taking the dates out?

q: number of results from (bes fixlets whose (name of it as lowercase contains "" and display name of site of it contains "Patches for Windows" and fixlet flag of it = true)) whose (exists last became relevant of it and not relevant flag of it ) of members of bes computer groups whose (name of it = "Windows Servers")

[Austin4778]

This one returns 0 results:

where as the previous one returns results:

[Austin4778]

Interesting enough…

Peeling the statement back a bit more…