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

Narrow down Outage Duration in Report Writer

I am trying to narrow down the Outage Duration report to the last 24 hours. Got everything working except the date is static and has to be changed in the SQL code. Somehow I need to make the following line a dynamic time and not static:

( EventTime BETWEEN 38206 AND 38207 )

I tried looking at the other reports but it seemed to be static there as well. Here is the code that I have, any help is appreciated.

SELECT
StartTime.EventTime,
Nodes.Caption,
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 38206 AND 38207 )
ORDER BY Nodes.Caption ASC

Find more posts tagged with

Accepted answers

All comments

JMP

Case anyone needs this the following statement will work:

replace

( EventTime BETWEEN 38206 AND 38207 )

with

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

(change the 1 for how many days you want to go back)

Network_Guru

That's great JMP, I was looking for the same thing.
Now I can create my "Outage durations for all nodes over the past 7 days" report and have it e-mailed to my Manager :-D

-=Cheers=-
NG

jonchill

I've created the report and it works perfectly fine in report writer but when I try and run it from the web page it show an error INCORRECT SYNTAX NEAR THE KEYWORD 'WHERE'.

Any ideas?

Thanks JMP the code works great.

Jon

IT Infrastructure Manager
Pilgrim Hospital
UK

seanbettencourt

Has anyone ever got this to work in crystal reports? My boss would like to see a "prettier version" with graphs and such.

I'm very new to crystal reports so any help would be appreciated. Many thanks
Sean

holt_isaac

Thanks a million JMP! I've been looking for this for months!

RechardW

Ok, I am having the same problem. I want to get the last month though. I tried the following (set date to 45 days)

SELECT
Events.EventTime AS Event_Time,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
Events_EventTypes.Name AS Event_Type_Name,
DATEDIFF(Mi, Events.EventTime,
(SELECT Top 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > Events.EventTime And EndTime.EventType = 11
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

WHERE
(
(Events_EventTypes.Name = 'Interface Down')

AND

(EventTime between dateadd(day, -45, getdate()) and getdate())
AND

(

(Nodes.Caption = 'atlrt-masergy') OR
(Nodes.Caption = 'denrt-inetatt') OR
(Nodes.Caption = 'denrt-inetmasergy') OR
(Nodes.Caption = 'denrt-masergy') OR
(Nodes.Caption = 'denrt-sprint') OR
(Nodes.Caption = 'frart-masergy') OR
(Nodes.Caption = 'frart-sprint') OR
(Nodes.Caption = 'lgwrt-masergy') OR
(Nodes.Caption = 'lgwrt-sprint') OR
(Nodes.Caption = 'nbyrt-masergy') OR
(Nodes.Caption = 'nobrt-masergy') OR
(Nodes.Caption = 'nobrt-sprint') OR
(Nodes.Caption = 'nycrt-masergy') OR
(Nodes.Caption = 'nycrt-philly') OR
(Nodes.Caption = 'nycrt-sprint') OR
(Nodes.Caption = 'b61sw-closet2') OR
(Nodes.Caption = 'SJC-IAD2420') OR
(Nodes.Caption = 'sjcrt-masergy') OR
(Nodes.Caption = 'sjcrt-sprint') OR
(Nodes.Caption = 'wilrt-masergy') OR
(Nodes.Caption = 'wilrt-sprint') OR
(Nodes.Caption = 'denrt-centennial') OR
(Nodes.Caption = 'nbyrt-masergy')
)
)

ORDER BY 1 DESC

Any ideas why its not showing dates still past 45 days. I know where were outages before Oct 11th. Also, would I have to set different criteria to see last month?

R. Wagner

RechardW

Nevermind I got it. The eventtime date part was working fine. However, in the Database manager, it was set up to delete events after 30 days. Whats why I wasnt seeing them correctly

R. Wagner

Warwick

Hi, Does anyone know if there is a way to tweak the alerts to specify the amount of users impacted during a node outage? This would be very beneficial in order to generate fast reports to my boss. THANKS!!