Get computers where property value matches the values in the list

Hello,
I’m trying to get list of computers where certain property value is in the list of values - ie. in my case I’m trying to find out which machines are owned by some users using their email address. I can do it one by one but there is a thousands of emails so I was trying to speed it up by using “contains” first where all emails were part of one string - that kind of works but fails to skip the machines where email is short and matches some longer email (eg. com@email.com matches also broadcom@email.com and viacom@email.com). Next idea was to use set of strings but I’m not able to properly formulate relevance and getting mostly not very helpful “this expression could not be parsed” error :).

This is what I’m trying to do
"(id of it | 0, hostname of it | “None”,"
“last report time of it as string | “Thu, 1 Jan 1970 00:00:00 +0000”,”
“value of result (it, bes property “{mail}”) as string | “None”,”
“value of result (it, bes property “{user_id}”) as string | “None”,”
“value of result (it, bes property “{lock}”) as string | “True””
") of bes computers whose ( set of ({emails_list}) contains “
”(value of result (it, bes property “{mail}”) as string as lowercase | “None”)))"

where emails list is like (“email1@com”;“email2@com”…) etc

Any idea how to properly match the {mail} property against the {emails_list}?

Thx

I can post on this in a few hours but in the meantime have a read at Efficient Session Relevance Query for Computer Properties

The theme that repeats is that you’d want to use ‘sets’ wherever possible to query efficiently. If you build the query like

names of bes computers whose (value of result (it, bes property "{mail}") = "email1.example.com")

what happens is, for every BES Computer, we have to go lookup the definition for property “{mail}”, then look up the result from that property, then lookup the value of the result of the property. Often the longest part of this is looking up the property definition, and if you have a thousand endpoints you repeat that lookup a thousand times.

For ease in changing the list of email address to target, we’d build a ‘string set’ of the email addresses first. I don’t have an ‘{email}’ property in my deployment, so for examples’ sake I’m using a different property “DNS Name”, but it’s the same logic.

  1. Build a set of values for filtering

    set of (
    ( “endpoint-1.d.domain.home”
    ; “Cent-1.domain.home”
    ; “osd.domain.home”
    ; “dc.d.domain.home”
    )
    as string as lowercase)

  2. Given that a ‘set’ is treated as a single item instead of it’s individual elements, performing a BES Property lookup along with a ‘set’ only looks up the property definition once. I have 4 properties in my list of hostnames to search, but the lookup of the “DNS Name” property only happens once

     (
       bes properties whose (reserved flag of it and name of it = "DNS Name")
       , it
     ) of
     set of (
             ( "endpoint-1.d.domain.home"
              ; "Cent-1.domain.home"
              ; "osd.domain.home"
              ; "dc.d.domain.home"
             )
             as string as lowercase)
    

Next, we’ll want to filter so that we only have the property results for “DNS Name” where the value in that result is contained by the list of hostnames we’re targeting. Here, the ‘values of items 0’ returns the value from the results, which I include just to test that the query works so far:

values of items 0 of
(
  results of item 0 of it,
  item 1 of it
) whose (value of item 0 of it as string as lowercase is contained by item 1 of it)
of
(
 bes properties whose (reserved flag of it and name of it = "DNS Name")
 , it)
of set of (
        ("endpoint-1.d.domain.home"
         ;"Cent-1.domain.home"
         ;"osd.domain.home"
         ;"dc.d.domain.home"
) as string as lowercase)

But, it’s not the values of the BES Result that we want, but the computers that gave those results. Let’s put those computers into a new computer set, and then look up the values of the other properties that we want to retrieve. Finally, I’m going to retrieve two built-in properties, you’d replace these with your custom properties and then refer to them as item 1, item 2, item 3, etc…

(
  name of item 0 of it | "No name",
  id of item 0 of it as string | "No computer ID",
  value of (result (item 0 of it, item 1 of it)) | "None",
  value of (result (item 0 of it, item 2 of it)) | "None"
) of 
(
  elements of item 0 of it,
  item 1 of it,
  item 2 of it
) of
(
  it,
  bes properties whose (reserved flag of it and name of it = "Relay"),
  bes properties whose (default flag of it and name of it = "Total Size of System Drive")
) of
set of computers of items 0 of
(
  results of item 0 of it,
  item 1 of it
) whose (value of item 0 of it as string as lowercase is contained by item 1 of it)
of
(
 bes properties whose (reserved flag of it and name of it = "DNS Name")
 , it)
