Ahh, a good problem to illustrate the issues with a Cross Product. The main performance factor I’d expect here would not be the number of computers, but the number of fixlets.
Here, you are basically retrieving every fixlet twice. The first time you retrieve every fixlet to count the computers that are still relevant to it; the second time, you retrieve every fixlet and count the computers that are not relevant, but once were relevant.
The issue then, is that every fixlet from the first group is paired to every fixlet of the second group.
To illustrate, consider this cross-product. Think of “a”, “b”, and “c” as “the first set of fixlets, that have relevant computers”, and “1”, “2”, “3” as “the second set of fixlets, that once had relevant computers”.
q: (("a";"b";"c"), ("1"; "2"; "3"))
A: a, 1
A: a, 2
A: a, 3
A: b, 1
A: b, 2
A: b, 3
A: c, 1
A: c, 2
A: c, 3
T: 0.121 ms
I: plural ( string, string )
The total number of results from this would be (number of relevant fixlets X number of once-relevant fixlets).
If you have one hundred fixlets in each set, there would be ten thousand results. Each of those ten thousand results is retrieving a count of the relevant results for the first fixlet, and the once-relevant results for the second fixlet - that’s a count calculation that would be performed some twenty thousand times.
What would be more efficient, is to retrieve each “interesting” fixlet one time, and then count both the relevant and once-relevant computers for each fixlet. That avoids the cross-product, by not pairing every fixlet to every other fixlet.
Something like
(
number of computers of results whose (relevant flag of it and last became relevant of it as universal date < "${req.query.dateUpper}" as date and last became relevant of it as universal date > "${req.query.dateLower}" as date) of it
, number of computers of results whose (remediated flag of it and last became nonrelevant of it as universal date < "${req.query.dateUpper}" as date and last became nonrelevant of it as universal date > "${req.query.dateLower}" as date) of it
, name of it
) of bes fixlets whose (
(fixlet flag of it or task flag of it or baseline flag of it)
and name of it as lowercase does not contain "corrupt patch"
and name of it as lowercase does not contain "superseded"
)
This will return a three-item tuple. For that hypothetical set of 100 fixlets, you would now have a total of 100 results in the form of
( # of currently-relevant computers, # of once-relevant computers, name of fixlet)
You also may want to add a whose () filter on the result tuple. A fixlet that is not now relevant, and never was relevant, may not be needed in the result. So you could trim that further to include only the ones where the “currently relevant” or “recently fixed” are not zero -
(
number of computers of results whose (relevant flag of it and last became relevant of it as universal date < "${req.query.dateUpper}" as date and last became relevant of it as universal date > "${req.query.dateLower}" as date) of it
, number of computers of results whose (remediated flag of it and last became nonrelevant of it as universal date < "${req.query.dateUpper}" as date and last became nonrelevant of it as universal date > "${req.query.dateLower}" as date) of it
, name of it
) whose (item 0 of it > 0 or item 1 of it > 0 )
of bes fixlets whose (
(fixlet flag of it or task flag of it or baseline flag of it)
and name of it as lowercase does not contain "corrupt patch"
and name of it as lowercase does not contain "superseded"
)
You should note though that is is retrieving all of the fixlets, tasks, and baselines from all of the sites. The next optimization would be to reduce the number of fixlets/tasks that you’re examining. Rather than our hypothetical set of 100 fixlets, you’re likely actually checking more like thousands, and yielding millions of results now. Probably a lot more than you’d want. I’d suggest adding a filter on the the ‘bes fixlets’ to only retrieve the Sites that you need, such as
name of site of it = "Enterprise Security"
for the “Patches for Windows” site. Otherwise you’ll also be retrieving everything from “BES Support”, “Master Action Site”, etc.
It’s also worth knowing that many Tasks will always be relevant, and usually don’t mean that the system is misconfigured or missing anything; a task for “Restart a service” for instance may always be relevant, so you might not want to report on the Tasks either.
edit: I had a copy/paste issue, and was counting the “remediated” computers twice and not counting the “relevant” computers at all. Fixed above.