Report Writer - SQL Query Help

superskippy

Any help from one of you SQL gurus would be great...

I have a report that I would like to run to show me all the down events for my routers over the past six months and the duration.  I found another report that works just fine, I just cannot figure out how to modify the date rage to give me six months worth.  I'm pretty sure it is on the eventtime line, but no matter what I change, I still cannot get it right.  Thanks in advance!!

SELECT
    StartTime.EventTime,
    Nodes.NodeId,
    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 Nodes.routerlocation = 'Stores'
AND nodes.sysname like 'rr-%' AND
eventtime between dateadd(week, -1, getdate()) and getdate()
ORDER BY Nodes.Caption DESC

sedmo

Try this.  Modify this line in your query:

eventtime between dateadd(week, -1, getdate()) and getdate()

Change it to look like this:

eventtime between dateadd(month, -6, getdate()) and getdate()