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

custom query time adjustment

I copied most of this query from thwack and made a few changes. I haven't found a way to correct how the time is displayed. It's currently showing 4 hours ahead.

SELECT DISTINCT [ActiveAlerts].TriggeredMessage AS [Alert]
,CASE [ActiveAlerts].AlertObjects.AlertConfigurations.Severity
WHEN 0
THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
WHEN 1
THEN '/Orion/images/ActiveAlerts/Warning.png'
WHEN 2
THEN '/Orion/images/ActiveAlerts/Critical.png'
WHEN 3
THEN '/Orion/images/ActiveAlerts/Serious.png'
WHEN 4
THEN '/Orion/images/ActiveAlerts/Notice.png'
ELSE '/Orion/images/StatusIcons/EmptyIcon.gif'
END AS [_IconFor_Alert]
,CONCAT (
'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'
,[ActiveAlerts].AlertObjectID
) AS [_LinkFor_Alert]
,[ActiveAlerts].AlertObjects.TriggeredCount AS [Count]
,[ActiveAlerts].TriggeredDateTime AS [Date/Time]
,[ActiveAlerts].AlertObjects.AlertNote AS [Alert Note]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN 'Unacknowledged'
ELSE CONCAT (
'By: '
,[ActiveAlerts].AcknowledgedBy
,' at '
,[ActiveAlerts].AcknowledgedDateTime
,' / Note: '
,[ActiveAlerts].AcknowledgedNote
)
END AS [Ack. Details]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN '/Orion/images/StatusIcons/Small-EmptyIcon.gif'
ELSE '/Orion/images/ActiveAlerts/Acknowliedged_icon16x16v1.png'
END AS [_IconFor_Ack. Details]
,[ActiveAlerts].AlertObjects.EntityCaption AS [Object]
,[ActiveAlerts].AlertObjects.EntityDetailsUrl AS [_LinkFor_Object]
,CONCAT (
'/Orion/StatusIcon.ashx?entity='
,[ActiveAlerts].AlertObjects.EntityType
,'&EntityUri='
,[ActiveAlerts].AlertObjects.EntityUri
,'&size=small&timestamp='
,SecondDiff('01/01/1970', [ActiveAlerts].TriggeredDateTime)
) AS [_IconFor_Object]
FROM Orion.AlertActive AS [ActiveAlerts]
WHERE [ActiveAlerts].AlertObjects.AlertConfigurations.Severity = 2
AND (
[ActiveAlerts].AlertObjects.AlertConfigurations.ObjectType LIKE 'APM%'
OR
[ActiveAlerts].AlertObjects.AlertConfigurations.ObjectType = 'Node'
)
ORDER BY [ActiveAlerts].TriggeredDateTime DESC;

Find more posts tagged with

Accepted answers

All comments

ewk.jr

i tried a few things i found online but it didn't work.

bobmarley

Try this and see if it is better

SELECT DISTINCT [ActiveAlerts].TriggeredMessage AS [Alert]
,CASE [ActiveAlerts].AlertObjects.AlertConfigurations.Severity
WHEN 0
THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
WHEN 1
THEN '/Orion/images/ActiveAlerts/Warning.png'
WHEN 2
THEN '/Orion/images/ActiveAlerts/Critical.png'
WHEN 3
THEN '/Orion/images/ActiveAlerts/Serious.png'
WHEN 4
THEN '/Orion/images/ActiveAlerts/Notice.png'
ELSE '/Orion/images/StatusIcons/EmptyIcon.gif'
END AS [_IconFor_Alert]
,CONCAT (
'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'
,[ActiveAlerts].AlertObjectID
) AS [_LinkFor_Alert]
,[ActiveAlerts].AlertObjects.TriggeredCount AS [Count]
,TOLOCAL ([ActiveAlerts].TriggeredDateTime) AS [Date/Time]
,[ActiveAlerts].AlertObjects.AlertNote AS [Alert Note]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN 'Unacknowledged'
ELSE CONCAT (
'By: '
,[ActiveAlerts].AcknowledgedBy
,' at '
,[ActiveAlerts].AcknowledgedDateTime
,' / Note: '
,[ActiveAlerts].AcknowledgedNote
)
END AS [Ack. Details]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN '/Orion/images/StatusIcons/Small-EmptyIcon.gif'
ELSE '/Orion/images/ActiveAlerts/Acknowliedged_icon16x16v1.png'
END AS [_IconFor_Ack. Details]
,[ActiveAlerts].AlertObjects.EntityCaption AS [Object]
,[ActiveAlerts].AlertObjects.EntityDetailsUrl AS [_LinkFor_Object]
,CONCAT (
'/Orion/StatusIcon.ashx?entity='
,[ActiveAlerts].AlertObjects.EntityType
,'&EntityUri='
,[ActiveAlerts].AlertObjects.EntityUri
,'&size=small&timestamp='
,SecondDiff('01/01/1970', [ActiveAlerts].TriggeredDateTime)
) AS [_IconFor_Object]
FROM Orion.AlertActive AS [ActiveAlerts]
WHERE [ActiveAlerts].AlertObjects.AlertConfigurations.Severity = 2
AND (
[ActiveAlerts].AlertObjects.AlertConfigurations.ObjectType LIKE 'APM%'
OR
[ActiveAlerts].AlertObjects.AlertConfigurations.ObjectType = 'Node'
)
ORDER BY [ActiveAlerts].TriggeredDateTime DESC;

ewk.jr

That worked, thank you so much I've been struggling with it for a little while. Any chance you know how I can add a active time/duration column?

sum_giais

You could add the first case statement to your query in the top section, and if you wanted, something similar to the second case statement to add an icon for it. Would need to change the logic for the second based on your need, and if it's going in a classic or modern dashboard.

,CASE 
	WHEN MINUTEDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) > 1440 -- More than 1 Day (1440 minutes)
		THEN (TOSTRING(ROUND(MINUTEDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) / 1440.0, 1)) + ' Days')
	WHEN MINUTEDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) > 60  -- More than 1 Hour (60 minutes)
		THEN (TOSTRING(ROUND(MINUTEDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) / 60.0, 1)) + ' Hours')
	ELSE (TOSTRING(MINUTEDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE())) + ' Minutes')
END AS [Active]

,CASE
    WHEN DayDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) > 7 -- More than 7 Day (1440 minutes)
        THEN 14
	 WHEN DayDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) < 2 -- Less than 2 Day
	 	THEN 1
    ELSE 3
END AS [ActiveIcon]