I’m trying to create a custom web report to show which patches are outstanding on each system. I used the BigFix excel connector to generate this relevance:
item 0 of it as string & "$x$" & item 1 of it as string & "$x$" & item 2 of it as string & "$x$" & item 3 of it as string & "$x$" & (if (year of it as integer = 1980) then ("<none>") else (year of it as string & "/" & (month of it as two digits) as string & "/" & (day_of_month of it as two digits) as string)) of (item 4 of it as date) & "$x$" & item 5 of it as string) of ((if (exists Names of Applicable Computers of it | false) then (concatenations "%0A" of (Names of Applicable Computers of it as string)) else ("<none>")) , (if (exists Name of it | false) then (concatenations "%0A" of (Name of it as string)) else ("<none>")) , (if (exists Source Severity of it | false) then (concatenations "%0A" of (Source Severity of it as string)) else ("<none>")) , (if (exists Category of it | false) then (concatenations "%0A" of (Category of it as string)) else ("<none>")) , (if (exists Source Release Date of it) then (Source Release Date of it as string) else ("Fri, 15 Feb 1980")) , (if (exists CVE ID List of it | false) then (concatenations "%0A" of (CVE ID List of it as string)) else ("<none>"))) of bes fixlets whose ((name of site of it = "Enterprise Security") AND ((Category of it as string as lowercase contains "security") AND (Source Severity of it as string as lowercase does not contain "unspecified")))
I want to filter this further based on group (using relevance). The excel connector doesn’t seem to have this option. I’ve tried adding of members of bes computer groups whose (name of it as lowercase contains "win servers") but i keep getting syntax errors. Any help would be appreciated. Thank you!
I’ve finally got this working but its really slow. If i were to remove my filters for ‘bes computer group’ from the relevance below and instead use the built-in web report filters it loads about 3 times quicker. Any suggestions to improve performance?
<?relevance
( item 0 of it as string & "$x$" &
item 1 of it as string & "$x$" &
item 2 of it as string & "$x$" &
item 3 of it as string & "$x$" &
(if (year of it as integer = 1980)
then ("<none>")
else
(year of it as string & "/" &
(month of it as two digits) as string & "/" &
(day_of_month of it as two digits) as string) )
of (item 4 of it as date) & "$x$" &
item 5 of it as string )
of (
(if (exists Names of Applicable Computers of it | false)
then (concatenations "%0A" of (names of applicable computers whose (exists bes computer group whose (name of it as lowercase contains "win servers") of it) of it ))
else ("<none>")),
(if (exists Name of it | false)
then (concatenations "%0A" of (Name of it as string))
else ("<none>")),
(if (exists Source Severity of it | false)
then (concatenations "%0A" of (Source Severity of it as string))
else ("<none>")),
(if (exists Category of it | false)
then (concatenations "%0A" of (Category of it as string))
else ("<none>")),
(if (exists Source Release Date of it)
then (Source Release Date of it as string)
else ("Fri, 15 Feb 1980")),
(if (exists CVE ID List of it | false)
then (concatenations "%0A" of (CVE ID List of it as string))
else ("<none>")))
of it whose
(disjunction of relevants (it, elements of sets of members of bes computer groups whose (name of it as lowercase contains "win servers"))) of fixlets whose (applicable computer count of it > 0 AND
(category of it = "Security Update" OR category of it = "Security Hotfix") AND
Source Severity of it as lowercase does not contain "unspecified" AND
name of it as lowercase does not contain "superseded")
of bes sites whose(name of it is contained by "Enterprise Security|Updates for Windows Applications")
?>
I’ll try to look a bit later today, but wanted to point out that these places are likely to be problematic.
In the first quoted spot, for every fixlet, this recreates the set of members of the bes computer groups; if there are a thousand fixlets involved, this looks up the computer groups and retrieves the group members a thousand times. This is known as a “cross-product problem”, and is one of the more common issues when trying to improve relevance performance.
In the second quotes spot, for every applicable computer we look up that computer’s group memberships and see whether it’s a member of the group you want. For one fixlet with a hundred computers, we lookup group memberships a hundred times; for a hundred fixlets with one applicable computer, we lookup up that computer’s group memberships a hundred times.
The key is to retrieve the set of computers in which we’re interested, and pass that set up throughout the query instead of looking up computer groups and group memberships repeatedly at different steps.
I think this will do what you want, please check it out and let me know.
( item 0 of it as string & "$x$" &
item 1 of it as string & "$x$" &
item 2 of it as string & "$x$" &
item 3 of it as string & "$x$" &
(if (year of it as integer = 1980)
then ("<none>")
else
(year of it as string & "/" &
(month of it as two digits) as string & "/" &
(day_of_month of it as two digits) as string) )
of (item 4 of it as date) & "$x$" &
item 5 of it as string )
of (
(if (size of item 1 of it > 0) /* if applicable computers exist */
then (concatenations "%0A" of (names of elements of item 1 of it ))
else ("<none>")),
(if (exists Name of item 0 of it| false)
then (concatenations "%0A" of (Name of item 0 of it as string))
else ("<none>")),
(if (exists Source Severity of item 0 of it| false)
then (concatenations "%0A" of (Source Severity of item 0 of it as string))
else ("<none>")),
(if (exists Category of item 0 of it| false)
then (concatenations "%0A" of (Category of item 0 of it as string))
else ("<none>")),
(if (exists Source Release Date of item 0 of it)
then (Source Release Date of item 0 of it as string)
else ("Fri, 15 Feb 1980")),
(if (exists CVE ID List of item 0 of it | false)
then (concatenations "%0A" of (CVE ID List of item 0 of it as string))
else ("<none>")))
of
(
/* a fixlet */
item 0 of it
/* set of applicable computers for the fixlet, filtered to only the group members we want */
, intersection of (applicable computer set of item 0 of it; item 1 of it)
)
of
(
/* unwrap the set of fixlets */
elements of item 0 of it
/* the set of bes computers in which we are interested */
, item 1 of it
)
of
(
/* Set of fixlets in which we are interested in; filter here for best efficiency */
set of fixlets whose (
applicable computer count of it > 0
AND
(category of it = "Security Update" OR category of it = "Security Hotfix")
AND
Source Severity of it as lowercase does not contain "unspecified"
AND
name of it as lowercase does not contain "superseded")
of bes sites whose(name of it is contained by "Enterprise Security|Updates for Windows Applications")
/* Set of bes computers in which we are interested; filter here for best efficiency */
, member set of bes computer groups whose (name of it = "Production_Site_Computers")
)
Oh, ok, it wasn’t clear to me and I thought you might want the Fixlet listed even if no members of your group were relevant. Try filtering where the applicable group intersection size is greater than zero.
(
/* a fixlet */
item 0 of it
/* set of applicable computers for the fixlet, filtered to only the group members we want */
, intersection of (applicable computer set of item 0 of it; item 1 of it)
) whose (size of item 1 of it > 0)
