Report on Node Status (Up and Down) for a particular server

Question

Hi

I'm wanting to run a report that gives me event history for node status of a particular server.  I'm wanting this report to tell me the date and time the node went down, and when it came back up again.  This report needs to also be filtered to only reflect business hours/days.

I've tried in report writer to generate such a report but I'm having no success at all.  I can get a simple average availability report but I can't get the details I need above.

Any assistance would be greatly appreciated.

Thanks

PavelSrot · Answer

Here is how it would look like to limit it to 7am to 7pm, Monday to Friday SELECT E.NetObjectID, E.[EventTime], N.Caption ,E.[EventType] FROM [dbo].[Events] E INNER JOIN [dbo].[EventTypes] ET ON [ET].[EventType]=[E].[EventType] INNER JOIN [dbo].[Nodes] N ON N.NodeID=E.[NetObjectID] WHERE E.[NetObjectType]=N'N' AND (ET.[EventType]=1 -- Node Down OR ET.[EventType]=5 -- Node Up ) AND ((DATEDIFF(dd,'17530101', E.[EventTime])) %7) >=0 AND ((DATEDIFF(dd,'17530101', E.[EventTime])) %7) <=4 -- only working days AND DATEPART(hour,E.[EventTime])>=7 AND DATEPART(hour,E.[EventTime])<=19 ORDER BY E.[EventTime]

pirro · Answer

Thanks for that.  That is very close to what I'm after.  How do I lock down the query to be 7am to 7pm Monday to Friday?  Sorry I'm no good with SQL.

PavelSrot · Answer

Hi,

What about try following sql query which you will enter in Orion Report Writer on the tab SQL when you will create Advanced SQL report.

Here is query:

SELECT E.NetObjectID, E.[EventTime], N.Caption

,E.[EventType]

FROM [dbo].[Events] E

INNER JOIN [dbo].[EventTypes] ET ON [ET].[EventType]=[E].[EventType]

INNER JOIN [dbo].[Nodes] N ON N.NodeID=E.[NetObjectID]

WHERE E.[NetObjectType]=N'N'

AND (ET.[EventType]=1 -- Node Down

OR ET.[EventType]=5 -- Node Up

)

AND ((DATEDIFF(dd,'17530101', E.[EventTime])) %7) >=0 AND ((DATEDIFF(dd,'17530101', E.[EventTime])) %7) <=4 -- only working days

ORDER BY E.[EventTime]