Compliance by Computer Report - Relevance

1

I’m struggling with relevance again. This is the relevance used in Lee Wei’s compliance by computer report. I want to add filters (using relevance) for category, severity, and by group. I think i have category/severity sorted out but i always struggle with group.

Original

trs of htmls (unique values of (it as string) of (td of (id of computer of it as string & id of fixlet of it as string) & td of (id of computer of it as string) & td of (if exists name of computer of it then name of computer of it else “”) & td of (if exists operating system of computer of it then operating system of computer of it else “”) & td of (if exists ip addresses of computer of it then concatenations (html “
”) of (ip addresses of computer of it as string) else html “”) & td of (if exists last report time of computer of it then (((year of it as string & “/” & month of it as two digits & “/” & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & “:” & two digit minute of it as string & “:” & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else (“”)) & td of (if (exists first became relevant of it) then (“1”) else (“0”)) & td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then “1” else “0”) & td of (if (exists Last Became Relevant of it and Relevant Flag of it) then “1” else “0”)) of results of bes fixlets)

Edited

trs of htmls (unique values of (it as string) of (
td of (id of computer of it as string & id of fixlet of it as string) &
td of (id of computer of it as string) & td of (if exists name of computer of it then name of computer of it else “”) &
td of (if exists operating system of computer of it then operating system of computer of it else “”) &
td of (if exists ip addresses of computer of it then concatenations (html “
”) of (ip addresses of computer of it as string) else html “”) &
td of (if exists last report time of computer of it then (((year of it as string & “/” & month of it as two digits & “/” & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & “:” & two digit minute of it as string & “:” & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else (“”)) &
td of (if (exists first became relevant of it) then (“1”) else (“0”)) &
td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then “1” else “0”) &
td of (if (exists Last Became Relevant of it and Relevant Flag of it) then “1” else “0”))

of results whose (
((Category of Fixlet of it as string as lowercase contains “security”) AND (Source Severity of Fixlet of it as string as lowercase contains “critical”) AND
(name of Fixlet of it as lowercase does not contain “superseded”))) of bes fixlets
whose (name of site of it = “Enterprise Security”)
)

2

Looking up the group memberships of each applicable computer would be inefficient in that template…instead I think you could start with the form at Custom Web Report - Relevance Help

3

Ok. I wasn’t sure if my other post was applicable here. It wasn’t obvious to me. Let me give it a try.

4

I’m using the form you have provided but i’m still doing something wrong. I’m getting error - The operator “applicable computer set” is not defined.

<?relevance 

  trs of htmls (unique values of (it as string) of (

  td of (id of computer of it as string & id of fixlet of it as string) & 
  td of (id of computer of it as string) & 
  td of (if exists name of computer of it then name of computer of it else "<none>") & 
  td of (if exists operating system of computer of it then operating system of computer of it else "<none>") & 
  td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "") & 
  td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":"   & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>")) & 
  td of (if (exists first became relevant of it) then ("1") else ("0")) & 
  td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0") & 
  td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0")) 

of
(
     /* a fixlet */
   item 1 of it 
     /* set of applicable computers for the fixlet, filtered to only the group members we want */
 , intersection of (applicable computer set of item 1 of it; item 0 of it) 
) 
 of
(
    /* unwrap the set of fixlets */
   elements of item 1 of it
    /* the set of bes computers in which we are interested */
 , item 0 of it
)  

of
(
  set of fixlets whose (
     
     	(category of it = "Security Update" OR category of it = "Security Hotfix") AND
         Source Severity of it as lowercase contains "critical" 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")

, member set of bes computer groups whose (name of it = "Endpoints"))

 
)

?>
5

You’re getting the ‘item 0’ and ‘item 1’ crossed up here. It confuses things when you use

(
    /* unwrap the set of fixlets */
   elements of item 1 of it
    /* the set of bes computers in which we are interested */
 , item 0 of it
)  of

because this crosses things up…originally, the Fixlet Set was item 0 and the Computer Set was item 1, but you swap them here so the new item 0 are the individual computers and the new item 1 is the Fixlet Set. You’re referencing them correctly later, but you cant pull ‘applicable computer set’ from a ‘bes fixlet set’, only from individual ‘bes fixlets’.

There’s also no place where you’re talking results (item 0 of it, item 1 of it) once item 0 is resolved to a Fixlet and item 1 is resolved to a Computer.

I rearranged it just a bit and put it in my Web Reports, try using this instead

<?relevance 

  trs of htmls (unique values of (it as string) of (

  td of (id of computer of it as string & id of fixlet of it as string) & 
  td of (id of computer of it as string) & 
  td of (if exists name of computer of it then name of computer of it else "<none>") & 
  td of (if exists operating system of computer of it then operating system of computer of it else "<none>") & 
  td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "") & 
  td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":"   & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>")) & 
  td of (if (exists first became relevant of it) then ("1") else ("0")) & 
  td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0") & 
  td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0")) 

of
 ( 
  results (
       item 0 of it
      ,  elements of item 1 of it
      )
)
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 whose (
     
     	(category of it = "Security Update" OR category of it = "Security Hotfix") AND
         Source Severity of it as lowercase contains "critical" 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")

, member set of bes computer groups whose (name of it starts with "Production"))

 
)

?>
1 Like
6

This correctly filters by group (thank you!). However, it is only returning results for systems that currently still have a patch outstanding. It’s filtering out all results that are 100% compliant.

7

Ah, I see. Intersecting between applicable computer set of <BES Fixlet> and the computer group member set will only include the currently-applicable computers for the Fixlet.

I need some time to think on it but may not be able to today. May be as simple as taking out that clause that does the intersection though.

8

ok good, that’s the section where i’m focusing my time… i’ve tried a few things so far but no luck. I believe i already tried removing it entirely… but i will try again.

9

Ok, if you really want results for all of the non-relevant computers…that report can get big. Very big.
In my deployment, I get that fixlet count as 2,581 fixlets of interest. My test computer group has only 20 machines in it. Cross-multiplying them, that’s 51,620 results / rows in the table.

When I tried to run it, it’s extremely slow. Basically don’t use the ‘unique values of (it as string)’ to sort the rows because that’s a huge memory footprint to sort. This seems to work though - I use the ‘concatenation “%0a” of trs’ so that newlines are embedded in the document, which isn’t necessary for the browser but does help if you want to look at the HTML source of the generated page.

<table border="all">
<?relevance 

concatenation "%0a" of trs of (

  td of (id of computer of it as string & id of fixlet of it as string) & 
  td of (id of computer of it as string) & 
  td of (if exists name of computer of it then name of computer of it else "<none>") & 
  td of (if exists operating system of computer of it then operating system of computer of it else "<none>") & 
  td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "") & 
  td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":"   & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>")) & 
  td of (if (exists first became relevant of it) then ("1") else ("0")) & 
  td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0") & 
  td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0")) 

of
 ( 
  results (
       item 0 of it
      ,  elements of item 1 of it
      )
)
of 
(
     /* a fixlet */
   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 whose (
     
     	(category of it = "Security Update" OR category of it = "Security Hotfix") AND
         Source Severity of it as lowercase contains "critical" 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")

, member set of bes computer groups whose (name of it starts with "Production")
)

 


?>
</table>
10

I think i caused confusion. Just like the original query we started with it should only return results that have ever had a fixlet that was relevant. (All results that return 0,0,0 should not be returned).

11

Ok, that helps a lot. So now we have three places to filter - filter the computers (with ‘member set of bes computer group’) ; filter the Fixlets (with the ‘category’ and ‘name’ clauses), and filter the Fixlet Results.

It’s possible to filter the ‘bes fixlet results’ to those with exists first became relevant of it, but because of the way Web Reports handles these three fields - ‘first became relevant’, ‘last became relevant’, and ‘last became nonrelevant’, those are very expensive fields to query, so we try to avoid using those as filters where we can. Instead we can look at ‘relevant flag of it or remediated flag of it’ to filter to fixlet results that are relevant now, or were relevant at some point in the past. That’s fairly fast on my test.

<table border="all">
<?relevance 

concatenation "%0a" of trs of (

  td of (id of computer of it as string & id of fixlet of it as string) & 
  td of (id of computer of it as string) & 
  td of (if exists name of computer of it then name of computer of it else "<none>") & 
  td of (if exists operating system of computer of it then operating system of computer of it else "<none>") & 
  td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "") & 
  td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":"   & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>")) & 
  td of (if (exists first became relevant of it) then ("1") else ("0")) & 
  td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0") & 
  td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0")) 

of
 ( 
  results (
       item 0 of it
      ,  elements of item 1 of it
      ) whose (remediated flag of it or relevant flag 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 whose (
     
     	(category of it = "Security Update" OR category of it = "Security Hotfix") AND
         Source Severity of it as lowercase contains "critical" 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")

, member set of bes computer groups whose (name of it starts with "Production")
)

 


?>
</table>

One more note, I’m not sure what you’re trying to obtain with the

  td of (if (exists first became relevant of it) then ("1") else ("0")) & 
  td of (if (exists Last Became NonRelevant of it and not relevant flag of it) then "1" else "0") & 
  td of (if (exists Last Became Relevant of it and Relevant Flag of it) then "1" else "0"))

As I said, these three fields in particular are expensive to query, so depending on what you’re seeking you could probably replace these with ‘relevant flag of it’ and ‘remediated flag of it’. I think the first one is “was it ever relevant” in which case you could use

if (relevant flag of it or remediated flag of it) then "1" else "0"

The send I think means it was relevant but it’s not now, so that maybe just

if (remediated flag of it) then "1" else "0"

The last one I think is for ‘still relevant now’ fixlets, so that would be

if (relevant flag of it) then "1" else "0"

Put all that together and we have

<table border="all">
<?relevance 

concatenation "%0a" of trs of (

  td of (id of computer of it as string & id of fixlet of it as string) & 
  td of (id of computer of it as string) & 
  td of (if exists name of computer of it then name of computer of it else "<none>") & 
  td of (if exists operating system of computer of it then operating system of computer of it else "<none>") & 
  td of (if exists ip addresses of computer of it then concatenations (html "<br>") of (ip addresses of computer of it as string) else html "") & 
  td of (if exists last report time of computer of it then (((year of it as string & "/" & month of it as two digits & "/" & day_of_month of it as two digits) of date (local time zone) of it & " " & (two digit hour of it as string & ":"   & two digit minute of it as string & ":" & two digit second of it as string) of time (local time zone) of it) of last report time of computer of it) else ("<none>")) & 
  td of (if (remediated flag of it or relevant flag of it) then ("1") else ("0")) & 
  td of (if (remediated flag of it) then "1" else "0") & 
  td of (if (relevant flag of it) then "1" else "0")) 

of
 ( 
  results (
       item 0 of it
      ,  elements of item 1 of it
      ) whose (remediated flag of it or relevant flag 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 whose (
     
     	(category of it = "Security Update" OR category of it = "Security Hotfix") AND
         Source Severity of it as lowercase contains "critical" 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")

, member set of bes computer groups whose (name of it starts with "Production")
)

 


?>
</table>
12

Jason, this is perfect. Swapping in ‘relevant flag of it’ and ‘remediated flag of it’ made a noticeable impact on performance. Thanks for the detailed explanation throughout as well! I really appreciate your help on this one.

1 Like