Need help creating a KPI for Modern dashboard in Solarwinds using the Custom SWQL query

We are in process of creating KPI widgets for a modern dashboard using custom SWQL queries.

We have a couple of count queries which are mentioned below:-

1. select Count(Distinct N.CustomProperties.XXXXXX) as Region_Count
FROM Orion.Nodes n where N.CustomProperties.XXXXXX <> ''

2. select count(Distinct Cont.Name) as countgroups from Orion.Container as Cont
join Orion.ContainerMemberSnapshots as Member
ON Cont.ContainerID = Member.ContainerID join Orion.Nodes as node ON Member.EntityID = node.NodeID WHERE ((Cont.DisplayName LIKE '%XXXX%'
OR Cont.DisplayName LIKE '%XXXXXXX%') AND Cont.status = 2)

Separately they are working perfectly fine, but i need help in getting a Numerical value by subtracting Query1 - Query2 with the help of SWQL query itself. 

I am not able to find anything related to subtraction of 2 count sub queries.

All the help will be appreciated.

hoping you guys have any inputs

Top Replies

Parents Reply Children
  • , can you show me the two query outputs and a description for them so I can get a better idea of the criteria. This will help me try and develop a similar use case I can use to hopefully build a query that accomplishes what you're asking.

  • Here you go

    Query 1

    select Count(Distinct N.CustomProperties.SERVICENOW_LOCATIONS) as Region_Count FROM Orion.Nodes n where N.CustomProperties.SERVICENOW_LOCATIONS <> ''


    In above query i am trying to get the count of the locations we currently have in our environment.

    Query 2 

    select count(Name) as countgroups from Orion.Container WHERE status = 2

    In above query i am trying to get how many groups are down.

    I the Final KPI widget i am trying to get output of Query 1 subtracts output of query 2. This KPI will provide me with the number of locations which are having problems in our environment

  • , does this help? I turned the left join into a subquery that then joins directly on the membersnapshots table to give it a valid column to join aganst. Everything regarding the output of the group count is done inside that area. Anything regarding the scope for the nodes is done outside of it. Might still need a little work cause the original query you provided and the one directly above dont match in terms of the where statement. 

    select count(distinct n.CustomProperties.XXXXX) as [Regions], count(distinct as [Problem Groups], (count(distinct n.customproperties.CustomerName) - count( as [Difference] 
    FROM orion.nodes n 
    left join (Select cont.ContainerID, cont.EntityID,
    from orion.ContainerMemberSnapshots cont
    where cont.status = '2' and cont.Container.DisplayName like '%XXXXX%'
    ) as t1 on t1.EntityID=n.nodeid
    where n.CustomProperties.CustomerName like '%XXXXX%'

  • Thanks i will try this query and make changes to accommodate my requirement. I will update you if i am successful.

    Thanks for your help