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.
