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

Alert Table Including Latest alert notes

Jump to solution

Hey Guys,

 

So I'm in the process of creating a new Alert View where we can monitor all active alerts, similar to the 'Active Alerts' page.

In this table, I want the alert notes as a column. This was easy enough, however, if alert notes have been added, I get a new row for every variation of notes when I just want to show the latest notes associated with the alert.

I've tried a few things, but I can't seem to come up with a way that doesn't involve subqueries.

If I use any subqueries, I get the 'SWQL Studio, subqueries not supported' message

DrBeehre_0-1602638385833.png

 

 

Here is a simplified version of my queries

 

SELECT aa.AlertObjectID ,ah.AlertObjectID, ah.Message, ah.TimeStamp
, (SELECT TOP 1 Message FROM Orion.AlertHistory WHERE AlertObjectID = aa.AlertActiveID ORDER by TimeStamp DESC) 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)
ORDER by o.AlertActive.TriggeredDateTime DESC

This one has the subquery in the select and just grabs the top note for the active alert, thought maybe I might need to add the DISTINCT keyword but this just didn't run so couldn't debug

 

SELECT aa.AlertObjectID ,ah.AlertObjectID, ah.Message, ah.TimeStamp
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 ah.TimeStamp IN (
    SELECT TOP 1 TimeStamp
    FROM Orion.AlertHistory
    WHERE AlertObjectId = aa.AlertActiveID
    ORDER BY TimeStamp DESC
)
ORDER by o.AlertActive.TriggeredDateTime DESC

This one has the subquery in the WHERE section. The idea being, the timestamp for the alert history object has to equal the latest one. 

Again couldn't run so couldn't debug

Tags (1)
0 Kudos
1 Solution

This is what I've been able to come up with.  It should meet your needs, but if not, I've tried to include many, many comments.

 

 

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' -- we should never get here
		END AS [_IconFor_Alert]
	,CONCAT (
		'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'
		,[ActiveAlerts].AlertObjectID
		) AS [_LinkFor_Alert]
	-- We can connect to related tables by referencing them.  Linked tables are show in SWQL Studio with a chain icon.
	--   Here we are chaining base table (ActiveAlerts) to the AlertObjects and selecting additional fields there
	,[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]
	-- This part is some magical mystery as far as I'm concerned.  I looked at the icons on the actual All Active Alerts page and looked at how the URL was formatted
	--   The StatusIcon.ashx takes (for our purposes) four (4) parameters: Entity, EntityUri, Size (always 'small'), and Timestamp
	--   Then I just joined them all together to get the icon.
	-- The timestamp was the interesting thing because it's in epoch time, which is the number of seconds after 01/01/1970
	,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]
-- We are also chaining here to get the AlertHistory event type /though/ the first chain of AlertObjects
--  The logic is this: "[ActiveAlerts].AlertObjects.AlertHistory.<Field Name>"
--  We are querying the Active Alerts, which is connected to AlertObjects, which is connected to AlertHistory
WHERE [ActiveAlerts].AlertObjects.AlertHistory.EventType IN (2, 3)
ORDER BY [ActiveAlerts].TriggeredDateTime DESC

 

 

 

This is what it looks like running in a custom query widget in my lab environment.

KMSigma_0-1603308339250.png

The Alert is clickable and so is the Alerting Object.  Both should also support the "hover over" functionality.

Tested on Orion Platform 2020.2.1 and Orion Platform 2019.4 HF5.

"Shoot for the stars to reach the moon"

View solution in original post

10 Replies
Level 13

Hi

I probably would have tried the same way as you did first. Found out another way of solving this though. Haven't tested it enought but it runs...

SELECT
aa.AlertObjectID
,ah1.AlertObjectID
,ah1.Message
,ah1.TimeStamp
From Orion.AlertActive aa
join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
LEFT join Orion.Nodes p on p.nodeid=relatednodeid
join orion.AlertHistory AH1 ON (AH1.AlertActiveID=AA.AlertActiveID AND AH1.EventType IN (2,3))
left OUTER join orion.alerthistory ah2 on
(AA.AlertActiveID=AH2.AlertActiveID and ah1.EventType in (2,3) AND
(AH1.TimeStamp<AH2.TimeStamp OR (AH1.TimeStamp=AH2.TimeStamp AND AH1.AlertActiveID<AH2.AlertActiveID)))
WHERE AH2.AlertActiveID IS NULL

ORDER by o.AlertActive.TriggeredDateTime DESC

