Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

SWQL Query on Down Event

iam trying to get node Down Time and Node Availability for Today Period. But Down Event time and Availability is showing wrong data.

My Requirement:

Date = always today's date ( start time 12 AM AND end time on which time query executed. )

Down Event = last time when down event generated.

SELECT TOP 10 n.Caption,n.Status,aggSub1._max AS Orion_Events_EventTime_max,n.CustomProperties.City AS CustomProperties_City,n.CustomProperties.ATM AS CustomProperties_ATM,aggSub2._avg AS Orion_ResponseTime_Availability_avg
FROM Orion.Nodes n
LEFT JOIN (
SELECT i.NetworkNode,i.InstanceSiteId
, MAX (i.EventTime) AS _max FROM Orion.Events i
GROUP BY i.NetworkNode,i.InstanceSiteId
) aggSub1 ON aggSub1.NetworkNode=n.NodeID AND aggSub1.InstanceSiteId=n.InstanceSiteId
LEFT JOIN (
SELECT i.NodeID,i.InstanceSiteId
, avg(i.Availability) AS _avg FROM Orion.ResponseTime i
WHERE (i.DateTime BETWEEN '2022-01-05T14:11:08.639Z' AND '2022-01-06T14:11:08.640Z') GROUP BY i.NodeID,i.InstanceSiteId
) aggSub2 ON aggSub2.NodeID=n.NodeID AND aggSub2.InstanceSiteId=n.InstanceSiteId
WHERE (Status = 2 AND n.CustomProperties.ATM LIKE 'Offsite ATM%')

ORDER BY aggSub1._max ASC

Find more posts tagged with

Accepted answers

m-milligan

Try replacing

AND StartTime.EventTime >'2022-02-24'

with

AND StartTime.EventTime > DATETRUNC('day', GETDATE())

That way you don't have to change the date in the script every day.

KMSigma.SWI

Or...

AND StartTime.EventTime >= GETDATE() - 1 --For the Last 24 hours, not just since midnight.

All comments

m-milligan

The query, as written, finds the last event (usually recovery), not the last Down event. You need this clause:

WHERE i.EventType = (Select e.EventType from Orion.EventTypes e where e.Name = 'Node Down')

This version specifically finds the last Node Down event:

SELECT TOP 10 n.Caption,n.Status,aggSub1._max AS Orion_Events_EventTime_max,n.CustomProperties.City AS CustomProperties_City,n.CustomProperties.ATM AS CustomProperties_ATM,aggSub2._avg AS Orion_ResponseTime_Availability_avg
FROM Orion.Nodes n
LEFT JOIN (
SELECT i.NetworkNode,i.InstanceSiteId
, MAX (i.EventTime) AS _max FROM Orion.Events i
WHERE i.EventType = (Select e.EventType from Orion.EventTypes e where e.Name = 'Node Down')
GROUP BY i.NetworkNode,i.InstanceSiteId
) aggSub1 ON aggSub1.NetworkNode=n.NodeID AND aggSub1.InstanceSiteId=n.InstanceSiteId
LEFT JOIN (
SELECT i.NodeID,i.InstanceSiteId
, avg(i.Availability) AS _avg FROM Orion.ResponseTime i
WHERE (i.DateTime BETWEEN '2022-01-05T14:11:08.639Z' AND '2022-01-06T14:11:08.640Z') GROUP BY i.NodeID,i.InstanceSiteId
) aggSub2 ON aggSub2.NodeID=n.NodeID AND aggSub2.InstanceSiteId=n.InstanceSiteId
WHERE (Status = 2 AND n.CustomProperties.ATM LIKE 'Offsite ATM%')
ORDER BY aggSub1._max ASC

Do you want to find the last Node Down event if it occurs outside of the current day?

makecrazy

thanks for detail reply,

need changes in date query as ( Date = always today's date ( start time 12 AM AND end time on which time query executed. )

Down Event = last time when down event generated.

Do you want to find the last Node Down event if it occurs outside of the current day? YES

m-milligan

You can use SWQL date functions to create the start and end dates for the BETWEEN() function. This will limit the range to events between 00:00:00.000 today and 00:00:00.000 tomorrow:

WHERE (i.DateTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(1, GETDATE())))

This query does what you need:

SELECT TOP 10 n.Caption,n.Status,aggSub1._max AS Orion_Events_EventTime_max,n.CustomProperties.City AS CustomProperties_City,n.CustomProperties.ATM AS CustomProperties_ATM,aggSub2._avg AS Orion_ResponseTime_Availability_avg
FROM Orion.Nodes n
LEFT JOIN (
SELECT i.NetworkNode,i.InstanceSiteId
, MAX (i.EventTime) AS _max FROM Orion.Events i
WHERE i.EventType = (Select e.EventType from Orion.EventTypes e where e.Name = 'Node Down')
GROUP BY i.NetworkNode,i.InstanceSiteId
) aggSub1 ON aggSub1.NetworkNode=n.NodeID AND aggSub1.InstanceSiteId=n.InstanceSiteId
LEFT JOIN (
SELECT i.NodeID,i.InstanceSiteId
, avg(i.Availability) AS _avg FROM Orion.ResponseTime i
WHERE (i.DateTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(1, GETDATE())))
GROUP BY i.NodeID,i.InstanceSiteId
) aggSub2 ON aggSub2.NodeID=n.NodeID AND aggSub2.InstanceSiteId=n.InstanceSiteId
WHERE (Status = 2 AND n.CustomProperties.ATM LIKE 'Offsite ATM%')
ORDER BY aggSub1._max ASC

CourtesyIT

I have removed the Custom Properties from this code and it still not work.

Do you have a sample of the output?

I was looking for source code that would give me the following columns:

Nodes IP Address DownTime UpTime MinutesDown.

Thanks,

Eric