Efficient Session Relevance Query for Computer Properties

[brolly33]

REPOST of the old BigFix Relevance Musings Blog posting. The IBM Dev works copy is no longer active. This is the most popular entry, so I am parking a copy here until I have a new home for relevance blogging.

There are many ways to query computer properties in BigFix. Web Reports has the Explore page, console can show you properties for a computer, REST API can pull computer properties.
Sometimes you want an automated way to pull specific properties from a BigFix server serving a large number of total endpoints and you want to pull a few property values across all of the endpoints, and do it with automation/code. Session relevance will allow you to query “as needed” and pull properties.

(name of it, values of results (it, bes property "Computer Name"), values of results (it, bes property "IP Address") ) of bes computers

Simple and straight-forward. Take each computer, then get the values of the results from the property named “Computer Name” and "IP Address"for that specific computer.

• What could go wrong, you ask?
• More than one Property with that name.
• No properties with that name.
• Results plural will create one line per result. If all your property results are plural, you will get a cross product (mess)
• Missing results will drop that computer entry out of the tuple
• You create your property objects once for each computer. If you have 250,000 computers, that is a whole lot of creation of BES Property objects.

What can you do about it? Trap all of those possibilities and use Sets to conquer that subtle efficiency issue. I have been fooling around with this code structure for quite some time and I would like to share my current best of breed BigFix property query in session relevance

We start with a single, flattened tuple of sets. Notice that we only create each property object once and that I take care of case sensitivity. You can easily add more lines in for more properties. Note also that duplicate properties will be captured in those sets. We will catch that error later.

(
set of BES computers
, set of  bes properties whose (name of it as lowercase = ("Computer Name") as lowercase)
, set of  bes properties whose (name of it as lowercase = ("IP Address") as lowercase)
)

Next step is very simple. You expand the BES Computer set in item 0. This gets you each of your computers “on their own lines”. The other items, your property sets, you just pass through, untouched.

 (
elements of item 0 of it  /*expand the computer set into single computers*/
,item 1 of it
,item 2 of it
) of 

final step is the part where all the error trapping will happen. First we will test our property sets to make sure they only have 1 property in them. If there are more than 1 or less than 1, we can then trap those errors separately.

if (size of item 1 of it = 1) then (/*good stuff*/) else (/*trap errors*/)

Let’s look at what will go into/*trap errors*/. If we have more than one property, we output an error along with the names and IDs of the duplicated properties. If there are no properties in our set, we still have a set object (empty one) and we can report that the property does not exist.

if (size of item 2 of it > 1) then (("Property 2 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 2 of it) as string) else ("Property 2 does not exist")

Now let’s look at the /*good stuff*/ branch of our if. If the property has no values, we catch the error and report it. If there are many values, we concatenate them into one line with a ; separator. I am not trapping for comma’s inside of the data, as the relevance engine will just parenthesize that case for me without extra code.

(if it = "" then "Property 2: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 2 of it)

Those are the major features. Now let’s enjoy the the entire relevance statement assembled into one. You can easily add in additional tuple elements at each of the 3 layers if you want to add additional properties into this query. Note that missing properties or property results do not result in any dropped rows, and you exactly get one row per computer record, if it has results or not.

(
name of item 0 of it|"missing Name"
, (concatenation ";" of values of results (item 0 of it, elements of item 1 of it))
, (if (size of item 2 of it = 1) then ((if it = "" then "Property 2: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 2 of it)) else (if (size of item 2 of it > 1) then (("Property 2 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 2 of it) as string) else ("Property 2 does not exist")))
, (if (size of item 3 of it = 1) then ((if it = "" then "Property 3: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 3 of it)) else (if (size of item 3 of it > 1) then (("Property 3 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 3 of it) as string) else ("Property 3 does not exist")))
, (if (size of item 4 of it = 1) then ((if it = "" then "Property 4: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 4 of it)) else (if (size of item 4 of it > 1) then (("Property 4 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 4 of it) as string) else ("Property 4 does not exist")))
, (if (size of item 5 of it = 1) then ((if it = "" then "Property 5: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 5 of it)) else (if (size of item 5 of it > 1) then (("Property 5 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 5 of it) as string) else ("Property 5 does not exist")))
) of (
elements of item 0 of it /*expand the computer set - gets you one line per computer*/
,item 1 of it
,item 2 of it
,item 3 of it
,item 4 of it
,item 5 of it
) of (
set of BES computers
, set of  bes properties whose (name of it as lowercase = ("BES Client Version") as lowercase) /*should usually exist and usually without duplicates */
, set of  bes properties whose (name of it as lowercase = ("FooBar") as lowercase) /*probably does not exist, but does not cause any errors or ommissions*/
, set of  bes properties whose (name of it as lowercase = ("operating system") as lowercase) /*usually multiple properties with this name - the output will report on this error and provide you property IDs*/
, set of bes properties whose (id of it = ((id of all bes sites whose (name of it = "ActionSite")), 4, 1)) /*example of hard coded property by ID in actionsite - this is Computer Name and it exists in all implementations*/
, set of  bes properties whose ((id of it =(1, 204, 1))) /*site 1 is BES Support - can use this method to get properties as well - this is BES Client Version in BES Support site on all BigFix servers*/
)

