Hi,
I´m looking for a report to show me how long any node is down... (only nodes in down status now).
Thanks a lot !
Contracer,
I found this post to have useful info:
If you drop the following (slightly modified) code in an Advanced SQL report it should do the trick.
SELECT
N.StatusLED as Status,N.Caption AS Device,MAX(E.EventTime) AS DownTime,
Cast(DateDiff(day,MAX(E.EventTime),getdate()) as varchar) + ' Day(s) ' + convert(char(8),dateadd(second,DateDiff(second,MAX(E.EventTime),getdate()),0),14) as Duration
FROM
Nodes N
INNER JOIN Events E ON E.NetworkNode = N.NodeID
where N.status = 2
GROUP BY
N.StatusLED,
N.Caption
I see how you did this in an advanced sql search but how can I do this slightly modified?
What I need to do is a monthly report of network "outages" for a certain group we will refer to as Vincent.
It would be helpful to create a report (monthly) that will show me which "Vincent" cisco router went offline & for how long (duration) along with the date of the outage.
Would anyone offer to assit me in how this would be sql coded please?
Thank you in advance.
Mike
Try this:
contracer,
thank you that looks really nice.
Two things though if you have the time.
#1 This precanned sql report is showing last 30 days (not a big deal but would prefer a monthly I can run at the beginning of each new month).
#2 Would you be kind enough to show me an example of how I would tweak it to only show my "Vincent" routers instead of all managed devices?
It would be hugely helpful to be able to break this down by the business unit which can be characterized by router device name.
An example of one for the Vincent group would be usrtrvinXXXX
Thank you
I´ll try create this query tomorrow in my work.
Thank you for your help. I look forward to seeing your script!
Cheers
Try this query:
Nodes.Caption,
StartTime.EventTime,
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 nodes.caption like '%vinc%'
ORDER BY Nodes.Caption ASC
good morning and thank you for the code!
It looks real good & works.
I have a couple of questions if you are willing to indulge me.
1) It is reporting on events this month as well as last month. Does this make it a "30 day" report or is there a way to tweak the code to do a "Last Month" report?
2) Is there a way to add another search parameter that would mean "any" in this portion of the query and nodes.caption like '%vinc%'"
My desire is to be able to do the above query & also have it pull back results for another node with a specific name like rtrnyny80.
3) Lastly, how do I get this saved report to import or show in the webpage list of reports so I or any other administrator can run it from the webgui?
A) I answered my own question on #3. Opening the folder where your reports are stored & pasting the custom report into this folder & it works.
I figured out how to sort it by changing ORDER BY Nodes.Caption ASC to ORDER BY StartTime.EventTime ASC on the last line of the code. Now it sorts by oldest event to newest.
Thank you very much for what you have given me. I can see a lot of use for this in monthly reporting.
Try this query (last month):
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND (nodes.caption like '%vinc%' OR nodes.caption like '%rtrnyny80%') and
eventtime between (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
--ORDER BY Nodes.Caption ASC
ORDER BY StartTime.EventTime ASC
Thank you VERY much.
This works perfectly for every variable!
I really appreciate your time & expertise.
I will use this template for several reports.
Have a great week!
How to get the report for particular custom group. Above SQL query include node caption only. For example I have created custom group xyz, abc etc. Now I want outage report for XYZ custom group.Would you please let me know how to add custom group in above query.
