I am trying to retrieve multiple property results along with the computer id of the computer they correspond to. I am hoping to have each property correspond to a different column and only have one row per computer. So far I have the following which does not meet these requirements and additionally doesn’t even return data.
(
values of results from
(
bes property "Serial Number"
) of it,
values of results from
(
bes property "Model"
) of it,
id of it
The statement that you have has 2 potential issues.
Firstly, if a computer is missing either the Serial Number or Model, then it will not be returned as part of the results.
Also, the construct bes property “SomeName” is not a good form because the result is undeterministic if it is not unique.
I used the Excel Connector, which generated the following statement for me.
Note that the If-Then-Else statement is used to ensure that a computer is returned even if not data is available.
Also note the specific reference by ID of the properties.
(
id of item 0 of it,
(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 “%0A” of values of result (item 0 of it, item 1 of it))
else ("")),
(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 “%0A” of values of result (item 0 of it, item 2 of it))
else ("")),
(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 “%0A” of values of result (item 0 of it, item 3 of it))
else ("")))
of (
bes computers,
bes property “Computer Name”,
bes property whose (name of it = “Model” and
(item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 3) of id of it),
bes property whose (name of it = “Serial Number” and
(item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 8) of id of it))
My trouble with this query is that it returns rows for computers that do not have those properties. I need all properties (there will at least be 4 of them, though I omitted the others for the purpose of brevity) and each must be defined. How would I accomplish this?
Thanks again!
EDIT:
(concatenation “%0A” of values of result (item 0 of it, item 3 of it))
upon further reflection I understand all but the ‘concatenation “%0A”’. With these properties could there be duplicates? What does the %0A entail?
I am accomplishing this by adding a “whose” clause with
exists result (item 0 of it , item 1 of it)
and exists values of result (item 0 of it , item 1 of it)
And getting rid of the if/then since they are no longer needed.
The full query being
(
id of item 0 of it,
concatenation “%0A” of values of result (item 0 of it , item 1 of it),
concatenation “%0A” of values of result (item 0 of it , item 2 of it)
)
of
(
bes computers,
bes properties whose (name of it = “Model” and
(item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 3) of id of it),
bes properties whose (name of it = "Serial Number" and
(item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 8) of id of it)
)
whose
(
exists result (item 0 of it , item 1 of it)
and exists values of result (item 0 of it , item 1 of it)
and exists result (item 0 of it , item 2 of it)
and exists values of result (item 0 of it , item 2 of it)
)
EDIT: “bes property” had to become “bes properties”