I have a report that I run to identify BigFix clients that are outdated based on them being relevant for a fixlet to upgrade them. If there is no available client upgrade fixlet, there is no point in my including it in my report.
Lately when I run this report, it can take several minutes or more (if it finishes at all). The query runs fast until the bes property Architecture is added.
the goal is to have the query generate output in this format:
hostname~os~agent_version~architecture
unique values of (name of it|"" & "~" & (((if it contains "." and it contains "Linux" then preceding text of first "." of it else it))) of (if operating system of it starts with "Win" then preceding text of first " " of operating system of it else (if operating system of it contains "(" then preceding text of first "(" of operating system of it as string else operating system of it as string)) &"~" & agent version of it & "~" & (value of results (bes properties whose (name of it = "Architecture"),it)|"")) of applicable computers whose (last report time of it > (now-3*day)) of bes fixlets whose (number of applicable computers of it > 0 and name of it as string as lowercase contains "available" and (name of it as string as lowercase contains "client - bigfix" or name of it as string as lowercase contains "client - ibm") and (preceding text of first " " of following text of first "version " of name of it as string as version <= "10.0.6"))
Our WebReports has 2 datasources so when adding ‘bes properties’ it must be plural because it has to look for the properties in all datasources. In the past, that has added some complexity to writing session relevance of ‘bes properties’.
Is there a more efficient way to get these results?
Try the following - in my experience the best structure to session relevance. I have scaled it up to 100 properties over 22k devices and still works extremely fast (bolded the code for the custom property, so if you need to add more, just add one more set in each location)! Hopefully it works for you too.
unique values of (name of item 0 of it|"" & "~" & (((if it contains "." and it contains "Linux" then preceding text of first "." of it else it))) of (if operating system of item 0 of it starts with "Win" then preceding text of first " " of operating system of item 0 of it else (if operating system of item 0 of it contains "(" then preceding text of first "(" of operating system of item 0 of it as string else operating system of item 0 of it as string)) & "~" & agent version of item 0 of it & "~" & (if (size of item 1 of it >= 1) then ((if it = "" then "" else it) of concatenation ";" of values of results (item 0 of it, elements of item 1 of it)) else (""))) of (elements of item 0 of it, item 1 of it) of (set of applicable computers whose (last report time of it > (now-3*day)) of bes fixlets whose (number of applicable computers of it > 0 and name of it as string as lowercase contains "available" and (name of it as string as lowercase contains "client - bigfix" or name of it as string as lowercase contains "client - ibm") and (preceding text of first " " of following text of first "version " of name of it as string as version <= "10.0.6")), set of bes properties whose (name of it as lowercase = ("Architecture") as lowercase))
unique values of (name of item 0 of it|"" & “~” & (((if it contains “.” and it contains “Linux” then preceding text of first “.” of it else it))) of (if operating system of item 0 of it starts with “Win” then preceding text of first " " of operating system of item 0 of it else (if operating system of item 0 of it contains “(” then preceding text of first “(” of operating system of item 0 of it as string else operating system of item 0 of it as string)) & “~” & agent version of item 0 of it & “~” & (if (size of item 1 of it >= 1) then ((if it = “” then “” else it) of concatenation “;” of values of results (item 0 of it, elements of item 1 of it)) else (""))) of (elements of item 0 of it, item 1 of it) of (set of applicable computers whose (last report time of it > (now-3*day)) of bes fixlets whose (number of applicable computers of it > 0 and name of it as string as lowercase contains “available” and (name of it as string as lowercase contains “client - bigfix” or name of it as string as lowercase contains “client - ibm”) and (preceding text of first " " of following text of first "version " of name of it as string as version <= “10.0.6”)), set of bes properties whose (name of it as lowercase = (“Architecture”) as lowercase))
2 Likes
@ageorgiev You should put the relevance statement in as a preformatted text block using the </> button to make your code easy to copy. As it stands, all of your quote symbols will be copied as “smartquotes”.
edit Of course, your bolding will be lost…
1 Like
Don’t forget to remove the ** left over from the bolding.
Yea, just figured that bit out! Put it in both just in case…
1 Like
From many minutes to 11 seconds. thank you @ageorgiev !
1 Like