p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2005 (http://p2p.wrox.com/forumdisplay.php?f=220)
-   -   Query from Solarwinds Database Help. (http://p2p.wrox.com/showthread.php?t=60526)

keevo24 July 26th, 2007 09:37 AM

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


Peso July 27th, 2007 03:57 PM

SELECT EventTime,
        Caption,
        Location,
        Message,
        OutageDurationInMinutes
FROM (
            <your query here without last order by>
        ) AS d
WHERE OutageDurationInMinutes >= 239
ORDER BY OutageDurationInMinutes



All times are GMT -4. The time now is 10:02 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.