I wanted a high level view of availability of network, system, and applications. Typically I have created separate graphs or top 10 list, but because of limited real estate, I went with combining all three on one graph.
I started with a SWQL studio, but realized that when I put the script into the custom query(swql) or custom table(swql), it didn't work - even though it worked in SWQL studio.
So, I ended up using SQL and it looks pretty good. It would have been better if the chart auto-sized on the y axis.
Thanks
Amit
--SWQL
select
datetrunc('day',rt.datetime) as Day
,(select avg(rt1.Availability) as [NetAvg] FROM Orion.ResponseTime rt1
where datetrunc('day',rt.datetime) = datetrunc('day',rt1.datetime)
and rt1.node.vendor like 'cisco%') as [NetAvail]
,(select avg(rt2.Availability) as [ServAvg] FROM Orion.ResponseTime rt2
where datetrunc('day',rt.datetime) = datetrunc('day',rt2.datetime)
and rt2.node.vendor like 'window%') as [ServAvail]
,(select avg(aps.percentAvailability) as [AppAvg] FROM Orion.apm.ApplicationStatus aps
where datetrunc('day',rt.datetime) = datetrunc('day',aps.TimeStamp)
) as [AppAvail]
FROM Orion.ResponseTime rt
where daydiff(rt.DateTime,tolocal(getdate()))<= 7
group by datetrunc('day',rt.DateTime)
order by day desc
--SQL
select
dateadd(hour, datediff(hour,0,rt.datetime),0) as Day
,(select avg(rt1.Availability) FROM ResponseTime rt1
join nodes n1 on rt1.nodeid = n1.nodeid
where dateadd(hour, datediff(hour,0,rt.datetime),0) = dateadd(hour, datediff(hour,0,rt1.datetime),0)
and n1.vendor like 'cisco%') as [NetAvail]
,(select avg(rt2.Availability) FROM ResponseTime rt2
join nodes n2 on rt2.nodeid=n2.NodeID
where dateadd(hour, datediff(hour,0,rt.datetime),0) = dateadd(hour, datediff(hour,0,rt2.datetime),0)
and n2.vendor like 'window%') as [ServAvail]
,(select avg(ast.PercentAvailability) FROM APM_applicationstatus ast
where dateadd(hour, datediff(hour,0,rt.datetime),0) = dateadd(hour, datediff(hour,0,ast.timestamp),0)
) as [AppAvail]
FROM ResponseTime rt
where datediff(day,rt.DateTime,getdate())<= 7
group by dateadd(hour, datediff(hour,0,rt.datetime),0)
order by day desc
