Availability of Network, Systems, Applications

Version 1

    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.








    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




    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