Fan of jgstew's relevance efficiency but unable to help myself :(

[Mario]

Hi all,

So I am a fan of efficiency and so jgstews articles such as below are of great interest to me

The way Session Relevance statements are written matters (Part 2) Computer Names

I wrote a previous post about optimizing session relevance statements and why it matters. This is another example of the same. The following info is based upon this: Relevance to retrieve a set of computer objects with duplicate names @glbproject had a need to get the set of computer objects that have duplicate names, but in an environment with 250k clients this can be a challenge. There are many different session relevance statements below that all find the set of computer objects with du…

The Way Relevance Statements Are Written Matters (part3) Whose

The order of operations within a complex WHOSE clause matters in BigFix Relevance but especially in Session Relevance. A WHOSE clause takes plural results (similar to arrays) and filters out the unwanted items. Here are 2 examples: Q: number of it whose(it contains "b") of ("b";"abc";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab";"aaaaaaaaaaaaaaaaac") A: 3 T: 0.054 ms I: singular integer Q: number of it whose(it starts with "a") of ("b";"abc";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab";"aaaaaaaaa…

I have created a web report that with some post processing gets turned into a pipe separated report.
I have hated it from day dot but happy obviously that it works. I have been meaning to find out how to optimize it for a while but every time I try I am hopelessly stuck.

I am also now experiencing a problem not only with speed but with my post processing vb script running out of memory due to large data (800MB+) from web reports.
So In theory It would be ideal to just do this right now and skip post processing altogether.

I am in particular very fond of this example:
Option #6 takes ~0.022 seconds in my environment:

(name of it, id of it) of items 1 of (it, bes computers) whose(name of item 1 of it is contained by item 0 of it) of ( set of unique values whose (multiplicity of it > 1) of names of bes computers )

I would like to somehow replicate that to the below code I am using now but all attempts (many days worth…) are not working for me. Is it possible? Would be thankful if someone can guide me through or assist. I seem to not understand how the multiples of items stringed together work and how to transfer to my example.

My code, that works but is… not efficient in any way

<?relevance ( names of applicable computers whose (operating system of it contains "Win7" OR operating system of it contains "Win8" OR operating system of it contains "Win10") of it, (if (exists cve id list of it) then (if (cve id list of it != "") then (concatenation ";" of substrings separated by "," of (cve id list of it)) else ("N/A")) else ("N/A")) & "|" & (if (exists source id of it) then (if (source id of it != "") then (concatenation ";" of substrings separated by "," of (source id of it)) else ("N/A")) else ("N/A")) & "|" & (if (exists source release date of it) then (if (source release date of it as string != "") then ((month of it as two digits & "/" & day_of_month of it as two digits & "/" & year of it as string) of date(local time zone) of ((source release date of it as string & " 00:00:00 +1000") as time)) else ("N/A")) else ("N/A")) & "|" & (if (exists source severity of it) then (if (source severity of it as string != "") then (source severity of it) else ("N/A")) else ("N/A")) & "|" & (if (exists category of it) then (if (category of it as string != "") then (category of it) else ("N/A")) else ("N/A")) & "|" & (concatenation "" of substrings separated by "," of (names of it as string)) ) of fixlets whose ( fixlet flag of it = true and globally visible flag of it = true and exist applicable computers of it and source id of it starts with "KB" and name of it as string contains "MS" and name of it as lowercase does not contain "corrupt patch" and name of it as lowercase does not contain "superseded" ) of bes sites whose (name of it = "Enterprise Security")?>

Again any help would be super appreciated.

Thanks!
Mario

[Mario]

I have managed to avoid the first comma after the hostname, it is now completely pipe separated.
There is some more work to go but if anyone sees anything that can be improved, appreciate it!
Need to see if i can add the column headers in there and to output via csv as well, and some checks in some of the code to change entries to “N/A”, like the source severity if it is .

Will keep updating as the journey continues…

Have not actually exported via CSV yet but here is actual output with hostnames changed. Exciting a bit…
Header: “Computer Name|CVE|Source ID|Source Release Date (mm/dd/yyyy)|Source Severity|Category|Fixlet Name”

akbar-endpoint|CVE-2014-7206|USN-2370-1|10/08/2014||Security|USN-2370-1 - apt vulnerability - Ubuntu 12.04 - (amd64)
akbar-endpoint|CVE-2014-6277; CVE-2014-6278|USN-2380-1|10/09/2014||Security|USN-2380-1 - bash vulnerabilities - Ubuntu 12.04 - (amd64)
akbar-endpoint|CVE-2014-3634; CVE-2014-3683|USN-2381-1|10/09/2014||Security|USN-2381-1 - rsyslog vulnerabilities - Ubuntu 12.04 - (amd64)

And the actual code I am on now, so saving any post processing required to change the first comma after hostname to a pipe

(name of item 0 of it & “|” & item 1 of it & “|” & item 2 of it & “|” & item 3 of it & “|” & item 4 of it & “|” & item 5 of it & “|” & item 6 of it) of (
applicable computers of it,
(if (cve id list of it != “”) then (concatenation “;” of substrings separated by “,” of (cve id list of it)) else (“N/A”) | “N/A”),
(if (source id of it != “”) then (concatenation “;” of substrings separated by “,” of (source id of it)) else (“N/A”) | “N/A”),
(if (source release date of it as string != “”) then ((month of it as two digits & “/” & day_of_month of it as two digits & “/” & year of it as string) of date(local time zone) of ((source release date of it as string & " 00:00:00 +1000") as time)) else (“N/A”) | “N/A”),
(if (source severity of it as string != “”) then (source severity of it) else (“N/A”) | “N/A”),
(if (category of it as string != “”) then (category of it) else (“N/A”) | “N/A”),
(concatenation “” of substrings separated by “,” of (name of it as string))
) of fixlets whose (
fixlet flag of it = true and
globally visible flag of it = true and
exist applicable computers of it and
name of it as lowercase does not contain “corrupt patch” and
name of it as lowercase does not contain “superseded”
) of bes sites whose (
name of it = “Patches for Ubuntu 1204” or name of it = “Patches for Ubuntu 1404”
)

Cheers!
Mario

[strawgate]

So to start it looks like you can simplify your relevance quite a bit:

(names of applicable computers of it, cve id list of it | "N/A", source id of it | "N/A", ((month of it as two digits & "/" & day_of_month of it as two digits & "/" & year of it as string) of source release date of it) | "N/A", name of it) of fixlets whose (fixlet flag of it = true and globally visible flag of it = true and name of it as lowercase does not contain "corrupt patch" and name of it as lowercase does not contain "superseded") of all bes sites whose (name of it = "Patches for Ubuntu 1204" or name of it = "Patches for Ubuntu 1404")

Should output the same thing as what you’re currently doing.

We can add:

("Computer Name", "CVE List", "Source ID", "Source Release Date", "Fixlet Name");

To the front to get headers for results.

("Computer Name", "CVE List", "Source ID", "Source Release Date", "Fixlet Name");(names of applicable computers of it, cve id list of it | "N/A", source id of it | "N/A", ((month of it as two digits & "/" & day_of_month of it as two digits & "/" & year of it as string) of source release date of it) | "N/A", name of it) of fixlets whose (fixlet flag of it = true and globally visible flag of it = true and name of it as lowercase does not contain "corrupt patch" and name of it as lowercase does not contain "superseded") of all bes sites whose (name of it = "Patches for Ubuntu 1204" or name of it = "Enterprise Security")

Essentially instead of always doing if (property exists) then (use Property) else ("N/A") we can use this (property exists) | "N/A" if the left side returns an error, we return the right side instead. It looks like in some spots you are doing both an else and an error pipe which shouldnt be necessary.

This makes your relevance much simpler but doesn’t fix your main issue – you’re ingesting too much data at once for your vbscript.

So you have two ways to fix this:

1. Do Post-Processing with a language that can handle that much data (i.e. Powershell, Python, or a compiled code language)
2. Reduce the amount of data you’re processing

If you are good with Powershell I’d probably just go that route. If you need to stick with a vbscript I’d recommend splitting it up into multiple jobs – running each through the script separately

By this I mean you can find all the fixlets whose ID numbers are even id of it mod 2 = 0 or you could do a years worth of fixlets for each run of the script.

[jgstew]

What do you want the final output to look like as an example?

@Mario

akbar-endpoint|CVE-2014-6277; CVE-2014-6278|USN-2380-1|10/09/2014||Security|USN-2380-1 - bash vulnerabilities - Ubuntu 12.04 - (amd64)

Is this ^ the final output, or do you do something with this after the fact to turn it into something else?

What is the post processing for? What would need to be done in web reports to avoid the need for post processing?

Also, if you use javascript, you can do some post processing directly in web reports.

[Mario]

Hi William,

Thanks for the tips, I did incorporate this after I realized I did not use it.

Essentially instead of always doing if (property exists) then (use Property) else (“N/A”) we can use this (property exists) | “N/A”
However the demands of the report(s) were to put in something if there is no result, so I needed an If statement to drop the “N/A” at least if blank which complicates things. I have been thinking about going back and asking if this is avoidable recently but have not done it yet.

I see I was doing the source release date in a more complicated fashion then needed. Will do some changes to that soon. But another requirement of the report was (or may have been at the time) to not have commas in the report as the custom report output the results with brackets around that property eg hostname|(mon 1, july 2017 etc)|something else
So the parts where i concatenate “:” where there is “,” can possibly be avoided if I can stop the brackets from appearing in the output if a result contains a comma.
This may be why I went with a pipe separated output in the end so I can deal with this “problem” easier. Now wondering if there is any other, better way of dealing with it?!

hah! the header… I was not aware I could drop it in that easily. Ill have to do that shortly.

Ideally I would like to avoid post processing altogether and just use a custom executable to copy the finished output file to the required network location for another program to consume the results into its own database. I think this is becoming more achievable recently as I am starting to make sense of session relevance more then last year when I first did these reports.

There is a lot of data as you mention, the environment has a touch over 60K endpoints where the majority are Windows.
I run a separate report for Windows Servers and another for Workstations for applied patches from MBSA type scan. This report is 800Mb~ for workstations and I cut a fair chunk of data off the MBSA type output already. I think Web Reports can handle it no problem but as mentioned external post processing makes it a mess… and I would now like to skip that step. Powershell upgrade is easy enough, not sure why I went with VBScript in the first place… probably because someone put cscript.exe in Web Reports already and i was lazy on the day…

Good idea on splitting it up, it was in my list of things to do but didnt have a good plan on how to split it yet, your idea sounds great. Tho think I can get away without splitting now if avoid post processing

Cheers!
Mario

[Mario]

Hi James,
Yep the report should be as you mentioned, I have a few ‘restrictions’ around the report I have to stick with for now at least, here is whole list.

• Web reports was outputting results with “,” in the result surrounded with brackets, and originally report was CSV so looked like hostname,(mon 1, july 2017 etc),something else which meant it wouldnt import into unknown DB/application/tool. So I think I removed all commas from report and also turned it into pipe separated.
• Blank result entries were converted into “N/A” but I will check if this is needed anymore as it will save a fair few if statements

Post processing added the following er ‘features’

• Header row, which should be easy fix now
• Replaced any "<br />" with a new line (Probably should be able to find a better method of doing this without post processing)
• Originally replaced the first comma after hostname as per original code I posted but with item X of it looks like i have managed to avoid the need for this to be post processed
• I see I put in another one if there is more then 1 comma, ie more then one after hostname, it meant there was brackets to deal with around a result, it removed the brackets around the comma and the comma itself. I dont think this is actually necessary as I am running concatenations to deal with commas so they never output.
• In each line replace “|not found|” with “||”
• In each line replace "<" (“<”) with vbNullString (Probably to cater for any error or other types of non actual results from BigFix)
• In each line replace ">" (“>”) with vbNullString) (Probably to cater for any error or other types of non actual results from BigFix)
• If 10 chars from right = “|not found” then replace with “||||”. This is from the post processing script used on installed patches reports also which is same style/format but uses MBSA type analyses for installed patches. I think some endpoints did not return any results from the MBSA analyses so this catered for them so there is always same amount of pipes per line.

The rest was used to copy the post processed file to the required network share.

So it is quite heavy post processing. I think some of it can definitely be cut out or re-visited but not sure how that would go yet as they have a fair whack of data imported for over a year already.

I am very open on incorporating JS, I am completely clueless on using it with Web Reports tho

Any tips/suggestions or otherwise if you think are possible?

Cheers,
Mario