nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

SQL gurus, can you help me with node filters for sequential time fames?

mas589

I am trying to define a page for our NOC that dispalys nodes down by time period. They want to see at a glance what just went down (within the last 8 hours) , what went down in the last 24 hours and what has been down for over 24 hours to help them see what would have been worked by a previous shift. I am not proficient yet by any means in SQL and can not seem to accomplish this. I tried using status=2 and SystemUpTime with time periods less than and greater than parameters but they do not work consisently often showing a node that has been down for days in the under 8 hours view.

Has anyone successfully done this that can tell me what parameter to use and what the filter should look like?

Thank you for your help if you can!

Find more posts tagged with

web_console

sql_filters

Accepted answers

All comments

adatole

Do you want to see what WENT down (but might be up now), or what IS down, sorted (and possibly grouped) by duration? Just want to be clear before I start throwing SQL at you.

mas589

Just what went down and is still down. I was going to make sections on the page for:

Nodes that went down during the last 8 hours

Nodes down for more than 16 hours

Nodes down for over 24 hours

But I am certainly open to new suggestions. This came about because there may be devices down for days waiting for something. They find it hard to keep up with the outages they need to call out when there are large amounts of outages on the screen so they asked for a way to move things that were already known to a specific section so each shift works on the current outages first, does that make sense?

Thanks for looking at my question!

adatole

OK, got it. I'm going offline for the weekend, but I'll throw something your way early next week. I've got one of these.

mas589

Thanks! Any help you can give in this is greatly appreciated.

PavelSrot

Here is example how to perform this one in ReportScheduler. Example give you only list of NodeID and this you can join with other tables. You can use it in report writer

SET NOCOUNT ON;
CREATE TABLE #tmpResponseTime
(
   [NodeID] int,
   [DateTimeS] datetime,
   [DateTimeE] datetime,
   [Availability] int
   --[ChangeType] int - 0 from up to down, 1 from down to up

);

DECLARE @nodeID int
DECLARE @dateTime DateTime
DECLARE @availability int
DECLARE @lastDateTime DateTime
DECLARE @lastAvailability int

DECLARE @lastNodeID int

DECLARE responseTime_Cursor CURSOR FOR
SELECT [NodeID], [DateTime], [Availability] FROM ResponseTime_Detail R1
ORDER BY [NodeID], [DateTime]

OPEN responseTime_Cursor
FETCH NEXT FROM responseTime_Cursor INTO @nodeID, @dateTime, @availability
SET @lastAvailability = @availability
SET @lastDateTime = @dateTime

SET @lastNodeID = @nodeID
@FETCH_STATUS=0
BEGIN

IF @lastNodeID <> @nodeID

BEGIN

SET @lastDateTime = @dateTime

SET @lastAvailability = @availability

END

     IF @lastAvailability <> @availability
        BEGIN
           INSERT INTO #tmpResponseTime (NodeID, DateTimeS, DateTimeE, Availability) VALUES(@nodeID, @lastDateTime, @dateTime, @availability)
           SET @lastDateTime = @dateTime
        END

SET @lastAvailability = @availability

     SET @lastNodeID = @nodeID
     FETCH NEXT FROM responseTime_Cursor INTO @nodeID, @dateTime, @availability

END

CLOSE responseTime_Cursor
DEALLOCATE responseTime_Cursor

SELECT Av.* FROM (
SELECT NodeID, datediff(hour ,DateTimeS, DateTimeE) AS [Hours] FROM #tmpResponseTime
WHERE [Availability]=100
) AS Av
Where AV.[Hours]>16 -- you will retrieve list of nodes which was down longer than 16 hours

DROP TABLE #tmpResponseTime

mas589

Wow, no wonder I couldn't figure this one out!! Thanks so much I will set this up right away!!!!

adatole

This one may be a little simpler (no cursors, etc). What it does is join the nodes table to the alertstatus table, pulling the durationfor how lnog a device was down. The one key piece is that it uses the built-in alert called "Alert me when a device goes down" as it's key. If you use a different alert for up/down, change the title to match.

select nodes.caption, Nodes.GroupStatus, nodes.nodeid, nodes.percentloss,

a1.downtime, nodes.n_mute_reason as Addtl_Info

from Nodes

left outer join (select AlertStatus.ActiveObject as nodeid,

CONVERT(varchar, alertstatus.triggertimeoffset/60/60/24) as days,

+CONVERT(varchar, alertstatus.triggertimeoffset/60/60 % 24) as hours,

+CONVERT(varchar, alertstatus.triggertimeoffset % 60) as minutes,

CONVERT(varchar, alertstatus.triggertimeoffset/60/60/24)+' d '

+CONVERT(varchar, alertstatus.triggertimeoffset/60/60 % 24)+' h '

+CONVERT(varchar, alertstatus.triggertimeoffset % 60)+'m' as downtime,

AlertDefinitions.AlertName

from AlertStatus

join AlertDefinitions on AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

WHERE

alertdefinitions.alertname like '%alert me when a node goes down%'

OR alertdefinitions.alertname is NULL) a1

on convert(varchar, Nodes.nodeid) = a1.nodeid

WHERE

nodes.status <> 9

order by nodes.GroupStatus asc,

a1.days desc, a1.hours desc, a1.minutes desc, nodes.caption

mas589

Thanks again, went to report writer and used one of the sql reports when I enter this it gives an error of, invlaid column name 'n_mute_reason', did I miss a step?