Hi,
Does anyone have any info on how to create a 'Group' SLA report via SQWL?
I found this thread, however, I'm not sure if this is the correct way. Group Availability Reports
Thank you in advance.
The problem is that the group availability is computed based on the current members of the group and then stored in the history table. You can't come back later and retroactively subset the group - it has already been summarized.
We could also just ignore the groups and compute node availability based on custom properties. Like this:
SELECT City, Department, AVG(WeightedAvailability) AS Availability
FROM (
SELECT N.NodeID, N.CustomProperties.City, N.CustomProperties.Department,
SUM(N.ResponseTimeHistory.Availability*N.ResponseTimeHistory.Weight)/SUM(N.ResponseTimeHistory.Weight) AS WeightedAvailability
FROM Orion.Nodes N
WHERE N.ResponseTimeHistory.[DateTime] > GETUTCDATE()-90
GROUP BY N.NodeID, N.CustomProperties.City, N.CustomProperties.Department
) X
GROUP BY City, Department
The inner query returns, for each node, the last-90-day availability and two custom properties. The outer query takes that and summarizes it by the two custom properties.
This will only reflect the current state of the custom properties. If nodes changed roles (added or removed from prod/dev/some application) during the last 90 days, then their availability will be aggregated into the availability for the node's current grouping, not whatever the node was previously used for. To have the group availability stats reflect the node's assignment at the time, you will have to create the prod/dev subgroups.
I have custom 'application' groups that will join some nodes that have a customproperty value=i.e.,Application-1234. Some of these nodes also have customproperty='production' and 'dev'
Now, I wanted to create an group SLA, availability report to list the application name with their group SLA.
This is possible with the web report writer, however, there is no way to link it to only show the 'production' application group only.
I was able to get the group and associate the prod flag in swql, however, I'm not sure how to add in the SLA for the group.
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'
Could you clarify what you mean by "SLA"? Also, you might find this kind of thing easier to do if you define groups for these subsets of nodes. Like have one group with filters for Application-1234 AND production and another group for Application-1234 AND dev. Then your group report would only need to select the right set of groups - not also filter a particular subset of nodes within a group. If you need to create a large number of groups, it might be worth scripting it.
I mean 'Availability' of the group, needing c.Container.ContainerStatus.PercentAvailability to average of 3 month, at the same time, to flag the members of that container to only report on a customproperty='production' flag.
I hope that make sense.. been scouring all of thwack, but I can't find any resource, hope you can save the day again TDanner!
I also understand what you are saying.. to create new group querys to tag the Prod and dev.. however, I already have 300+ group and counting.. meaning that if I create a new group, I will need to create an additional group for prod/dev/test/etc...
That's true, but with the right script that can be a very quick operation - write a script that checks whether all of the prod/dev subgroups that are supposed to exist do or not and creates any that are missing. You could even schedule that script to run regularly and not have to think about it any more.
