Topic
  • 3 replies
  • Latest Post - ‏2013-10-09T14:34:39Z by JohnnyRapid
JohnnyRapid
JohnnyRapid
7 Posts

Pinned topic Retrieve properties along with computer id

‏2013-10-08T16:19:12Z |

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 
)
of bes computers
  • Lee Wei
    Lee Wei
    599 Posts

    Re: Retrieve properties along with computer id

    ‏2013-10-08T17:22:03Z  

    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 ("<none>")),
        (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 ("<none>")),
        (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 ("<none>")))
    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))
           

  • JohnnyRapid
    JohnnyRapid
    7 Posts

    Re: Retrieve properties along with computer id

    ‏2013-10-08T18:03:16Z  
    • Lee Wei
    • ‏2013-10-08T17:22:03Z

    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 ("<none>")),
        (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 ("<none>")),
        (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 ("<none>")))
    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))
           

    Thank you for your speedy response.

    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?

  • JohnnyRapid
    JohnnyRapid
    7 Posts

    Re: Retrieve properties along with computer id

    ‏2013-10-09T14:34:39Z  
    • Lee Wei
    • ‏2013-10-08T17:22:03Z

    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 ("<none>")),
        (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 ("<none>")),
        (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 ("<none>")))
    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))
           

    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"