Hi everyone,
I’m working with a custom query in Web Reports that calculates the compliance level of computers within a specific group (using the group id). The query works correctly and returns the expected results, but its performance is not ideal: it takes at least 30 seconds to complete for a group of about 30 computers.
I’m looking for suggestions or best practices to optimize this query. Specifically:
- Is there a way to filter or narrow down the computers before retrieving or processing them in the query?
- Is it possible to cache or store partial results to avoid recalculating everything from scratch each time?
Please note that I’m aware filters can be applied at the report level, but my goal is to optimize the query itself, keeping the report as clean and filter-free as possible.
Below is the current query I’m using directly in Web Reports:
(
html of ((id of computer of it as string) | "Unknown"),
html of ((name of computer of it) | "Unknown"),
html of ((operating system of computer of it as string) | "Unknown"),
html of ((device type of computer of it as string) | "Unknown"),
html of (link href of computer of it),
html of (id of fixlet of it as string),
html of (name of fixlet of it),
html of (source severity of fixlet of it),
html of (link href of fixlet of it),
html of ((if (exists last became relevant of it) then (1) else (0)) as string),
html of ((if (exists last became nonrelevant of it and relevant flag of it = false) then (1) else (0)) as string),
html of ((if (relevant flag of it = true) then (1) else (0)) as string),
html of (source release date of fixlet of it as string)
)
of results whose (
computer of it is contained by set of (members of bes computer groups whose (id of it = 811))
)
of bes fixlets whose (
(
(
source severity of it as lowercase = "critical"
) or
(
source severity of it as lowercase = "important"
)
) and
(
source release date of it < (current date - 1 * week)
)
)
Any advice or insights on how to improve performance would be greatly appreciated.
Thanks so much in advance for your help!
The “go-to” explanation on this is at Efficient Session Relevance Query for Computer Properties
Basically, the way your query is written now behaves like a series of nested loops. In the outer loop, you are retrieving a list of bes fixlets matching your criteria. In the first inner loop, you are iterating through all of the results of each fixlet. Then futher within the inner loop, you retrieve a set of members of a computer group and check whether the current result is for a computer in that group.
In practice, what this means is that if there are a hundred fixlets that match your criteria, you have to lookup the computer group and retrieve its member set a hundred times.
This should be much more efficient if we lookup the fixlets first (creating a ‘bes fixlet set’), lookup the computers once (building a ‘bes computer set’, and then loop through the members of each set retrieving results (bes computer, bes fixlet).
Try changing the end of your query from
of results whose (
computer of it is contained by set of (members of bes computer groups whose (id of it = 811))
)
of bes fixlets whose (
(
(
source severity of it as lowercase = "critical"
) or
(
source severity of it as lowercase = "important"
)
) and
(
source release date of it < (current date - 1 * week)
)
)
to
of (results (elements of item 0 of it, elements of item 1 of it) ) of
(
member set of bes computer groups whose (id of it = 811)
, set of bes fixlets whose (
(
(
source severity of it as lowercase = "critical"
) or
(
source severity of it as lowercase = "important"
)
) and
(
source release date of it < (current date - 1 * week)
)
)
)
)
3 Likes
Hi,
Thank you for your help, everything is working now in terms of returning the correct data!
However, the query is still quite slow. I’m wondering if it could be due to the fact that it’s retrieving the entire set of fixlets, many of which might not be relevant to the specific computers. Is there a way to loop through each computer and then check which fixlets need to be retrieved for that particular machine, rather than querying the entire set of fixlets upfront?
Thanks again for your support!
Best regards,
Leonardo
@leonardo1998 your Fixlet details pulls at the top are happening once per computer per Fixlet… if you can move those down into the bottom tuple instead, then they will be pulled once per Fixlet.
Something like this (Untested, but the theory should demonstrate the technique) (should end up being much faster)
(
html of (item 0 of item 0 of it),
html of (item 1 of item 0 of it | "Unknown"),
html of (item 2 of item 0 of it | "Unknown"),
html of (item 3 of item 0 of it | "Unknown"),
html of (item 4 of item 0 of it),
html of (item 0 of item 1 of it),
html of (item 1 of item 1 of it),
html of (item 2 of item 1 of it),
html of (item 3 of item 1 of it),
html of ((if (exists last became relevant of item 2 of it) then (1) else (0)) as string),
html of ((if (exists last became nonrelevant of item 2 of it and relevant flag of item 2 of it = false) then (1) else (0)) as string),
html of ((if (relevant flag of item 2 of it = true) then (1) else (0)) as string),
html of (item 4 of imem 1 of it it as string)
) of (
item 0 of it
, item 1 of it
, (results (items 0 of item 0 of it, items 0 of item 1 of it) ) of
(
( it
,id of it
,name of it
,operating system of it
, device type of it
,link href of it
) of elements of item 0 of it
,
(id of it
, name of it
, source severity of it
, link href of it
, source release date of it
) of elements of item 1 of it
) of (
member set of bes computer groups whose (id of it = 811)
, set of bes fixlets whose (
(source severity of it as lowercase = "critical")
or ( source severity of it as lowercase = "important" )
)
and
( source release date of it < (current date - 1 * week))
)
)
Typo alert… [Forum Padding]
Thanks Boyd. no time to test it right now… there are probably at least 2 more issues with Item callouts.
The idea is to pull “all the interesting stuff” at the bottom in a 1:1 relationship, before joining the Fixlets with the Computers for the Results.
1 Like