I have a session relevance query I use to return all the relevant fixlets I care about for certain operating systems. With 10s of thousands of computers and a huge response, it can take over 10minutes to get the results returned.
Is there a more efficient query to perform to get the same results?
(name of it & "|~|" & concatenation "|*|" of names of relevant fixlets whose ((source of it as lowercase = "microsoft" and category of it as lowercase contains "security") OR ((source of it as lowercase contains "oracle" or source of it as lowercase contains "red hat") AND (name of it as lowercase starts with "elsa" or name of it as lowercase starts with "rhsa")))of it) of bes computers whose (exists name of it AND ((it contains "win201" or it contains "win202" or it contains "enterprise linux 8" or it contains "enterprise server 7" or it contains "enterprise server 8" or it contains "red hat enterprise 8") of (operating system of it as lowercase)))
Hey Chris,
Couldn’t this be done quicker using Web Reports?
Based on the relevance you’ve posted I’m pretty sure I’ve created a Web Report that does almost the same thing for the same purpose.
I’m not at the laptop just now but I’ll email you a copy of what I’ve set first thing tomorrow morning to see if it helps.
Though, tbh, knowing your knowledge and skill level, I’m almost certain that you’ve tried this and it’s not giving what you’re after 
I need to get the results programmatically so I run the query against the Root server :52311/api/query.
I could have a saved Web Report that writes the report contents to csv on the webreport server but then I have to move the output file to a HTTP accessible path so it can be consumed and seems kind of clunky.
Ahhh I see, knew it wouldn’t be just a simple Web report 
Looking at what you have, I’d be surprised if there’s a more efficient way to write that but I’ll be watching with interest.
We can probably do…something…since the filtering on fixlets is repeated for every computer on which it’s relevant. Let me see what I can do.
Check whether this performs any better…
(name of item 0 of it & "|~|" & concatenation "|*|" of names of items 1 of (item 0 of it, elements of item 1 of it) whose (relevant flag of result (item 0 of it, item 1 of it))) of (elements of item 0 of it, item 1 of it) of (set of bes computers whose (exists name of it AND ((it contains "win201" or it contains "win202" or it contains "enterprise linux 8" or it contains "enterprise server 7" or it contains "enterprise server 8" or it contains "red hat enterprise 8") of (operating system of it as lowercase))), set of bes fixlets whose (exists applicable computers of it and ((source of it as lowercase = "microsoft" and category of it as lowercase contains "security") OR ((source of it as lowercase contains "oracle" or source of it as lowercase contains "red hat") AND (name of it as lowercase starts with "elsa" or name of it as lowercase starts with "rhsa")))))
The way this works is by first building a set of every computer we’re interested in, and a set of every fixlet we’re interested in. This way the filtering on fixlets is only performed once; any fixlets with no relevant computers or that don’t match the source/category filters are discarded up-front. Then it expands set of computers, and for every computer it expands the set of fixlets, and retrieves the properties from the fixlets where the fixlet is relevant on the computer.
For readability, this expands as
(
name of item 0 of it
& "|~|"
& concatenation "|*|" of names of items 1
of (item 0 of it, elements of item 1 of it) whose (relevant flag of result (item 0 of it, item 1 of it))
)
of (elements of item 0 of it, item 1 of it)
of (
set of bes computers whose (exists name of it AND ((it contains "win201" or it contains "win202" or it contains "enterprise linux 8" or it contains "enterprise server 7" or it contains "enterprise server 8" or it contains "red hat enterprise 8") of (operating system of it as lowercase)))
, set of bes fixlets whose (exists applicable computers of it and ((source of it as lowercase = "microsoft" and category of it as lowercase contains "security") OR ((source of it as lowercase contains "oracle" or source of it as lowercase contains "red hat") AND (name of it as lowercase starts with "elsa" or name of it as lowercase starts with "rhsa"))))
)
Timing it in my lab, I have a bit over 4500 test computer instances (most of them simulated and with few relevant fixlets), and this change brings the query time down from 6s189 ms to 0s138ms
2 Likes
This new query resulted in a run time of 2 minutes! The old one took 84 minutes. The data comparison looks accurate. Thank you @JasonWalker
2 Likes
84 minutes!?!
Happy I was able to help