View Single Post
  #1 (permalink)  
Old July 26th, 2007, 09:37 AM
keevo24 keevo24 is offline
Registered User
 
Join Date: Jul 2007
Location: San Jose, San Jose, Costa Rica.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query from Solarwinds Database Help.

Hi all I'm a beginner at SQL and understand the principals. We have a network monitoring database, with a reports creator that's powerful to a certain extent. However when i try this query, i get total times of the events in our network monitor. I'm looking for not total times from all nodes, but anything in the last 4 months that is over 239minutes in outage duration. Here is a sample of the query perhaps someone can point me in the right direction.

Thanks,

Kevin

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    Nodes.Location,
    StartTime.Message,
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate() AAND 'OutageDurationInMinutes' >= '239'
ORDER BY OutageDurationInMinutes ASC