(imported topic written by gdaswani91)
Hello,
I’m trying to perform the following relevance query via the SOAP service and I’m running into a problem with some of the computers not having the property and throwing an
“Error: Singular expression refers to nonexistent object”
( id of it as string &
"^" & value of ( results from (bes property
"Asset Tag") of it) &
"^" & value of ( results from (bes property
"OS - Version") of it) ) of bes computers whose (operating system of it as lowercase starts with
"win")
How do I perform something like an outer join on the properties so that if the property in question doesn’t exist for said instance of bes computer, i’ll be able to set it to an empty string instead.
Something like (pseudocode)
( id of it as string &
"^" & (
if exists( bes property
"Asset Tag")) of it then value of ( results from bes property
"Asset Tag") of it )
else
"") ) of bes computers whose (operating system of it as lowercase starts with
"win")
Thanks.
(imported comment written by jessewk)
Here are three ways. The first is easiest to read. The second is slightly more efficient but uses a not so obvious trick. The third is the rockin’ new syntax that will be coming in 8.0 (excited yet?).
(id of it as string & “” & (if (exists results from (bes properties “Asset Tag”) of it) then (value of results from (bes properties “Asset Tag”) of it) else ("")) & “” & (if (exists results from (bes properties “OS - Version”) of it) then (value of results from (bes properties “OS - Version”) of it) else (""))) of bes computers whose (operating system of it as lowercase starts with “win”)
(id of it as string & “” & concatenation “” of (if (exists value of it) then (value of it) else ("")) of results from (bes properties “Asset Tag”) of it & “” & concatenation “” of (if (exists value of it) then (value of it) else ("")) of results from (bes properties “OS - Version”) of it) of bes computers whose (operating system of it as lowercase starts with “win”)
(id of it as string & “” & (value of results from (bes properties “Asset Tag”) of it | “”) & “” & (value of results from (bes properties “OS - Version”) of it | “”)) of bes computers whose (operating system of it as lowercase starts with “win”)
(imported comment written by gdaswani91)
been mucking around with it and came up with the following which seems to work
(id of it as string &
"^" & name of it &
"^" & (
if (exists value of (results from (bes property
"Asset Tag") of it)) then value of (results from (bes property
"Asset Tag") of it)
else
"") &
"^" & name of it &
"^" & (
if (exists value of (results from (bes property
"Serial Number") of it)) then value of (results from (bes property
"Serial Number") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"DNS Name") of it)) then value of (results from (bes property
"DNS Name") of it)
else
"") &
"^" & concatenation
"," of values of results (bes property(
"IP Address"),it) &
"^" & last report time of it as string &
"^" & (
if (exists value of (results from (bes property
"Domain/Workgroup - Windows") of it)) then value of (results from (bes property
"Domain/Workgroup - Windows") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"BIOS") of it)) then value of (results from (bes property
"BIOS") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"Computer Manufacturer - Windows") of it)) then value of (results from (bes property
"Computer Manufacturer - Windows") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"Computer Model - Windows") of it)) then value of (results from (bes property
"Computer Model - Windows") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"OS - Version") of it)) then value of (results from (bes property
"OS - Version") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"OS Service Pack Level") of it)) then value of (results from (bes property
"OS Service Pack Level") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"OS Version Number - Windows") of it)) then value of (results from (bes property
"OS Version Number - Windows") of it)
else
"") &
"^" & (
if (exists value of (results from (bes property
"RAM") of it)) then value of (results from (bes property
"RAM") of it)
else
"") &
"^" & cpu of it &
"^" & (
if (exists value of (results from (bes property
"Number of Processors - Windows") of it)) then value of (results from (bes property
"Number of Processors - Windows") of it)
else
"") ) of bes computers whose ( (last report time of it > now - 1*hour) AND (operating system of it as lowercase starts with
"win") )
The only problem is that it’s
REALLY
slow (it takes a while for the server to respond) and I believe it has to do with all the IF statements. Do any of you guru’s have any hints on how to speed this up?
(imported comment written by gdaswani91)
Thanks Jesse , didn’t see your post. I’ll try out #2 tomorrow and see if its faster. We have 230K+ computers in our BES system, and even when doing the (id of it mod 10 =
http://0…9
) in parallel it takes a while…