I added the flattened relevance below for ease of cut and paste.

(name of item 0 of it|"missing Name" , (concatenation ";" of values of results (item 0 of it, elements of item 1 of it)) , (if (size of item 2 of it = 1) then ((if it = "" then "Property 2: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 2 of it)) else (if (size of item 2 of it > 1) then (("Property 2 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 2 of it) as string) else ("Property 2 does not exist"))) , (if (size of item 3 of it = 1) then ((if it = "" then "Property 3: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 3 of it)) else (if (size of item 3 of it > 1) then (("Property 3 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 3 of it) as string) else ("Property 3 does not exist"))) , (if (size of item 4 of it = 1) then ((if it = "" then "Property 4: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 4 of it)) else (if (size of item 4 of it > 1) then (("Property 4 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 4 of it) as string) else ("Property 4 does not exist"))) , (if (size of item 5 of it = 1) then ((if it = "" then "Property 5: No values" else it) of concatenation ";" of values of results (item 0 of it, elements of item 5 of it)) else (if (size of item 5 of it > 1) then (("Property 5 duplicates: " & concatenation "|" of ((name of it) & "=" & (id of it as string)) of elements of item 5 of it) as string) else ("Property 5 does not exist")))) of (elements of item 0 of it /*expand the computer set - gets you one line per computer*/ ,item 1 of it ,item 2 of it ,item 3 of it ,item 4 of it ,item 5 of it) of (set of BES computers , set of bes properties whose (name of it as lowercase = ("BES Client Version") as lowercase) /*should usually exist and usually without duplicates */ , set of bes properties whose (name of it as lowercase = ("FooBar") as lowercase) /*probably does not exist, but does not cause any errors or ommissions*/ , set of bes properties whose (name of it as lowercase = ("operating system") as lowercase) /*usually multiple properties with this name - the output will report on this error and provide you property IDs*/ , set of bes properties whose (id of it = ((id of all bes sites whose (name of it = "ActionSite")) , 4, 1)) /*example of hard coded property by ID in actionsite - this is Computer Name and it exists in all implementations*/ , set of bes properties whose ((id of it =(1, 204, 1))) /*site 1 is BES Support - can use this method to get properties as well - this is BES Client Version in BES Support site on all BigFix servers*/)

[Aram]

This is for sure one of my favorite posts!

And in the spirit of sharing, here’s a client relevance code snippet that I use based on brolly33’s structure/approach here to automatically generate a session relevance statement given a list of desired property names (input via a file). It is especially handy if you have a relatively long list of properties you’d like to return to avoid having to manually build the session relevance statement by hand. For instance, I used this approach to automatically generate a session relevance statement to return data from over 100 properties at once! (Not something I’d generally recommend, but it worked for my use case).

