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;

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

  • 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;

  • 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?

  • 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]

  • I have one more request  :) I'm using another custom widget to show agent status and want to remove the agents that show plugin errors:

    SELECT [Agent].Node.Caption AS [Name]
    , [Agent].Node.DetailsUrl AS [_LinkFor_Name]
    , CONCAT('/Orion/images/StatusIcons/Small-', [Agent].Node.StatusIcon) AS [_IconFor_Name]
    , [Agent].IP
    , [Agent].AgentStatusMessage AS [Message]
    , CASE
    WHEN [Agent].AgentStatus = 1 THEN '/Orion/AgentManagement/Images/StatusIcons/Small-Agent-Up.gif'
    ELSE '/Orion/AgentManagement/Images/StatusIcons/Small-Agent-Down.gif'
    END AS [_IconFor_Message]
    , [Agent].AgentVersion as [Version]
    FROM Orion.AgentManagement.Agent AS [Agent]
    WHERE [Agent].AgentStatus <> 1 AND [Agent].Node.Status <> 9
    ORDER BY agentstatusmessage desc

  • got it, added a "not like Plug-in error"