(Refering to this page for explanation: https://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-rel...)

Otherwise, try a SQL-query in a custom table instead of SWQL is that does not solve the case.

So while this query worked, it was a lot slower! 

Wasn't much of an issue when running in through swql studio but when running it on my dashboard, it slows everything up and takes an additional 30 seconds to run.

Going from 0.7 seconds to 30 seconds

 

Looking into a better way of doing this

0 Kudos

Yeah I was trying to avoid running a custom-sql query just because I'm trying to move over to the 'modern' Dashboard and I believe that only support SWQL.

 

That approach of double joining on the alertHistory table works a treat! Thank you for that!

 

Cheers!

0 Kudos

This is what I've been able to come up with.  It should meet your needs, but if not, I've tried to include many, many comments.

 

 

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' -- we should never get here
		END AS [_IconFor_Alert]
	,CONCAT (
		'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'
		,[ActiveAlerts].AlertObjectID
		) AS [_LinkFor_Alert]
	-- We can connect to related tables by referencing them.  Linked tables are show in SWQL Studio with a chain icon.
	--   Here we are chaining base table (ActiveAlerts) to the AlertObjects and selecting additional fields there
	,[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]
	-- This part is some magical mystery as far as I'm concerned.  I looked at the icons on the actual All Active Alerts page and looked at how the URL was formatted
	--   The StatusIcon.ashx takes (for our purposes) four (4) parameters: Entity, EntityUri, Size (always 'small'), and Timestamp
	--   Then I just joined them all together to get the icon.
	-- The timestamp was the interesting thing because it's in epoch time, which is the number of seconds after 01/01/1970
	,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]
-- We are also chaining here to get the AlertHistory event type /though/ the first chain of AlertObjects
--  The logic is this: "[ActiveAlerts].AlertObjects.AlertHistory.<Field Name>"
--  We are querying the Active Alerts, which is connected to AlertObjects, which is connected to AlertHistory
WHERE [ActiveAlerts].AlertObjects.AlertHistory.EventType IN (2, 3)
ORDER BY [ActiveAlerts].TriggeredDateTime DESC

 

 

 

This is what it looks like running in a custom query widget in my lab environment.

KMSigma_0-1603308339250.png

The Alert is clickable and so is the Alerting Object.  Both should also support the "hover over" functionality.

Tested on Orion Platform 2020.2.1 and Orion Platform 2019.4 HF5.

"Shoot for the stars to reach the moon"

View solution in original post

Sorry for the late response

This is awesome!

 

I'm still working through the query and just modifying it to work with my one, but it looks like it's exactly what I want

 

I am having one issue though with displaying the Icons.
I mainly want the severity icons to display next to the alerts but I just get a question mark?

I generate this link which looks right in the SWQL

DrBeehre_0-1603838731508.png

 

And if I follow that in the URL, I get the icon I want

 

But when I add this into the table 

DrBeehre_1-1603838805179.png

 

Then I only get these in my table

DrBeehre_2-1603838843809.png

 

Any ideas what may be going wrong?

0 Kudos
Ah, sorry. I didn't realize you were putting this into the new Modern Dashboards. I wrote it for a Custom Query Widget on a classic dashboard. The logic for icons is a little different. I will take a look when I get some time, but I'm pretty busy with THWACKcamp prep at the moment.
"Shoot for the stars to reach the moon"
0 Kudos

That's algood!

I'll do some more digging and see if I can get it going myself.

 

Otherwise, which dashboard do you reckon? I've noticed the modern one still has a way to go before it's as good as the older way of doing it, however, that's the direction Solarwinds is going in so want to stay some what up to date.

0 Kudos

Actually, the Modern dashboards make many things easier for icons and colors.  That being said, it's still new to me and I'm more comfortable working on the legacy ones.  I've also got to make the jump and plan on spending some time for that in December.

The Modern Dashboard table widgets aren't quite as flexible as the Custom Query Widget - and that's just because of development time.  If you need more functionality (like something you REALLY like from Custom Query that isn't in the table widget), then you really need to put that in as a Feature Request (if it doesn't already exist).

"Shoot for the stars to reach the moon"
0 Kudos

Oh awesome!

That's what I thought. I thought it was still being developed, I'll check the feature requests and add what I need.

I'm quite new to all this but decided to push our team into using the new dashboard and it has been a tad more limiting then I'd like but I suspected it was still being worked on! Thanks for confirming.

 

0 Kudos

Modern Dashboards as a whole are only 2 (or 3 depending how you count) releases old. They are newer, but that doesn't change the fact that there's room for improvement. That's probably the greatest thing about working with SolarWinds software - we are constantly trying to find ways to make all of it better, not just the new hotness. Today I had a call working with designers about an older feature that needed some modernizing. We're always trying to improve.

I'm my customer days I got to see many feature requests turn into actual features in a relatively short time. That's one of the reasons I decided to take a job with SolarWinds - they (and now we) will always listen to the users.

"Shoot for the stars to reach the moon"
0 Kudos