/* efficient session relevance builder */
/* based on John Talbert's blog - https://www.ibm.com/developerworks/community/blogs/e9d21113-aa93-467e-ac77-a0d20a21eaec/entry/Session_Relevance_Computer_Properties_query_Efficiency?lang=en*/
/* be sure to update the *3* references to the file name/path containing the desired property names */
("(";
"name of item 0 of it | %22missing name%22";
(", (concatenation %22;%22 of values of results (item 0 of it, elements of item " & it & " of it))") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string));
") of (";
"elements of item 0 of it";
(",item " & it & " of it") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string);
") of (";
"set of BES computers";
(", set of  bes properties whose (name of it as lowercase = (%22" & it as trimmed string & "%22) as lowercase)") of lines of file "R:\Properties.txt";
")"

So, for instance, if you create a file called “R:\properties.txt” with the following contents:

Computer Name
OS
CPU
RAM
Free Space on System Drive

The result of the Client relevance would be the following Session relevance:

(
name of item 0 of it | "missing name"
, (concatenation ";" of values of results (item 0 of it, elements of item 1 of it))
, (concatenation ";" of values of results (item 0 of it, elements of item 2 of it))
, (concatenation ";" of values of results (item 0 of it, elements of item 3 of it))
, (concatenation ";" of values of results (item 0 of it, elements of item 4 of it))
, (concatenation ";" of values of results (item 0 of it, elements of item 5 of it))
) of (
elements 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
) of (
set of BES computers
, set of  bes properties whose (name of it as lowercase = ("Computer Name") as lowercase)
, set of  bes properties whose (name of it as lowercase = ("OS") as lowercase)
, set of  bes properties whose (name of it as lowercase = ("CPU") as lowercase)
, set of  bes properties whose (name of it as lowercase = ("RAM") as lowercase)
, set of  bes properties whose (name of it as lowercase = ("Free Space on System Drive") as lowercase)
)

This can be tweaked fairly easily to work with Property IDs rather than names as well in cases where that’s needed.

[blair_heiserman]

Does developer.bigfix.com have automatically generated list of all properties, casts, binary and unary operators for every supported platform?

[Aram]

Here’s a list of all properties/inspectors on developer.bigfix.com:

https://developer.bigfix.com/relevance/search/?query=*

The guides also provide a lot of good context and insight as do the general reference.

That said, if I’m understanding your question correctly, we do not publish the BigFix Properties that are provided with our content (within Analyses for instance) vs. the properties of inspectors that are available via client/session relevance.

[brolly33]

Thanks to @JasonWalker for tracking down a copy of the old blog in the wayback machine

https://web.archive.org/web/20200101142700/https://www.ibm.com/developerworks/community/blogs/e9d21113-aa93-467e-ac77-a0d20a21eaec?lang=en

It takes a while to load. Basic navigation seems to work. content seems intact.

[man7630]

@Aram, Can you post the flattened relevance for below code , I keep getting “A string constant had no ending quotation mark” error, maybe I am missing something.

("(";
"name of item 0 of it | %22missing name%22";
(", (concatenation %22;%22 of values of results (item 0 of it, elements of item " & it & " of it))") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string));
") of (";
"elements of item 0 of it";
(",item " & it & " of it") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string);
") of (";
"set of BES computers";
(", set of  bes properties whose (name of it as lowercase = (%22" & it as trimmed string & "%22) as lowercase)") of lines of file "R:\Properties.txt";
")"

[Aram]

It looks like you have some curly quotes in there (likely from copy/paste operations to certain programs). Replacing those with regular double-quotes should likely fix things, but here’s the flattened version as requested:

