I am trying to pull back the values of the results of a bes property, along with the sum of the numbe rof relevant fixlets of clients with that property. I got the values of the property pulled back successfully, but the second column is counting the number of computers with that value, and I’m not sure how to show the sum of the count of applicable fixlets instead.
Platform Support
# of Applicable Fixlet
<?Relevance
trs of
(
td of it & td of (multiplicity of it as string)) of unique values of values whose (it as lowercase contains "windows-") of results of bes property "Platform Support"
?>
( name of computer of it , number of relevant fixlets of computer of it ) of results whose ( value of it as lowercase contains
"win" ) of bes properties whose ( name of it =
"OS" )
You are likely to want to include filters so only Fixlets are counted, not Tasks/Baselines/Analyses.
This is also an example to filter by sites, in this case Patches for Windows
( name of computer of it , number of relevant fixlets whose ( fixlet flag of it =
true and display name of site of it =
"Patches for Windows (English)" ) of computer of it ) of results whose ( value of it as lowercase contains
"win" ) of bes properties whose ( name of it =
"OS" )
So the final script might look like this.
<table class=
"sortable" id=
"table1" border=2> <th>Platform Support</th><th># of Applicable Fixlet</th> <?Relevance trs of (td of name of computer of it & td of (it as string) of number of relevant fixlets whose (fixlet flag of it =
true and display name of site of it =
"Patches for Windows (English)") of computer of it) of results whose (value of it as lowercase contains
"win") of bes properties whose (name of it =
"OS") ?> </table>
thanks Lee, but I’m trying to get a count of number of applicable fixlets (your correct, not tasks baselines, or analyses) for computers that have a common property.
{sigh} Close, but I’m already a pint into my Friday and can’t seem to remember how to get a total of one side of a tuple from unique values of the other. I think this would get close to what you want. I just don’t know how to get section B to work based on the result from A.
A: (item 0 of it, item 1 of it) of (number of relevant fixlets of it, value of result from (bes property “OS”) of it,name of it) of bes computers
B: (sum of items 0 of it, unique values of item 1 of it) of
I hate that I still get a mental image of what I’d do with Perl to get the result and have to stuggle to remember the relevance to juggle sums of lists of tuples. One other benefit from this is that once you get the final answer, you could even get the count of how many computers there were in each group based on the multiplicity (from the unique values).
If something clicks, I’ll update this, but perhaps someone else can jump in with the final step that I’m missing.
We cannot write this as one relevance statement. So some scripting language such as JavaScript is needed to iterate through a second pass.
Also we need to clarify the term “Applicable Fixlets”. Are these the ones still (#1) Vulnerable/Relevant on the computers or (#2) The Fixlets that have applicability but not vulnerable right now.
For example, I have Adobe Acrobat 9 on all my 100 computers. A patch comes out for Adobe Acrobat 9, so all my 100 computers have applicability (#2).
However, after one week, only 5 are still vulnerable (#1)
#1 is much easier, and the values are pre-calculated as the “Relevant Fixlet” property of a BES Computer, so to extract that is easy.
#2 is an involved topic, because BigFix can only tell if a computer is applicable, if it has at some point reported so. We determine this by looking at the “First Became Relevant” property of the BES Fixlet Result of the computer against the Fixlet.
Here is an example of assuming we want #1 - “Relevant Fixlet”.
This statement is very fast.
( values of results from (bes property whose (name of it =
"Subnet Address") ) of it, number of relevant fixlets whose ( fixlet flag of it and display name of site of it =
"Patches for Windows (English)" and name of it as lowercase does not contain
"superseded" and name of it as lowercase does not contain
"corrupt patch" and source severity of it as lowercase =
"critical" ) of it ) of bes computers whose (operating system of it as lowercase contains
"win")
Here is an example of assuming #2 - “First Became Relevant”
color=red
This statement is very slow[/color], make sure to try on a small set of computers first.
( values of results from (bes property whose (name of it =
"Subnet Address") ) of it, number of results from ( bes fixlets whose ( fixlet flag of it and display name of site of it =
"Patches for Windows (English)" and name of it as lowercase does not contain
"superseded" and name of it as lowercase does not contain
"corrupt patch" and source severity of it as lowercase =
"critical" )) whose (exists first became relevant of it) of it ) of bes computers whose (operating system of it as lowercase contains
"win")
Back to the very first issue that we have to deal with, which is to iterate through a property and get the subtotal, here is an example of doing the first pass using Relevance, then using Excel Pivot Table to do the subtotal for us.
It is easier shown then to describe, so here is a video clip example:
Well, I turn my back and all of a sudden there is Lee saying you can’t run this as a single relevance. I wasn’t sure that was completely accurate…so I wrote it. Please keep in mind that I’ve now had dinner, a few more pints, a chat with an old friend and a lovely glass of port. So I’d highly recommend testing this, by adding a ‘name of it starts with…’ to filter the bes computers returned initially.
(it,(sum of items 1 of items 1 of (it, ((value of result from (bes property “OS”)of it, number of relevant fixlets whose(fixlet flag of it AND name of it starts with “MS”) of it) of bes computers whose (exists result from (bes property “OS”) of it and exists value of result from (bes property “OS”) of it))) whose (item 0 of it = item 0 of item 1 of it) of it)) of ((unique values of ((value of result from (bes property “OS”)of it) of bes computers whose (exists result from (bes property “OS”) of it AND exists value of result from (bes property “OS”) of it))))
Win2000 5.0.2195, 7033
Win2003 5.2.3790, 123839
WinNT 4.0.1381, 35
WinXP 5.1.2600, 358786
For 30k machines, it took 12 minutes, which is quite a while, but at least it gets the answer (or at least so I think). It only filters for fixlets that start with “MS” but you’ll want to add the extra filter elements, such as no corrupt fixlets, etc.
I think I’d want to re-write this just to take the fixlets listed in my patching baselines, but I just can’t face doing that right now.
Hopefully this will work for you, cstoneba.
-Jim
P.S. If someone sees where I’ve gone wrong somewhere, please post. I won’t be that surprised.
I’m testing your relevance from post #8, Jim, but it doesn’t seem to run for me. It doesn’t error out, or act like it is running, it just sits there. I’m testing in our QA environment which only has a couple dozen clients, so the number of clients isn’t the issue. Does your relevance need to be encapsulated with anything (exluding <?Relevance ?>) ?
Not sure why you wouldn’t get a result, unless it’s just fallen over and given up due to internal engine issues/memory contraints.
You could check you’re getting results of the two query sections. The ‘internal’ one (#1 - which is run as many times as number of properties you have - case for memoization by the engine?) that has all the detail about number of fixlets per machine and the ‘external’ one (#2), that create the list of properties.
Creates tuple of (property, num of fixlets)
#1: ((value of result from (bes property “OS”)of it, number of relevant fixlets whose(fixlet flag of it AND name of it starts with “MS”) of it) of bes computers whose (exists result from (bes property “OS”) of it and exists value of result from (bes property “OS”) of it))
results*:
Win2008R2 6.1.7600, 13
Win2008R2 6.1.7600, 10
Mac OS X Server 10.6.4, 0
Mac OS X 10.6.4, 0
WinXP 5.1.2600, 3
Win2008R2 6.1.7600, 22
…
Creates list of unique property values
#2: (unique values of ((value of result from (bes property “OS”)of it) of bes computers whose (exists result from (bes property “OS”) of it AND exists value of result from (bes property “OS”) of it)))
results*:
Mac OS X 10.6.3
Mac OS X 10.6.4
Mac OS X Server 10.6.1
Mac OS X Server 10.6.4
Win2003 5.2.3790
Win2008 6.0.6002
Win2008R2 6.1.7600
WinXP 5.1.2600
*(new v8 environment, run on server and 321 clients. YMMV.)
I’d meant to update my post and give credit where credit is due by mentioning that the summation section of the relevance was given to me to solve an different problem ages ago. It was provided by Aram Eblighatian, who is a typical Bigfix PS chap (smart, very friendly and very helpful). He said he’d pulled it from their internal KB database <-- nice thing to have access to.
Due to the awkward need to re-run the internal chunk of relevance that returns a lot of data, it may be necessary to do what Lee stated and extract the main data set into a report and then let a bit of javascript finish the grouping/summation part of the job. I’ll admit that I’d fallen into the trap of pursuing the technical goal (do it with relevance), and ignoring the more practical issue…will it actually come up with a result in a realistic timeframe (if at all)?
-Jim
P.S. Although I’d admitted to having taken a sip or two of legal, recreational beverages, Lee had obviously fallen into a vat of something quite potent and lingered a while, before climbing out and posting his last reply.
As Jim says, i would suspect that an expression like that is reaching some sort of resource issue. The reason being that the expression is constantly looking up all bes computers, which is usually the largest set of items in a deployment. Also, try running the expression in the presentation debugger with the “String” evaluation mode (and removing the “<?Relevance ?>” tags), just to remove some layers of complexity.
I would suggest taking the approach Lee Wei suggests (its how i usually write my reports), but i was kinda curious to see if i could whip up a quicker expression, using sets(and some convoluted tuples):
( item 0 of it, sum of number of relevant fixlets whose ( ( fixlet flag of it AND name of it starts with
"MS" ) ) of elements of items 1 of it ) of ( item 2 of it, sets of items 2 of ( item 0 of it, item 2 of it, elements of item 1 of it ) whose ( value of results ( item 0 of it, item 2 of it ) = item 1 of it ) ) of ( item 0 of it, item 1 of it, unique values of values of ( results ( item 0 of it, elements of item 1 of it ) ) ) of ( it, bes computer set ) whose ( exist results ( item 0 of it, elements of item 1 of it ) whose ( not error flag of it ) ) of bes property
"OS"
i
think
this is a more efficient expression, since it looks up bes computers only a few times, and bes properties only once, but i dont have a large deployment handy to actually test it on. Let me know how it goes.
EDIT
I challenged Ben Kus to a “who can write a better relevance expression”-off, and this is what he come up with :
(it, multiplicity of it) of unique values of values of results (bes property whose (name of it =
"OS" and reserved flag of it =
true), computers of results whose (relevant flag of it =
true) of (bes fixlets whose (name of it starts with
"" and fixlet flag of it =
true)))
Note that his ONLY works in webreports though (the “fixlet results” inspector is a webreports only inspector).
My expression is faster on my small deployment, but i am not sure at scale…
Not sure I can follow that one directly, but in some of my other tests I had tried to derive the main ‘chunk’ of data at the beginning as a set and then try to calculate the values from there, but in at least two of the ways I wanted to do it meant I tried to use “item 0 of it” as the first parameter of the concatenation and substring separated functions - which they don’t allow.
I’ve had troubles using “item of it” in whose clauses before as well, but I think it’s because I targeted function results and not tuples before. Too late now, but might have a stab at it tomorrow.
As far as running your code, I will have to spend a little time unravelling your gem, but when I ran it on our v7.1.1 (30k clients - 2.5 minutes) and v8.0.584 (321 clients) environments, it came back with all the OS’s listed, but the totals were all 0.
I’m sure you’ll find where the bug is and get a good result. I just wonder if I’ll ever get my head around relevance enough to come up with constructs like that.
Oops, had a little bug in the final whose clause, fixed it now.
I always think it’s easier to incrementally build a relevance expression than to understand what someone else wrote (especially if they go tuple crazy like i do)
Ben’s code in post #13 works great, and only takes about 15sec to run in our 8,000 endpoint deployment. The issue I have though is when I replace the (what I call, built in properties (aka OS, CPU, etc) with my own property name of “Platform Support”, I get "Singular expression refers to nonexistent object. "
nevermind, I had to get rid of the code “and reserved flag of it = true”. Works now. Now I just need to get it into a table. Thanks for everyones help.