cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

swql question for Active Alerts on Volumes

Jump to solution

Can someone help me out with some swql please.  I am getting an error mismatch input 'Orion' expecting 'SELECT' in From Clause.  Here is the actual script I am using:

Select n.Node.Capture, n.Node.StatLED, n.Node.PRIMARY_ROLE, AlertDefinitions.Name, AlertStatus.AlertDefID, AlertHistory.Description, ALERTSTATUS.TRIGGERTIMESTAMP, Volumes.DetailsUrl, Volumes.StatusLED, Volumes.VolumeSpaceAvailable, Volumes.VolumeSize, Volumes.VolumePercentUsed

From (Orion.AlertDefinitions INNER Join alertstatus on (Orion.AlertDefinitions.alertdefid = AlertStatus.AlertDefID) inner join volumes on (Orion.Volumes.Fullname = AlertStatus.objectname) inner join nodes on (Orion.Volumes.nodeid = nodes.nodeid) inner join AlertHistory on (Orion.AlertHistory.TimeStamp = ALERTSTATUS.TRIGGERTIMESTAMP) inner join AlertHistory on (Orion.AlertHistory.DisplayName = ALERTSTATUS.OBJECTNAME))

Where alertdefinitions.objecttype = 'volume'

Order by Node.Primary_Role, node.Capture, Node.StatLED, AlertDefinitions.Name, ALERTSTATUS.OBJECTNAME, ALERTSTATUS.TRIGGERTIMESTAMP, Volumes.DetailsUrl, round((Volumes.VolumeSpaceAvailable / 1024 / 1024  ),1), Volumes.VolumetypeIcon, Volumes.VolumePercentUsed, Volumes.StatusLED

Labels (2)
Tags (1)
0 Kudos
1 Solution

There are a lot of syntax errors in this and the alertdefinitions table is not used anymore, so I'll just paste the example of my custom swql alert widget with a filter for just volumes objects.  Feel free to adjust it to whatever your end goal is.

SELECT

o.AlertConfigurations.Name AS [ALERT NAME]

,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]

,CASE

WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'

WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'

WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'

WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'

WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'

END AS [_iconfor_ALERT NAME]

,o.EntityCaption AS [ALERT OBJECT]

,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

,case

WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'

When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

End as [RELATED NODE]

,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]

,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]

-- ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

--,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_ALERT OBJECT]

,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]

,CASE

when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')

when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')

else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')

end as [Time Active]

,aa.AcknowledgedBy

,ah.Message as [Note]

From Orion.AlertActive aa

join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid

LEFT join Orion.Nodes p on p.nodeid=relatednodeid

left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)

where o.EntityType like '%volume%'

ORDER by o.AlertActive.TriggeredDateTime DESC

- Marc Netterfield, Github

View solution in original post

3 Replies

There are a lot of syntax errors in this and the alertdefinitions table is not used anymore, so I'll just paste the example of my custom swql alert widget with a filter for just volumes objects.  Feel free to adjust it to whatever your end goal is.

SELECT

o.AlertConfigurations.Name AS [ALERT NAME]

,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]

,CASE

WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'

WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'

WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'

WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'

WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'

END AS [_iconfor_ALERT NAME]

,o.EntityCaption AS [ALERT OBJECT]

,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

,case

WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'

When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

End as [RELATED NODE]

,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]

,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]

-- ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

--,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_ALERT OBJECT]

,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]

,CASE

when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')

when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')

else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')

end as [Time Active]

,aa.AcknowledgedBy

,ah.Message as [Note]

From Orion.AlertActive aa

join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid

LEFT join Orion.Nodes p on p.nodeid=relatednodeid

left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)

where o.EntityType like '%volume%'

ORDER by o.AlertActive.TriggeredDateTime DESC

- Marc Netterfield, Github

View solution in original post

Can you also use this same type of query to identify all active alerts on a specific network device?

0 Kudos

Thank you so much for the help I would have never gotten the sql to work myself I see.

0 Kudos