I am trying to return a list of information for input into a separate database. However, this query takes ~45 minutes to complete on both REST API (< server>:< port>/api/query?output=json&relevance=< relevance>) and SOAP API (BigFix Excel Connector) and is therefore useless. It’s only returning 6.5k rows (which is the correct amount). The reasoning for this format is an error saying I’m referring to a non-existent object.
( item 0 of it as string,
item 1 of it as string,
item 2 of it as string,
item 3 of it as string,
item 4 of it as string,
item 5 of it as string,
item 6 of it as string,
item 7 of it as string,
item 8 of it as string,
item 9 of it as string,
item 10 of it as string,
item 11 of it as string,
item 12 of it as string )
of (
(if (exists result (item 0 of it, item 1 of it) and
exists values of result (item 0 of it, item 1 of it) )
then (concatenation “;” of values of result (item 0 of it, item 1 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 2 of it) and
exists values of result (item 0 of it, item 2 of it) )
then (concatenation “;” of values of result (item 0 of it, item 2 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 3 of it) and
exists values of result (item 0 of it, item 3 of it) )
then (concatenation “;” of values of result (item 0 of it, item 3 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 4 of it) and
exists values of result (item 0 of it, item 4 of it) )
then (concatenation “;” of values of result (item 0 of it, item 4 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 5 of it) and
exists values of result (item 0 of it, item 5 of it) )
then (concatenation “;” of values of result (item 0 of it, item 5 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 6 of it) and
exists values of result (item 0 of it, item 6 of it) )
then (concatenation “;” of values of result (item 0 of it, item 6 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 7 of it) and
exists values of result (item 0 of it, item 7 of it) )
then (concatenation “;” of values of result (item 0 of it, item 7 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 8 of it) and
exists values of result (item 0 of it, item 8 of it) )
then (concatenation “;” of values of result (item 0 of it, item 8 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 9 of it) and
exists values of result (item 0 of it, item 9 of it) )
then (concatenation “;” of values of result (item 0 of it, item 9 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 10 of it) and
exists values of result (item 0 of it, item 10 of it) )
then (concatenation “;” of values of result (item 0 of it, item 10 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 11 of it) and
exists values of result (item 0 of it, item 11 of it) )
then (concatenation “;” of values of result (item 0 of it, item 11 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 12 of it) and
exists values of result (item 0 of it, item 12 of it) )
then (concatenation “;” of values of result (item 0 of it, item 12 of it))
else (“NULL”)),
(if (exists result (item 0 of it, item 13 of it) and
exists values of result (item 0 of it, item 13 of it) )
then (concatenation “;” of values of result (item 0 of it, item 13 of it))
else (“NULL”)))
of (bes computers, bes property “ID”, bes property “Computer Type”, bes property “Device Type”, bes property “IP Address”, bes property “DNS Name”, bes property “cid”, bes property “OS”, bes property “MAC Addresses - Windows”, bes property “MAC Addresses - Mac OS X”, bes property “MAC Addresses - Unix”, bes property “Computer Model - Windows”, bes property “Machine Model - Mac OS X”, bes property “Computer Manufacturer - Windows”)
I have tried rewriting this query like this with no improvement (I think it actually takes longer):
(
id of it,
values of property results whose (name of property of it contains “Computer Type”) of it,
device type of it,
concatenation “;” of (ip addresses of it as string),
hostname of it,
(value of it) of client settings whose (name of it is “cid”) of it,
(if (operating system of it contains “Win”)
then (values of property results whose (name of property of it contains “Full Operating System Name”) of it)
else (operating system of it)),
concatenation “;” of (values of property results whose (name of property of it contains “MAC Addresses”) of it),
values of property results whose (name of property of it contains “Model -”) of it,
values of property results whose (name of property of it contains “Manufacturer”) of it
)
of bes computers
After some experimentation, it would seem that when I have to search for a non-standard property - “values of property results whose (name of property of it contains ‘< something>’) of it” - causes the slow down.
Is there a better way to get these properties and therefore speed up this query to something more reasonable? Ex. “hostname of it” returns the property “DNS Name”.