Custom Web Report - better filtering

[LouC]

I have the below relevance and i want to break out the external sites so i can apply different filters to them. Here is what i’m starting with

(html "<table id=resultsTable class=sortable> <colgroup><col style=width:140px;/><col style=width:480px;/><col/><col/><col style=width:120px;/><col/></colgroup>" & html ("<th>Computer Name</th><th>Applicable Patch</th><th>Severity</th><th>Category</th><th>Release Date</th><th>CVE</th>") & (it) & html "</table>" ) of concatenations of trs of (td of (item 0 of it as string) & td of (item 1 of it as string) & td of (item 2 of it as string) & td of (item 3 of it as string) & td of ((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) & td of (item 5 of it as string)) of ((if (size of item 1 of it > 0) then (concatenations "; " 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 (item 0 of it , intersection of (applicable computer set of item 0 of it; item 1 of it)) whose (size of item 1 of it > 0) of (elements of item 0 of it , item 1 of it) of (set of fixlets whose ((category of it = "Security Update" OR category of it = "Security Hotfix" OR category of it = "Update") AND Source Severity of it as lowercase does not contain "unspecified" AND Name of it as string as lowercase does not contain "jre <" AND applicable computer count of it > 0 AND name of it as lowercase does not contain "superseded" AND name of it as lowercase does not contain "sharepoint" AND name of it as lowercase does not contain "sql" and globally visible flag of it = true) of bes sites whose(name of it is contained by "Enterprise Security|Updates for Windows Applications|Updates for Windows Applications Extended"), union of member set of bes computer groups whose (name of it as lowercase = "endpoints")

And here is an attempt to break it out:

(html "<table id=resultsTable class=sortable> <colgroup><col style=width:140px;/><col style=width:480px;/><col/><col/><col style=width:120px;/><col/></colgroup>" & html ("<th>Computer Name</th><th>Applicable Patch</th><th>Severity</th><th>Category</th><th>Release Date</th><th>CVE</th>") & (it) & html "</table>" ) of concatenations of trs of (td of (item 0 of it as string) & td of (item 1 of it as string) & td of (item 2 of it as string) & td of (item 3 of it as string) & td of ((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) & td of (item 5 of it as string)) of ((if (size of item 1 of it > 0) then (concatenations "; " 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 (item 0 of it , intersection of (applicable computer set of item 0 of it; item 1 of it)) whose (size of item 1 of it > 0) of (elements of item 0 of it , 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 does not contain "unspecified" AND applicable computer count of it > 0 AND name of it as lowercase does not contain "superseded" AND name of it as lowercase does not contain "sharepoint" AND name of it as lowercase does not contain "sql" and globally visible flag of it = true) of bes sites whose(name of it is contained by "Enterprise Security"))

OR whose (

(category of it = "Security Update" OR category of it = "Update") AND Name of it as string as lowercase does not contain "jre <" AND applicable computer count of it > 0 AND name of it as lowercase does not contain "superseded" and globally visible flag of it = true) of bes sites whose(name of it is contained by "Updates for Windows Applications|Updates for Windows Applications Extended"))

, union of member set of bes computer groups whose (name of it as lowercase = "endpoints")

I would appreciate any help. Thanks

[LouC]

I’m also thinking about what if you want to have AIX, Linux and Windows systems all on the same report and you want to get granular with what patches you want included. Is this possible?

[JasonWalker]

You’ve shown what you started with, and what you’re trying, but I don’t think I understand you’re goal…what are you trying to accomplish?

[LouC]

I want to be able to use a custom set of filters for different external sites. For example, Lets say I want one report that includes outstanding patches for both Windows and RHEL servers

• for windows i want to include patches from just the security updates category
• for RHEL i want to include patches from just the Security Advisory category

Since Windows also has a Security Advisory category i need to have a way to apply a different set of filters against each external site.

[JasonWalker]

Jason Cordell is presenting on “web Reports from scratch” at BigFix Days right now …

[JasonWalker]

Ok, I don’t have all the sites & computer groups to reproduce the whole query handy, but looking specifically at the fixlets filtering… if I understand correctly, you want to create a set of fixlets, where the fixlets come from two sites and we need to specify different filters based on the site.

One approach is to create two separate fixlet sets and then use the union of (set; set) construct to merge the two sets into one.

Selection for one set:

set of fixlets whose (
    (category of it = "Security Update" OR category of it = "Update") 
    AND Name of it as string as lowercase does not contain "jre <" 
    AND applicable computer count of it > 0 
    AND name of it as lowercase does not contain "superseded" 
    AND globally visible flag of it = true
    ) of bes sites whose(
    name of it is contained by "Updates for Windows Applications|Updates for Windows Applications Extended"
    )    

(in my lab, this creates a set of 37 fixlets)

The second set definition is

set of fixlets whose (
    (category of it = "Security Update" OR category of it = "Security Hotfix") 
    AND Source Severity of it as lowercase does not contain "unspecified" 
    AND applicable computer count of it > 0 
    AND name of it as lowercase does not contain "superseded" 
    AND name of it as lowercase does not contain "sharepoint" 
    AND name of it as lowercase does not contain "sql" 
    AND globally visible flag of it = true
   ) of bes sites whose(
   name of it is contained by "Enterprise Security"
   )

which, in my lab, is a set of 8 fixlets.

We can use the ‘union’ operator to combine the two sets into one. The basic usage is union of (<set1> ; <set2>) – note the semicolon between the two sets, rather than a comma.

union of (
    set of fixlets whose (
        (category of it = "Security Update" OR category of it = "Update") 
        AND Name of it as string as lowercase does not contain "jre <" 
        AND applicable computer count of it > 0 
        AND name of it as lowercase does not contain "superseded" 
        AND globally visible flag of it = true
        ) of bes sites whose(
        name of it is contained by "Updates for Windows Applications|Updates for Windows Applications Extended"
        )    

   ; set of fixlets whose (
        (category of it = "Security Update" OR category of it = "Security Hotfix") 
        AND Source Severity of it as lowercase does not contain "unspecified" 
        AND applicable computer count of it > 0 
        AND name of it as lowercase does not contain "superseded" 
        AND name of it as lowercase does not contain "sharepoint" 
        AND name of it as lowercase does not contain "sql" 
        AND globally visible flag of it = true
       ) of bes sites whose(
       name of it is contained by "Enterprise Security"
       )
   )

This ‘union’ generates a single set, containing the fixlets that were in either of our original sets.

[JasonWalker]

Given the work you have thus far, I’d prefer the first approach.
The other approach is to grab ‘all’ the ‘bes fixlets’ first and then do the filtering, including ‘name of site of it’ as part of the filtering and then AND and OR the terms as needed. You can also move around the filters based upon whether the filter is common to both sites, or only applies to one of the two sites, like

set of bes fixlets whose (
   globally visible flag of it = true
   AND applicable computer count of it > 0 
   AND (
        (
         name of site of it is contained by "Updates for Windows Applications|Updates for Windows Applications Extended"
         AND (category of it = "Security Update" OR category of it = "Update") 
         AND Name of it as string as lowercase does not contain "jre <" 
         AND name of it as lowercase does not contain "superseded" 
        ) 
       OR 
       ( 
         name of site of it is contained by "Enterprise Security"
         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" 
         AND name of it as lowercase does not contain "sharepoint" 
         AND name of it as lowercase does not contain "sql" 
      )
  )
)

[LouC]

I’m getting “the Tuple index 0 is out of range”

This is the entire relevance with your first suggestion:

(html "<table id=resultsTable class=sortable> <colgroup><col style=width:140px;/><col style=width:480px;/><col/><col/><col style=width:120px;/><col/></colgroup>" & html ("<th>Computer Name</th><th>Applicable Patch</th><th>Severity</th><th>Category</th><th>Release Date</th><th>CVE</th>") & (it) & html "</table>" ) of concatenations of trs of (td of (item 0 of it as string) & td of (item 1 of it as string) & td of (item 2 of it as string) & td of (item 3 of it as string) & td of ((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) & td of (item 5 of it as string)) of ((if (size of item 1 of it > 0) then (concatenations "; " 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 (item 0 of it , intersection of (applicable computer set of item 0 of it; item 1 of it)) whose (size of item 1 of it > 0) of (elements of item 0 of it , item 1 of it) of union of (
    set of fixlets whose (
        (category of it = "Security Update" OR category of it = "Update") 
        AND Name of it as string as lowercase does not contain "jre <" 
        AND applicable computer count of it > 0 
        AND name of it as lowercase does not contain "superseded" 
        AND globally visible flag of it = true
        ) of bes sites whose(
        name of it is contained by "Updates for Windows Applications|Updates for Windows Applications Extended"
        )    

   ; set of fixlets whose (
        (category of it = "Security Update" OR category of it = "Security Hotfix") 
        AND Source Severity of it as lowercase does not contain "unspecified" 
        AND applicable computer count of it > 0 
        AND name of it as lowercase does not contain "superseded" 
        AND name of it as lowercase does not contain "sharepoint" 
        AND name of it as lowercase does not contain "sql" 
        AND globally visible flag of it = true
       ) of bes sites whose(
       name of it is contained by "Enterprise Security"
       )
   )

, union of member set of bes computer groups whose (name of it as lowercase = "endpoints")

[JasonWalker]

I think you need one more set of parentheses, to create a tuple based on (union of (<bes fixlet sets>), union of (bes computer groups) )

I don’t have the groups, but this modification to the syntax seems to work for me, give it a try

(html "<table id=resultsTable class=sortable> <colgroup><col style=width:140px;/><col style=width:480px;/><col/><col/><col style=width:120px;/><col/></colgroup>" & html ("<th>Computer Name</th><th>Applicable Patch</th><th>Severity</th><th>Category</th><th>Release Date</th><th>CVE</th>") & (it) & html "</table>" ) of concatenations of trs of (td of (item 0 of it as string) & td of (item 1 of it as string) & td of (item 2 of it as string) & td of (item 3 of it as string) & td of ((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) & td of (item 5 of it as string)) of ((if (size of item 1 of it > 0) then (concatenations "; " 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 (item 0 of it , intersection of (applicable computer set of item 0 of it; item 1 of it)) whose (size of item 1 of it > 0) of (elements of item 0 of it , item 1 of it) of 

 (
  union of (
    set of fixlets whose (
        (category of it = "Security Update" OR category of it = "Update") 
        AND Name of it as string as lowercase does not contain "jre <" 
        AND applicable computer count of it > 0 
        AND name of it as lowercase does not contain "superseded" 
        AND globally visible flag of it = true
        ) of bes sites whose(
        name of it is contained by "Updates for Windows Applications|Updates for Windows Applications Extended"
        )    

   ; set of fixlets whose (
        (category of it = "Security Update" OR category of it = "Security Hotfix") 
        AND Source Severity of it as lowercase does not contain "unspecified" 
        AND applicable computer count of it > 0 
        AND name of it as lowercase does not contain "superseded" 
        AND name of it as lowercase does not contain "sharepoint" 
        AND name of it as lowercase does not contain "sql" 
        AND globally visible flag of it = true
       ) of bes sites whose(
       name of it is contained by "Enterprise Security"
       )
   )

, union of member sets of bes computer groups whose (name of it as lowercase = "endpoints")
)

edit: I also use the plural ‘union of member sets of bes computer groups’ to avoid throwing an error when I have no group “endpoints”

[LouC]

yeah, that was it… Thanks Jason… and thanks for the tip about using the plural ‘union of member sets of bes computer groups’