Similar to the below, but for the alerting section, like active alert, which application, server, node triggering more alert like that.
Do we have any swql query for that?
Similar to the below, but for the alerting section, like active alert, which application, server, node triggering more alert like that.
Do we have any swql query for that?
SELECT aa.AlertActiveID, CASE WHEN ao.AlertConfigurations.Severity = 2 then '<b><p style="color:red;font-size:16px">Critical</p></b>' WHEN ao.AlertConfigurations.Severity = 3 then '<b><p style="color:orangered;font-size:16px">Serious</p></b>' WHEN ao.AlertConfigurations.Severity = 1 then '<b><p style="color:Orange;font-size:16px">Warning</p></b>' WHEN ao.AlertConfigurations.Severity = 0 then '<p style="font-size:16px">Informational</p>' WHEN ao.AlertConfigurations.Severity = 4 then '<p style="font-size:16px">Notice</p>' ELSE concat(ao.AlertConfigurations.Severity, '') END AS [Severity] , CONCAT('<p style="font-size:16px"><a href="/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:', a.AlertObjectID, '">' , ao.AlertConfigurations.DisplayName, '</a></p>') AS Alert , a.triggertimestamp , a.TriggerCount AS [Recent Alert Count] , CASE WHEN Minutediff(a.triggertimestamp,GETUTCDATE()) BETWEEN 0 AND 119 THEN concat('<p style="font-size:16px">',Minutediff(a.triggertimestamp,GETUTCDATE()), ' Minutes Ago</p>') WHEN HOURDIFF(a.TriggerTimeStamp,GETUTCDATE()) BETWEEN 2 AND 48 THEN concat('<p style="font-size:16px">',HOURDIFF(a.triggertimestamp,GETUTCDATE()), ' Hours Ago</p>') ELSE concat('<p style="font-size:16px">',DAYDIFF(a.triggertimestamp,GETUTCDATE()), ' Days Ago</p>') -- ELSE concat('<p style="font-size:16px">',a.TriggerTimeStamp,'</p>') END AS [Trigger Time] , concat('<p style="font-size:16px">',ao.AlertNote,'</p>') as [Ticket] , concat('<p style="font-size:16px">',ao.AlertActive.AcknowledgedBy,'</p>') as [Acknoledged By] , concat('<p style="font-size:16px">',ao.AlertActive.AcknowledgedDateTime,'</p>') as [Acknoledged Time] , CASE WHEN ao.RelatedNodeCaption IS NULL then CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a></p>') WHEN ao.EntityCaption = ao.RelatedNodeCaption then CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a></p>') ELSE CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a> on ','<a href="', ao.RelatedNodeDetailsUrl, '">', ao.RelatedNodeCaption,'</a></p>') END AS [On] FROM Orion.AlertActive aa LEFT JOIN Orion.AlertStatus a on aa.AlertObjectID = a.AlertObjectID LEFT JOIN Orion.AlertObjects ao on a.AlertObjectID = ao.AlertObjectID
Is this tweak on the SolarWinds Active Alert Dashboard , adam.beedell ?
Probably, cant remember where I got it from and revised several bits. There's a couple columns there no-one needs but hey I had it to hand
Everyone else, if 2 people recommend a page, and one of them is KMSigma.SWI use his one first
I wanted to share what I have on my alerting table. So one of the issues I ran into, is we use custom properties pretty heavily for our alerts. Basically all of our emails are tied to a Email custom property for the object type. When trying to create a dashboard for the teams, this became a bit of a struggle since I didn't really have a good way to filter. I forget what was original code and what was lifted or modified from other queries and dashboards. I believe the active time came from one dashboard, and I want to say I used the link KMSigma.SWI referenced for the base of the table layout.
To make something usable for our end, I ended up doing a subquery with various Cases to populate back the custom properties. This then allowed me to easily filter in my where clause for the teams, and provide a clean-ish select statement at the top. Anything in the [CP] table is the subquery which gets nasty. The subquery would need to be edited to line up with your environment. The main things I pulled over for ours is a Responsible Team property and an Email address field. Each object (Node, Volume, Datastore, Application (and component), Transaction (and step)) has a corresponding custom property for this.
When I build out the modern dashboard widget, I have:
An Alert name which hyperlinks to the alert
Severity
Object (Node, Volume, Virtual Machine, etc....)
Triggering Entity (Name, Status, Hyperlink)
Related Node (Name, Status, Hyperlink)
Email
Responsible Team
Trigger Time
Time Active
Acknowledge by
SELECT [AA].AlertObjects.AlertConfigurations.Name , '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString([AA].AlertObjectID) As AlertDetailURL --Build Out URL/URI , [AA].AlertObjects.AlertConfigurations.Severity , [AA].AlertObjects.EntityCaption , [AA].AlertObjects.EntityDetailsUrl , [CP].EntityStatus , Case [AA].AlertObjects.EntityType When 'Orion.Nodes' then '[Self]' Else [AA].AlertObjects.RelatedNodeCaption End As RelatedNodeCaption , [AA].AlertObjects.RelatedNodeDetailsUrl , [AA].AlertObjects.Node.Status as RelatedNodeStatus , ToLocal([AA].TriggeredDateTime) As TriggerTime , CASE WHEN MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE()) > 1440 THEN (TOSTRING(ROUND(MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days') WHEN MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE()) > 60 THEN (TOSTRING(ROUND(MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours') ELSE (TOSTRING(MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE())) + ' Minutes') END AS TimeActive , [AA].Acknowledged , [AA].AcknowledgedBy , [CP].EntityObj , [CP].Email , [CP].ResponsibleTeam From Orion.AlertActive [AA] Left Outer Join ( SELECT [AO].AlertObjectID , Case [AO].EntityType --Sets a clean value for alert object type When 'Orion.Nodes' then 'Node' When 'Orion.Volumes' then 'Volume' When 'Orion.APM.Component' Then 'Component' When 'Orion.APM.Application' Then 'Application' When 'Orion.VIM.Datastores' then 'Datastore' When 'Orion.VIM.VirtualMachines' then 'Virtual Machine' When 'Orion.SEUM.TransactionSteps' then 'WPM - Step' When 'Orion.SEUM.Transaction' Then 'WPM - Transaction' When 'Orion.Engines' Then 'Orion Server' Else [AO].EntityType --Entity type not accounted for End As EntityObj , Case [AO].EntityType --Retrieves status of Object When 'Orion.Nodes' then [AO].Node.Status When 'Orion.Volumes' then [V].Status When 'Orion.APM.Component' Then [C].Status When 'Orion.APM.Application' Then [App].Status When 'Orion.VIM.Datastores' then [DS].Status When 'Orion.VIM.VirtualMachines' then [VM].Status When 'Orion.SEUM.TransactionSteps' then [TS].Status When 'Orion.SEUM.Transaction' Then [T].Status When 'Orion.Engines' Then [SW].Status Else 0 --Entity type not accounted for End As EntityStatus , Case [AO].EntityType When 'Orion.Nodes' Then [AO].Node.CustomProperties.Email When 'Orion.Volumes' then [V].CustomProperties.VEmail When 'Orion.APM.Application' then [App].CustomProperties.AEmail When 'Orion.APM.APplication' then [C].Application.CustomProperties.AEmail When 'Orion.VIM.VirtualMachines' then [AO].Node.CustomProperties.Email When 'Orion.VIM.Datastores' then [DS].CustomProperties.DSEmail When 'Orion.SEUM.Transactions' then [T].CustomProperties.TEmail When 'Orion.SEUM.TransactionSteps' then [TS].Transaction.CustomProperties.TEmail When 'Orion.Engines' then [SW].CustomProperties.Email Else Concat('N/A - ',[AO].EntityType) End As Email , Case [AO].EntityType When 'Orion.Nodes' Then [AO].Node.CustomProperties.ResponsibleTeam When 'Orion.Volumes' then [V].CustomProperties.VResponsibleTeam When 'Orion.APM.Application' then [App].CustomProperties.AResponsibleTeam When 'Orion.APM.Component' then [C].Application.CustomProperties.AResponsibleTeam When 'Orion.VIM.VirtualMachines' then [AO].Node.CustomProperties.ResponsibleTeam When 'Orion.VIM.Datastores' then [DS].CustomProperties.DSResponsibleTeam When 'Orion.SEUM.Transactions' then [T].CustomProperties.TResponsibleTeam When 'Orion.SEUM.TransactionSteps' then [TS].Transaction.CustomProperties.TResponsibleTeam When 'Orion.Engines' then [SW].CustomProperties.ResponsibleTeam Else Concat('N/A - ',[AO].EntityType) End As ResponsibleTeam From Orion.AlertObjects [AO] Left Outer Join Orion.Volumes [V] on [AO].EntityDetailsURL = [V].DetailsURL Left Outer Join Orion.APM.Application [App] on [AO].EntityDetailsUrl = [App].DetailsUrl Left Outer Join Orion.APM.Component [C] on [AO].EntityDetailsUrl = [C].DetailsUrl Left Outer Join Orion.VIM.VirtualMachines [VM] on [AO].EntityDetailsUrl = [VM].DetailsUrl Left Outer Join Orion.VIM.DataStores [DS] on [AO].EntityDetailsUrl = [DS].DetailsUrl Left Outer Join Orion.SEUM.Transactions [T] on [AO].EntityDetailsUrl = [T].DetailsUrl Left Outer Join Orion.SEUM.TransactionSteps [TS] on [AO].EntityDetailsUrl = [TS].DetailsUrl Left OUter Join Orion.Nodes [SW] on [AO].EntityCaption = [SW].Caption ) as [CP] on [AA].AlertObjectID = [CP].AlertObjectID Where 1=1 -- And [AA].Acknowledged is NULL -- And [AA].Acknowledged = TRUE -- And [CP].Email like '%EMAILHERE%' And 2=2
This is a thing of beauty. You should be very proud of yourself SteveK because I am duly impressed.
Curious about the reason for the WHERE 1=1
and WHERE 2=2
near the end, but otherwise...
Thank you. KMSigma.SWI feel free to use any of it. It took a bit to get the tables we used linked up, and I think I drove one of our DBAs a bit crazy trying to get the subquery sorted out. When I start doing some crazy queries like that, I like to get them involved to make sure I don't cause any issues on the backend they manage.
So on the 1=1, was a trick a previous DBA told me to use. Essentially as we played around with various where statements, it allowed me to quickly comment out the various conditions without having to move stuff around. It always seemed like i was commenting out the first thing behind where, so I was cutting/pasting, re-adding "Where" and just got tired of it. Plus it doesn't really add any time when the query runs.
The 2=2 was due to a bug I stumbled in with the modern dashboard widgets. I don't know if its still an issue, but the widgtets didn't like the last line being a comment. So this allowed me to get around that as I would comment out the where pieces for my "template" dashboards.
Yeah - multiple things in the platform don't like comments as the last line. If I remember right, it's something about preventing possible SQL injections - which I understand, but it seems to be fresh and new every time I encounter it and I'm like...
Yeah - multiple things in the platform don't like comments as the last line. If I remember right, it's something about preventing possible SQL injections - which I understand, but it seems to be fresh and new every time I encounter it and I'm like...
Glad its just not me on that one. So now the 2=2 has just become a standard in every SWQL query I write. I never know where the query may end up (Widget, one off run, report, etc...)
And I would have gone weird with it and used:
WHERE ( 3.14 > 0 ) AND ( 42 > 3.14 )
Because I'm very odd.
For some reason the following did not work:
Where 42 = 'Life, The Universe, and Everything'
I may have to report a bug on that one. I also tried 'How many roads must a man walk down' but that still came back as wrong.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 200,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.