I have a report based on the Microsoft Patch Compliance Report, but it’s taking too long to load when there’s a large number of computers in the network it’s checking. Does anyone have any tips to make this report load faster? I have 117 computers on the network I’m testing my queries on and it’s taking 40 - 50 seconds to load this report. I need this query to load much faster (like 1 - 5 seconds).
The report is supposed to produce a list of computers that includes the total number of patches available for each computer, the total number of installed patches (non-relevant patches) for each computer, the total number of outstanding patches available (relevant patches) for each computer, and the compliance percentage of the computer (installed patches / total patches) * 100.
I have created 2 versions of this report. One, that produces individual counts for each patch on each computer, then the totals of which are summed up after the query is produced in javascript. The other version sums up all of the totals inside the query using multiplicities. Both versions are taking too long for a large set of computers.
Here’s the first version that doesn’t sum up the total patch counts in the query (saving that functionality for the javascrpt).
((id of computer of it as string & id of fixlet of it as string), (id of computer of it as string), name of computer of it | "<none>", operating system of computer of it | "<none>", (if exists ip addresses of computer of it then (concatenations (html "<br>") of (ip addresses of computer of it as string)) as string else html "" as string ), (if exists last report time of computer of it then (last report time of computer of it) as string else ("<none>")), (if (exists first became relevant of it) then ("1") else ("0")), (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0"), (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0") ) of results of (bes fixlets whose ((name of site of it) = "Enterprise Security" and source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ))
Here’s the 2nd version that does sum the total patch counts in the query as well as calculates the compliance percentage.
( id of item 0 of it, name of item 0 of it | "<none>", operating system of item 0 of it | "<none>", ( if (exists ip addresses of item 0 of it) then ((concatenations (html "<br>") of (ip addresses of item 0 of it as string)) as string) else (html "" as string) ), item 1 of it, item 2 of it, item 3 of it, (((item 2 of it) as floating point / (item 1 of it) as floating point) * 100) as integer as string & "%25") of ( it, number of ( ( results from ( fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ) of bes sites whose (name of it = "Enterprise Security") ) of it ) whose (exists first became relevants of it) ), multiplicities of unique values of ((((relevant flags of it) whose (it = FALSE)) as string) of (results from ( fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ) of bes sites whose (name of it = "Enterprise Security") ) of it ) whose (exists first became relevants of it) ), multiplicities of unique values of ((((relevant flags of it) whose (it = TRUE)) as string) of (results from ( fixlets whose ( source severity of it does not contain "Unspecified" and source severity of it does not contain "N/A" and source severity of it is not "" and (number of actions of it) as integer > 0 and globally visible flag of it = TRUE and (source release date of it) > date "01 Jan 2000" ) of bes sites whose (name of it = "Enterprise Security") ) of it ) whose (exists first became relevants of it) ) ) of bes computers
Any tips you can give me to make either of these run much faster than they currently do would be very appreciated.