Seeking some guidance / assistance with some session relevance please.
I have a custom property, RegistrationID, and I want to get a list of all computer names and computer ids along with the property results for RegistrationIDfor every instance where RegistrationIDis not unique.
I can figure out how to pull all the values that are not unique which I believe I can then use as a lookup (a set) to pull all computer names, ids and property results but I can’t figure out then next step on how to join the list of duplicates and parse the computer names/ids and proppoerty result for each duplicated result, and for it to be an efficient query.
(unique values whose (multiplicity of it > 1) of it whose (it contains "-") of values of results of bes property "RegistrationID")
You’d need to run through the results of the property twice essentially - once to get the list of the duplications and then once to physically grab what machines have that as value. Try something like this (there is easier way to write too but it doesn’t scale well, in my experience using sets would be the most efficient one):
(item 0 of it, item 1 of it) of (name of item 0 of it|"Missing Name", (if (size of item 1 of it >= 1) then ((if it = "" then "" else (it)) of concatenation ";" of values whose (it contains "-") of results (item 0 of it, elements of item 1 of it)) else ("N/A")), item 2 of it) whose (item 1 of it is contained by item 2 of it) of (elements of item 0 of it,item 1 of it,item 2 of it) of (set of bes computers, set of bes properties whose (name of it as lowercase = ("RegistrationID") as lowercase), set of unique values whose (multiplicity of it > 1) of values whose (it contains "-") of results of bes properties whose (name of it as lowercase = ("RegistrationID") as lowercase))
Thank you @ageorgiev, thats brilliant. That returns the computer names and property result and very efficient given we have > 100k endpoints and it took only 3 secs to complete. Is it possible to add the computer ID to the results? We have enviromnets where the computer name isn’t unique so the computer ID is the only unique key we can use to differentiate between systems.
Yea, should be very scalable. I have had similarly structured query retrieving 50+ properties for 20k machines with no issues (it does become gigantic and hectic to follow but apart from that)…
(item 0 of it, item 1 of it, item 3 of it) of (name of item 0 of it|"Missing Name", (if (size of item 1 of it >= 1) then ((if it = "" then "" else (it)) of concatenation ";" of values whose (it contains "-") of results (item 0 of it, elements of item 1 of it)) else ("N/A")), item 2 of it, id of item 0 of it) whose (item 1 of it is contained by item 2 of it) of (elements of item 0 of it,item 1 of it,item 2 of it) of (set of bes computers, set of bes properties whose (name of it as lowercase = ("RegistrationID") as lowercase), set of unique values whose (multiplicity of it > 1) of values whose (it contains "-") of results of bes properties whose (name of it as lowercase = ("RegistrationID") as lowercase))
Thank you so much @ageorgiev. Works like a charm. I’ll use this as a reference point for future use cases…I always struggle with this type of session relevance