SQL Report Help

jkennedy

I am trying to create a report for any of my routers that have been down for greater than 15 minutes. Since Solarwinds stores availability as a percentage and I need to know the date and time that the device was down, I think the best way is to create the report off of an alert that matches my criteria. I've gotten this far but need help taking the last few steps.

Select AlertLog.MsgID as 'Incident ID',AlertLog.LogDateTime, AlertLog.ObjectID,AlertLog.ObjectName,AlertLog.Message
From AlertLog,Nodes
WHERE AlertLog.AlertDefID = '{DB15AAC2-3950-44E2-97A9-128CBBD66A94}' and AlertLog.ActionType != 'EMail' and AlertLog.ObjectType = 'Node' and (Nodes.Type = 'Router' and AlertLog.ObjectID = Nodes.NodeID)
order by AlertLog.MsgID 

 

I need to take the results of this query and calculate the elapsed time between each "Alert Trigger" and the "Alert Reset" with the key as the ObjectID. Any SQL experts know how this could be done or a better way to get the same information out of Orion?

Network_Guru

 Have a look at this post, it should get you close to what you want.

With some extra filters, you should be able to get only outage durations greater than 15 minutes.