This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Problem with SWQL query

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.

Parents
  • Have you tried doubling up your parentheses around the sub-query? I could be completely wrong at this (still very new at SQL) but it looks like your argument for your IN statement is (sub-query), and IN is looking for a list of values. The sub-query is supposed to populate these values, so shouldn't it be IN ((sub-query)), since the sub-query needs to be isolated within it's own parentheses?

    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

  • Tried that just now and still a no-go.

    I did try a modified query to get additional Node details out of the query by joining a different direction (starting from the Orion.Nodes table versus the Groups table) but it also has been unsuccessful. I think either query should work, just something isn't right with the syntax...

  • when you get the 'object reference not set to an instance of an object' error, is it telling you a line? can you repeat the query inside the database manager and receive results?

Reply Children