("("; "name of item 0 of it | %22missing name%22"; (", (concatenation %22;%22 of values of results (item 0 of it, elements of item " & it & " of it))") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string)) ; ") of ("; "elements of item 0 of it"; (",item " & it & " of it") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string) ; ") of ("; "set of BES computers"; (", set of bes properties whose (name of it as lowercase = (%22" & it as trimmed string & "%22) as lowercase)") of lines of file "R:\Properties.txt"; ")"

[jgstew]

This dashboard is related: https://github.com/jgstew/bigfix-content/blob/master/dashboards/Computer_Filter_Search.ojo

I wrote a custom session relevance query that could return as many built in computer properties as possible as efficiently as I could get it. It isn’t as generic of an answer as the above, but it is more specific. The hardest part is getting property results instead of just built ins since those are slower generally, but I really wanted to include MAC Addresses in the table, so I had to use the MAC Address properties to get that since this isn’t a built in one.

I had considered having the table query MAC Addresses only for the computers currently displayed in the table in a lazy load fashion, but then you couldn’t search for computers based upon MAC Addresses, which was the whole point of including it.

The dashboard takes a while to load with a large number of endpoints, but once it does, it seems pretty snappy even with 50k+ endpoints.

[genious_2k1]

Hi All,

I have tried using sets as stated in this forum and it does get results but I am stuck at a point where I am not able to filter the set of bes computers further. Below is the session relevance that I have and I get an error when I run it, which is “A singular expression is required”. If someone can point out what I am doing wrong, that will be super helpful.

P.S. - Property1, Property2 and Property3 are all custom properties created on some machines using analyses.

(
name of item 0 of it|“missing Name”
, (if (size of item 1 of it = 1) then ((if it = “” then “Property 1: No values” else it) of concatenation “;” of values of results (item 0 of it, elements of item 1 of it)) else (if (size of item 1 of it > 1) then (("Property 1 duplicates: " & concatenation “|” of ((name of it) & “=” & (id of it as string)) of elements of item 1 of it) as string) else (“Property 1 does not exist”)))
, (if (size of item 2 of it = 1) then ((if it = “” then “Property 2: No values” else it) of concatenation “;” of values of results (item 0 of it, elements of item 2 of it)) else (if (size of item 2 of it > 1) then (("Property 2 duplicates: " & concatenation “|” of ((name of it) & “=” & (id of it as string)) of elements of item 2 of it) as string) else (“Property 2 does not exist”)))
) of (
elements of item 0 of it /expand the computer set - gets you one line per computer/
,item 1 of it
,item 2 of it
) of (
set of BES computers whose ((name of it as lowercase starts with “ab” and (values of results (it, bes properties “Property3”) as lowercase = (“xyz”))) | (name of it as lowercase starts with “de”) | (name of it as lowercase starts with “fg”))
, set of bes properties whose (name of it as lowercase = (“Property1”) as lowercase)
, set of bes properties whose (name of it as lowercase = (“Property2”) as lowercase)
)

[trn]

Your relevance to build the computer set is flawed (even without the | to catch errors)

If you break it down to make sure you get what you expect at each stage, you can then build it back out to get the full set of results.

Simplifying to

	number of elements of 
	(
		set of BES computers 
		whose
		(
			(
				name of it as lowercase starts with "ab" 
			and
				(
					values of results 
					(
						it , bes properties "Property 3" 
					)
					as lowercase = 
					(
						"xyz" 
					)
				)
			)
		)
	)

will throw your error. Fix that and then continue.

[genious_2k1]

@trn - I tried this as well and get the same error “Singular expression is required”.

I think I may be doing this wrong (or have a syntactical error) but just feels like a deadlock in my head . Any help that you or @Aram @brolly33 @jgstew @JasonWalker can provide to figure this out, it will surely make my day.

[Aram]

Try something like the following:

