I am trying to write a custom SQL report that includes the node, event types 1 and 5, the duration between the events, and the message. I have the node, event types, and message but need help calculating the duration between events 1 and 5.
Below is the code. Any help is much appreciated! Thank you!
Select NodeName, Event_Time, Cast(Message As nvarchar(250)) as Message
From
( SELECT Nodes.Caption AS NodeName,
Events.EventTime AS Event_Time,
Events.Message AS Message
FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType =
Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)
where DATEDIFF(d, eventtime, getdate() ) = 1 and Events.EventType in (1,5)
) As r ORDER BY 1 ASC, 2 ASC
