| | DOWN EVENT | UP EVENT | MINUTES |
|---|
| 468567 - customer1- site 1 - IOC - FW |
| | Feb 26 2024 3:32PM | Feb 26 2024 3:38PM | 6 |
| | Feb 21 2024 1:45AM | Feb 21 2024 1:47AM | 2 |
| | Feb 16 2024 1:34AM | Feb 16 2024 2:32AM | 58 |
| | Feb 14 2024 2:41PM | Feb 14 2024 2:57PM | 16 |
| | Jan 18 2024 3:01PM | Jan 18 2024 3:03PM | 2 |
| | Jan 17 2024 2:54PM | Jan 17 2024 2:56PM | 2 |
| 469092 - customer1 - site 2- IOC - FW |
| | Feb 21 2024 5:52AM | Feb 21 2024 5:57AM | 5 |
| | Feb 2 2024 2:34AM | Feb 2 2024 3:55AM | 81 |
| 469091 - customer 1 - site 3 - IOW |
| | Feb 17 2024 2:58PM | Feb 17 2024 3:18PM | 20 |
| 469091 - customer 1 - site 4 - Switch |
| | Feb 15 2024 6:52PM | Still Down | 20327 |
| | Feb 15 2024 11:00AM | Feb 15 2024 3:13PM | 253 |
| | Feb 14 2024 7:07PM | Feb 15 2024 3:14AM | 487 |
| | Feb 7 2024 3:24AM | Feb 7 2024 10:46AM | 442 |
| | Feb 6 2024 5:45PM | Feb 6 2024 6:58PM | 73 |
| | | |
| | | |
|
| | | |
| | | |
| | | |
|
| | | |
| | | |
|
| | | |
| | | |
| | | |
Here is the code so far..... but I want to have a report by month, then by site, then by node with interfaces associated, and that nodes availability for that month
content 1 - Monthly downtime
select n.caption as [Device]
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
, n.DetailsUrl as [_linkfor_Device]
, isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event]
, isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event]
, isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETUTCDATE())),99999) as Minutes
from orion.nodes n
left join (SELECT
-- Device nodeid used for our join
StartTime.Nodes.NodeID
-- Down Event time stamp in local time zone
,ToLocal(StartTime.EventTime) AS [Down Event]
-- Up Event time stamp in local time zone
,(SELECT TOP 1
ToLocal(EventTime) AS [EventTime]
FROM Orion.Events AS [EndTime]
-- picks the first up event that is newer than the down event for this node
WHERE EndTime.EventTime >= StartTime.EventTime
-- EventType 5 is a node up
AND EndTime.EventType = 5
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime
) AS [Up Event]
-- This is the table we are querying
FROM Orion.Events StartTime
-- EventType 1 is a node down
WHERE StartTime.EventType = 1
) t2 on n.NodeID = t2.nodeid
-- this is how I catch nodes that are down but have aged out of the events table
where (n.status = 2 or t2.nodeid is not null) AND (n.Caption like '%customer1%')
-- If you want to filter the results to only show outages of a minimum duration uncomment the below line
--and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) > 60
-- if you want to use this query in a search box of the Custom Query resource uncomment the below line
--and n.Caption like '%${SEARCH_STRING}%'
order by t2.[down event] desc
Content 2 Monthly Group Availability
SELECT c.Name,
c.DetailsUrl,
january.Availability AS January,
february.Availability AS February,
march.Availability AS March,
april.Availability AS April,
may.Availability AS May,
june.Availability AS June,
july.Availability AS July,
august.Availability AS August,
september.Availability AS September,
october.Availability AS October,
november.Availability AS November,
december.Availability AS December
FROM Orion.Container c
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 1 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS january ON c.ContainerID = january.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 2 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS february ON c.ContainerID = february.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 3 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS march ON c.ContainerID = march.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 4 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS april ON c.ContainerID = april.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 5 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS may ON c.ContainerID = may.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 6 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS june ON c.ContainerID = june.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 7 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS july ON c.ContainerID = july.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 8 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS august ON c.ContainerID = august.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 9 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS september ON c.ContainerID = september.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 10 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS october ON c.ContainerID = october.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 11 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS november ON c.ContainerID = november.ContainerID
LEFT JOIN (SELECT ContainerID, ROUND((SUM(PercentAvailability*Weight*1.0)/SUM(Weight)),3) AS Availability FROM Orion.ContainerStatus WHERE MONTH(DateTime) = 12 AND YEAR(DateTime) = 2024 GROUP BY ContainerID) AS december ON c.ContainerID = december.ContainerID
WHERE c.Name NOT LIKE '!Availability%' AND (c.Name LIKE '%customer1%')
