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

faizan_123, im not the greatest so i'm fairly certain there might be a better way, but I was able to come up with the following. Is this what you're looking for?

SELECT count(a.name) as [Total Apps], count(t1.applicationid) as [SolarWinds CP Applied], (count(a.name) - count(t1.applicationid)) as [SolarWinds CP not Applied]
FROM Orion.APM.Application a
left join (select applicationid from Orion.apm.application am where am.CustomProperties.SAM_Applications <> 'SolarWinds') as t1 on t1.applicationid = a.ApplicationID

outputs to below

faizan_123, im not the greatest so i'm fairly certain there might be a better way, but I was able to come up with the following. Is this what you're looking for?

SELECT count(a.name) as [Total Apps], count(t1.applicationid) as [SolarWinds CP Applied], (count(a.name) - count(t1.applicationid)) as [SolarWinds CP not Applied]
FROM Orion.APM.Application a
left join (select applicationid from Orion.apm.application am where am.CustomProperties.SAM_Applications <> 'SolarWinds') as t1 on t1.applicationid = a.ApplicationID

outputs to below

christopher.t.jones123 thanks for your reply.

I do not have any thing to join on, i dont know if there is a way to directly do subtraction in SWQL, if you have anything in that regard that would be really helpful

faizan_123, 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.

christopher.t.jones123 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

faizan_123, 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 t1.name) as [Problem Groups], (count(distinct n.customproperties.CustomerName) - count(t1.name)) as [Difference] 
FROM orion.nodes n 
left join (Select cont.ContainerID, cont.EntityID, cont.Container.name
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 christopher.t.jones123 i will try this query and make changes to accommodate my requirement. I will update you if i am successful.

Thanks for your help