(
name of item 0 of it | “missing name”
, (concatenation “;” of values of results (item 0 of it, elements of item 1 of it))
, (concatenation “;” of values of results (item 0 of it, elements of item 2 of it))
) of (
elements of item 0 of it
,item 1 of it
,item 2 of it
) of (
set of BES computers whose (name of it as lowercase starts with “ab” AND (concatenation of values of results (it, bes properties “Property 3”) as lowercase contains (“xyz”) as lowercase))
, set of bes properties whose (name of it as lowercase = (“Property 1”) as lowercase)
, set of bes properties whose (name of it as lowercase = (“Property 2”) as lowercase)
)

The reason you are getting the error (Singular expression is required) is that you are trying to perform a comparison between multiple objects and a single object (valueS … = “xyz”) in your filter. There are a few ways to address this, but one way is by concatenating the values into a single string…we can then perform our comparison.

[genious_2k1]

@Aram - This was interesting. It worked but - it did the same thing that I was trying to avoid. That is, it dropped all the entries where just one of the properties was set. I wanted all the results of machines where even one of the values Property 1 or Property 2 are set. What could be the way to get around this?

Edit:

I found out what I was doing wrong. I was using “|” as the “OR” operator

Now I have all the necessary results (thanks @Aram). Next thing is to figure out how to put this result set into a sorted table.

[nicksberger]

This is a very cool way to build session relevance. How simple would it be to change the delimiters to a || (double pipe) for example opposed to comma for the results. I ask because some results from our RP’s may contain a comma already so that would mess with formatting.

[Aram]

If you’re referring to the Client relevance that builds the session relevance based on brolly33’s approach, yes, it should be pretty simply to change the delimiter (in which case, the result won’t be a tuple object as far as BigFix is concerned). Here’s an example with double pipe:

/* efficient session relevance builder */ /* based on John Talbert's Forum Post - https://forum.bigfix.com/t/efficient-session-relevance-query-for-computer-properties/32820 /* be sure to update the *3* references to the file name/path containing the desired property names */ ("("; "name of item 0 of it | %22missing name%22"; ("& %22||%22 & (concatenation %22;%22 of values of results (item 0 of it, elements of item " & it & " of it))") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string)); ") of ("; "elements of item 0 of it"; (",item " & it & " of it") of (integers in (1,(number of lines of file "R:\Properties.txt")) as string); ") of ("; "set of BES computers"; (", set of bes properties whose (name of it as lowercase = (%22" & it as trimmed string & "%22) as lowercase)") of lines of file "R:\Properties.txt"; ")"

[nicksberger]

Awesome Aram thank you. I am new to this approach (as you can tell). Are there any examples where I can grab ID, Computer Name, and RELEVANT Fixlets from a custom site ?
Assuming it would be a combination of session relevance and REST API ?

[ncpeteusa]

/* efficient session relevance builder / / based on John Talbert’s Forum Post - Efficient Session Relevance Query for Computer Properties /* be sure to update the 3 references to the file name/path containing the desired property names */ ("("; “name of item 0 of it | %22missing name%22”; ("& %22||%22 & (concatenation %22;%22 of values of results (item 0 of it, elements of item " & it & " of it))") of (integers in (1,(number of lines of file “R:\Properties.txt”)) as string)); “) of (”; “elements of item 0 of it”; (",item " & it & " of it") of (integers in (1,(number of lines of file “R:\Properties.txt”)) as string); “) of (”; “set of BES computers”; (", set of bes properties whose (name of it as lowercase = (%22" & it as trimmed string & “%22) as lowercase)”) of lines of file “R:\Properties.txt”; “)”

is there a version of this that dosen’t contain all of the percent 22 values

[Aram]

is there a version of this that dosen’t contain all of the percent 22 values

Can you provide more details please on what you mean by this, or what you’re looking for? Is the relevance not working for you?

Assuming a file (R:\properties.txt) that contains property names on individual lines, this Client-side relevance should help create efficient session relevance:

[JasonWalker]

A post was split to a new topic: Splitting ang joining items in a property result