SWQL: Group SLA with custom property filters

Hello, thought I post this on a new thread, since the last one was not as clear.

I have over 300 groups, (Ex.1) i.e. one of the group below, with member nodes.

Now, I want create a report to list out the group, with their 'Availability' (Ex.2),

(Ex.1)

(Ex.2)

I created the report via the web report writer, however, this will show for 'all nodes' being the member of the group.

I have an issue, is that I need to filter out to only specific nodes that has certain customproperty tags, i.e. = 'production'

I know that I can re-create new group to query only nodes with the custom properties I need, however, it would not be efficient, as I would need to create a new group just to filter out certain nodes.

I know this could be possible via a SWQL query, however, I'm having difficulties finding any resource for this. Can anyone help steer me in the right course, or have a sample of a query?

Thank you!

Find more posts tagged with

swql query

group availability

containers

group sla

monthly availability

Accepted answers

zackm

Here you go:

SELECT TOP 10
c.Name AS [Container_Name]
,n.Caption AS [Member_Name]
,n.CustomProperties.OS_Environment
,SUM( n.ResponseTimeHistory.Availability * n.ResponseTimeHistory.Weight) / SUM( n.ResponseTimeHistory.Weight ) AS [Weighted_Availability]
FROM Orion.Nodes n
JOIN Orion.Container c ON c.MemberSnapshots.EntityID = n.NodeID
WHERE n.CustomProperties.os_flavor IS NOT NULL
AND c.MemberSnapshots.EntityType = 'Orion.Nodes'
AND n.ResponseTimeHistory.[DateTime] > GETUTCDATE()-1
GROUP BY c.Name, n.Caption, n.CustomProperties.OS_Environment

Reminder that SWQL entities generally don't have to be joined like in SQL. There's almost always a linked object available.

All comments

tdanner

If I understand correctly, you are reporting on the availability of each node in a group, not the availability of the group as a whole. If that is correct, then since your nodes are identified by custom properties, you don't need the groups at all.

A query like this will give you last-30-days availability for nodes with a particular custom property:

SELECT N.Caption, SUM(N.ResponseTimeHistory.Availability*N.ResponseTimeHistory.Weight)/SUM(N.ResponseTimeHistory.Weight) AS WeightedAvailability

FROM Orion.Nodes N

WHERE N.CustomProperties.City='Austin'

AND N.ResponseTimeHistory.[DateTime] > GETUTCDATE()-30

GROUP BY N.Caption

You can customize that to change the filter or look-back time.

timt

Thank TDanner for the help.

I am looking for availability as a group, however, also have the ability to filter the member nodes with nodes.customproperties.

I'm looking to add availability into this query...

select distinct parent_container_name, c.entitytype, child_container_name, cp.os_environment as OS_Environment

from (

select c.name as parent_container_name, containerid as parent_containerid, entityid as child_container_id, s.name as child_container_name

from orion.containermembersnapshots s

inner join orion.container c on c.containerid = s.containerid

where s.entitytype = 'orion.groups'

) as p

left join orion.containermembersnapshots c

on p.child_container_id = c.containerid

left join orion.nodescustomproperties cp ON cp.nodeid=c.entityid

left join orion.nodes n ON n.nodeid=c.entityid

where c.entitytype = 'orion.nodes' and cp.OS_Environment = 'production' and cp.OS_CBU = 'hma'

zackm

Here you go:

SELECT TOP 10
c.Name AS [Container_Name]
,n.Caption AS [Member_Name]
,n.CustomProperties.OS_Environment
,SUM( n.ResponseTimeHistory.Availability * n.ResponseTimeHistory.Weight) / SUM( n.ResponseTimeHistory.Weight ) AS [Weighted_Availability]
FROM Orion.Nodes n
JOIN Orion.Container c ON c.MemberSnapshots.EntityID = n.NodeID
WHERE n.CustomProperties.os_flavor IS NOT NULL
AND c.MemberSnapshots.EntityType = 'Orion.Nodes'
AND n.ResponseTimeHistory.[DateTime] > GETUTCDATE()-1
GROUP BY c.Name, n.Caption, n.CustomProperties.OS_Environment

Reminder that SWQL entities generally don't have to be joined like in SQL. There's almost always a linked object available.

timt

I never thought possible until now...

This will work wonders, being able to show the Container names, with it's member, OS, Environment, and Availability all in 1 row.

Thank you so much! zackm