9 Replies Latest reply on Mar 21, 2015 11:06 AM by azabielski

    Problem with SWQL query

    erik.boyer@hcr-manorcare.com

      I am attempting to generate a table in a report using SWQL. I have groups setup with devices inside based on a dynamic query. Then those groups are children of other groups. I am trying to get the contents of all of the children groups as node details (name and IP address) in the report for use in an export / import custom properties csv. I am trying to use the following SQWL/SQL but it errors out saying "object reference not set to an instance of an object".

       

      select ContainerMembers.Name, ContainerMembers.MemberPrimaryID, ContainerID, Groups.Name as GroupName from Orion.ContainerMembers

      left join Orion.Groups

      on ContainerMembers.ContainerID=Groups.ContainerID

      where Groups.Name IN (

        select ContainerMembers.Name from Orion.ContainerMembers

        left join Orion.Groups

        on ContainerMembers.ContainerID=Groups.ContainerID

        where Groups.Name = 'MyParentGroupName'

      )

      group by Groups.Name

       

      If you run the sub-select statement by itself it returns results just fine:

       

        select ContainerMembers.Name from Orion.ContainerMembers

        left join Orion.Groups

        on ContainerMembers.ContainerID=Groups.ContainerID

        where Groups.Name = 'MyParentGroupName'

       

      If you run the outside select statement by itself with a static list of group names, it also works just fine:

       

      select ContainerMembers.Name, ContainerMembers.MemberPrimaryID, ContainerID, Groups.Name as GroupName from Orion.ContainerMembers

      left join Orion.Groups

      on ContainerMembers.ContainerID=Groups.ContainerID

      where Groups.Name IN (

      'groupName1', 'groupName2', 'groupName3'

      )

      group by Groups.Name

       

      What am I doing wrong? Is there an easier way to manage the nodes contained in a group through the "manage nodes" page? It doesn't let you group/sort the nodes by group which is why I am trying to go with the 'report export to csv' then import route for custom properties.