This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Reporting on Node Outage in Minutes with specified time problem?

beckyk193 over 12 years ago

Hi,

I am trying to create a report that details when a node goes down, how long it was down for for a specified time frame - between 07:30 and 21:00 for the last 7 days. I have used the following sql statement that I have found on other posts but the time doesnt seem to work.

I also only want to report on outages greater than 10 minutes - which I'm not sure how to do.

Any ideas?

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
AND EndTime.EventTime > 15
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(day, -7, getdate()) and getdate()
)
AND
(
(CONVERT(Char, EventTime, 108)> '0730') AND
(CONVERT(Char, EventTime, 108)< '2100')
)
ORDER BY Nodes.Caption ASC

0 qle over 12 years ago

I have used the following sql statement that I have found on other posts but the time doesnt seem to work.

Can you elaborate more on what you mean by "the time doesn't seem to work"?
Cancel
Vote Up 0 Vote Down

Cancel
0 beckyk193 over 12 years ago in reply to qle

When I mean the time doesnt work I mean that it shows events that have happened before 07:30 and after 21:00.
Cancel
Vote Up 0 Vote Down

Cancel
0 FormerMember over 12 years ago in reply to beckyk193

You need to put : in the time, i.e.
(CONVERT(Char, EventTime, 108)> '07:30') AND
(CONVERT(Char, EventTime, 108)< '21:00')
Cancel
Vote Up 0 Vote Down

Cancel
0 Marlbs over 12 years ago

Everytime I try to save the report, I get and error that says:
"Detected useage of a macro, which links to a nonexistent field in the report. Therefor WebURL, which uses this macro, will not be generated correctly.
Missing fields;
NodeID
I only see NodeID used one place in the code. Is this perhaps a typo?
Suggestions on how to fix?
Cancel
Vote Up 0 Vote Down

Cancel
0 Marlbs over 12 years ago in reply to Marlbs

I figured it out.
I needed delete the data in Field formatting from the Caption field under Web URL.
Cancel
Vote Up 0 Vote Down

Cancel
0 khmatar over 12 years ago in reply to FormerMember

Hello,

is there any way to change it to Hours and Minutes ie: (HH:MM) ??
Cancel
Vote Up 0 Vote Down

Cancel
0 Marlbs over 12 years ago in reply to khmatar

is there any way to change it to Hours and Minutes ie: (HH:MM) ??

I beleive that it is possible, but that is really beyond what I know how to do at this point.
Maybe someone else could suggest a way to do this?
Cancel
Vote Up 0 Vote Down

Cancel