Pulling availability stats

Question

Hello,

I have downloaded the SWQL studio and managed to run a number of queries successfully, allowing us to use the NPM data across our other internal systems.

* Apologies if this is not the correct place its a NPM API question

But I have got stumped on the Availability table that appears on both nodes and applications.

I have got as far as the Orion.NPM.EW.DeviceStats table/view but there seems to be a mixture of results (minute by minute, hourly and daily all combined some of which have an "Available" and "Available Percent" others are just blank).

The query I have for now is:

SELECT NodeID, Available, AvailablePercent, ObservationTimestamp

FROM Orion.NPM.EW.DeviceStats

WHERE NodeID = 1740

ORDER BY ObservationTimestamp

Essentially I just want to recreate the table, is there a better way to go about this:

Availability Statistics

PERIOD AVAILABILITY

Today 100.000 %

Yesterday 100.000 %

Last 7 Days 100.000 %

Last 30 Days 100.000 %

This Month 100.000 %

Last Month 100.000 %

This Year 100.000 %

Am I missing something obvious?

Many thanks,

Oli

oliver.grist · Accepted Answer

The final code I have ended up with is as follows, the dates being set in PHP.

This solves the issue of devices having different amounts of historic data, it is slightly slow when run across the entire dataset .

SELECT NodeID, NCP.MIST_OBJECT_ID, (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '15/11/2013 00:00:00') as AvailabilityToday,

(SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '14/11/2013 00:00:00' AND ObservationTimestamp <= '15/11/2013 00:00:00') as AvailabilityYesterday,

(SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '08/11/2013 00:00:00') as AvailabilityLast7days,

(SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '15/10/2013 00:00:00') as AvailabilityLast30days,

(SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '15/08/2013 00:00:00') as AvailabilityLast90days,

(SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '01/01/2013 00:00:00') as AvailabilityThisYear

FROM Orion.Nodes

INNER JOIN Orion.NodesCustomProperties NCP ON Nodes.NodeID = NCP.NodeID

Pulling availability stats

Top Replies