of set of (
        ("endpoint-1.d.domain.home"
         ;"Cent-1.domain.home"
         ;"osd.domain.home"
         ;"dc.d.domain.home"
) as string as lowercase)

This gives results like

cent-1.domain.home, 15019766, BES-Root.domain.home:52311, 40524 MB
ENDPOINT-1, 548870062, BES-Root.domain.home:52311, 129168 MB
DC, 1626921079, BES-Root.domain.home:52311, 65167 MB

For what it’s worth, my test instance has around 5k endpoints, and this lookup took 26 milliseconds to complete.

3 Likes

I recall reading this one. It did not help much to clarify the situation - maybe I’m just too dumb to get it :slight_smile: . Honestly speaking the documentation for bigfix is really poor and mostly the best sources of information are here in the forum. For example - I was about to get information about actions using REST API /api/action/{action id} endpoint - according to documentation I should get XML representation of the specified action id but I just get name for some actions IDs while for some others I get also relevance detils. Why? Who knows… there is no information nor warning when sending the query to bigfix server so I do not even have anything to start with and all is try’n’error approach :confused:

Thanks @JasonWalker, this is pretty comprehensive and gives me a new way how to look on building query. The thing is that even when I try to use your query exactly (well just replacing the DNS names) it does not work and ends up with “This expression could not be parsed”. I’m using REST API query endpoint if that makes some difference…

( 
  name of item 0 of it | "No name",
  id of item 0 of it as string | "No computer ID",
  value of ( result ( item 0 of it, item 1 of it )) | "None",
  value of ( result ( item 0 of it, item 2 of it )) | "None"
) of 
( 
  elements of item 0 of it,
  item 1 of it, 
  item 2 of it
) of 
(
  it,
  bes properties whose (reserved flag of it and name of it = "Relay"),
  bes properties whose (default flag of it and name of it = "Total Size of System Drive")
) of 
set of computers of items 0 of 
( 
  results of item 0 of it,
  item 1 of it 
) whose (value of item 0 of it as string as lowercase is contained by item 1 of it)
of 
( 
  bes properties whose (reserved flag of it and name of it = "DNS Name"),
  it
) of set of ( 
  ("caesar";
   "byju" 
   ) as string as lowercase)


<?xml version="1.0" encoding="UTF-8"?>
<BESAPI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="BESAPI.xsd">
	<Query Resource="( name of item 0 of it | &quot;No name&quot;, id of item 0 of it as string | &quot;No computer ID&quot;, value of ( result ( item 0 of it, item 1 of it )) | &quot;None&quot;, value of ( result ( item 0 of it, item 2 of it )) | &quot;None&quot;) of ( elements of item 0 of it, item 1 of it, item 2 of it) of (it, bes properties whose ( reserved flag of it and name of it = &quot;Relay&quot; ), bes properties whose ( default flag of it and name of it = &quot;Total Size of System Drive&quot; )) of set of computers of items 0 of ( results of item 0 of it, item 1 of it ) whose ( value of item 0 of it as string as lowercase is contained by item 1 of it ) of ( bes properties whose ( reserved flag of it and name of it = &quot;DNS Name&quot; ), it ) of set of ( (&quot;caesar&quot;">
		<Result></Result>
		<Error>This expression could not be parsed.</Error>
	</Query>
</BESAPI>

Oh I see the REST API strips everything after semicolon!

Fixed now. I’m still not sure how to send the GET request with relevance containing semicolon but POST works.
Thanks again @JasonWalker :vulcan_salute:

Saving the relevance to a file and using POST is far easier. If you’re using a command-line utility like iem.exe or curl, you have to deal with URL-encoding the string ( curl can do that for you with --data-urlencode ).

1 Like

A GUI like Insomnia may be helpful as well…when you set everything up as you like,.it can generate a script or curl or whet command lines for you

Will check Insomina thanks for the tip. Currently I’m using RESTED extension in browser for quick things and otherwise I have custom python app for communicating with Bigfix (mostly used just for setting up various properties on machines to be able to control what users can do like access the usb storage, get the certificates etc)

Another option is to look at the location by subnet property, You can create something similar so the serial number matches up with values so its more of a dynamic property. The only downfall is you have to update the data regularly but this worked for me when i was a customer.

https://www.linkedin.com/pulse/bigfix-customizing-location-subnet-property-brad-sexton/