How to get icons in a custom query?

Is your alert based on node status?

This is my query. It is based on the alerts.

SELECT 

   tolocal(AStat.TriggerTimeStamp) AS [Time of Alert], 

   HourDiff(ToLocal(AStat.TriggerTimeStamp), GETDATE()) as [Hours],

   AStat.ObjectName AS [Triggering Object],

   AStat.AlertMessage AS [Message],

      CASE 

      WHEN AStat.ObjectType = 'Node' THEN ('/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(Nodes.NodeID)) 

      WHEN AStat.ObjectType = 'Hardware Sensor' THEN ('/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(Hardware.NodeID)) 

   END AS [_LinkFor_Alert] 

FROM Orion.AlertStatus 

   AS AStat 

INNER JOIN Orion.AlertDefinitions 

   AS ADefs 

   ON AStat.AlertDefID = ADefs.AlertDefID 

LEFT OUTER JOIN Orion.Nodes 

   AS Nodes 

   ON Nodes.NodeID = AStat.ActiveObject AND AStat.ObjectType = 'Node' 

LEFT OUTER JOIN Orion.HardwareHealth.HardwareItem 

   AS Hardware 

   ON Hardware.ID = AStat.ActiveObject AND AStat.ObjectType = 'Hardware Sensor' 

WHERE 

    Acknowledged = 0

    AND (ADefs.Name = 'Alert me when a node goes down'

    OR ADefs.Name = 'NCH Hardware Notification'

    OR ADefs.Name = 'Disk Space Notice'

    OR ADefs.Name = 'Low Disk Space'

        OR ADefs.Name = 'Node Down')

dgilliam28, Some of this might inspire you, or show what is possible:

Alert Prioritising Dashboard (SWQL) with Alert Time and Acknowledge Status

Have you seen this post?

My co-worker jm_sysadmin‌ and I put this together. It gives us the icons and the links.

SELECT

'' as i,

Case

WHEN aa.AlertObjects.AlertConfigurations.Severity = '0' THEN ('Critical')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '1' THEN ('Serious')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '2' THEN ('Warning')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '3' THEN ('Information')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '4' THEN ('Notice')

End as [Severity],

toLocal(TriggeredDateTime) as [Time],

HourDiff(ToLocal(aa.TriggeredDateTime), GETDATE()) as [Hours],

TriggeredMessage as [Alert],

'/Orion/View.aspx?NetObject=AAT%3a' + ToString(Hardware.NodeID) as [_Linkfor_Alert],

aa.AlertObjects.EntityCaption as [Details],

aa.AlertObjects.EntityDetailsUrl as [_Linkfor_Details],

aa.AlertObjects.RelatedNodeCaption as [Device],

aa.AlertObjects.RelatedNodeDetailsURL as [_Linkfor_Device],

Case

WHEN aa.AlertObjects.AlertConfigurations.Severity = '0' THEN ('/Orion/images/ActiveAlerts/Critical.png')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '1' THEN ('/Orion/images/ActiveAlerts/Serious.png')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '2' THEN ('/Orion/images/ActiveAlerts/Warning.png')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '3' THEN ('/Orion/images/ActiveAlerts/InformationalAlert.png')

WHEN aa.AlertObjects.AlertConfigurations.Severity = '4' THEN ('/Orion/images/ActiveAlerts/Notice.png')

End as [_IconFor_i]

FROM

Orion.AlertActive aa

WHERE

    (isNull(aa.Acknowledged,False)) = False

    And

    (aa.AlertObjects.AlertConfigurations.Name = 'Alert me when a node goes down'

    OR aa.AlertObjects.AlertConfigurations.Name = 'Hardware Notification'

    OR aa.AlertObjects.AlertConfigurations.Name = 'Disk Space Notice'

    OR aa.AlertObjects.AlertConfigurations.Name = 'Low Disk Space'

        OR aa.AlertObjects.AlertConfigurations.Name = 'Node Down')