Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Need some SQL help

Ok all you SQL jocks, I need some help. The SQL syntax below was pulled from this site and works well to pull all events for a period. I need a derivative of it that pulls all events with the word Sprint in the message. Here is the generic syntax

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()
ORDER BY Nodes.Caption ASC

I've tried putting in in the Where statments Startime.message LIKE '%Sprint%' and several derivatives and all it does is pull up every thing and negates the duration calcs. Any ideas

Find more posts tagged with

Accepted answers

All comments

jeffnorton

Still need some help!!. Any one out there

sedmo

Try this.

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()) AND (Message like '%Sprint%')

ORDER BY Nodes.Caption ASC

jeffnorton

Ok. I finally figured it out the SQl is below:

SELECT     StartTime.EventTime, Nodes.Caption, StartTime.Message, StartTime.EventType, StartTime.NetObjectType, DATEDIFF(Mi, StartTime.EventTime,
                          (SELECT     TOP (1) EventTime
                            FROM          Events AS Endtime
                            WHERE      (EventTime > StartTime.EventTime) AND (EventType = 11) AND (NetObjectType = 'I') AND (NetworkNode = StartTime.NetworkNode)
                            ORDER BY EventTime)) AS OutageDurationInMinutes
FROM         Events AS StartTime INNER JOIN
                      Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE     (StartTime.EventType = 10) AND (StartTime.EventTime BETWEEN DATEADD(month, - 1, GETDATE()) AND GETDATE()) AND
                      (StartTime.Message LIKE '%sprint%')
ORDER BY StartTime.EventTime, Nodes.Caption

The key to this report solution is we only wanted to get events and duration for those events for router interfaces with the word sprint in their description field. So after finding the MS Visual studio would let me break down the sql into detail, I determined that the in needed to change the netobject type to be I instead of N and the I need to change Event Types from Node to Interface for up and down. Hope that explanation is clear enough. Thanks for the help