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.

  • 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?

  • I'm attempting to build this in SWQL Studio before throwing it into a report datasource.

    An additional explanation of what I'm trying to accomplish can be found in my reply on this thread: Re: Custom SWQL Query Questions

    Here's a screenshot out of SWQL Studio:

    ScreenShot2751.png

    If I replace the second select query with a list of a few groups, it returns successfully:

    success query.png

  • Ok, I'm working on this, but my tables are a bit different. What version of NPM do you have?

    I'm in the process of creating a couple nested groups so I can validate this.

  • Here's what I show on my side for version info:

    Orion Platform 2014.2.1

    SAM 6.0.1

    QoE 1.0

    NCM 7.3.2

    NPM 11.0.1

    IVIM 1.10.0

    VNQM 4.2

  • I believe this is because SWQL returns XML and a where IN with a select statement inside the select statement is more XML that the where clause can not parse

    <a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>

    RETURN XML RAW</query>

          <parameters>

          </parameters>

        </QueryXml>

      </s:Body>

    Give me a few to play around with the tables i'll get you something working i assume it has to be in SWQL? Cause in SQL it works fine.

    select cms.Name,c.Name from ContainerMemberSnapshots as cms

    left join Containers as c on c.ContainerID = cms.ContainerID

    where c.Name IN (

      select cms.Name from ContainerMemberSnapshots as cms

      left join Containers as c on c.ContainerID = cms.ContainerID

      where c.Name = 'Parent Group Name'

    )

  • ‌The only reason it has to be SWQL is because I need to use it in a report to export to XLS format so I can then import a bunch of custom properties. It would be nice if the "manage nodes" view allowed us to group or sort by the groups we create then I wouldn't need to do this. If you can think of an easier way to do this I would be open to it...

    Thanks!

  • I just made a quick test report using the SQL query type and i can export to excel right from the report. Additionally you can just query the database select all the columns and rows, then right click and hit save with headers and paste into your own XLS.

    This would save a ton of time trying to figure out a hack around the XML issue

    ss1.PNG