Alert Table Including Latest alert notes

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-relationship)

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

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!

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.

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.

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

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

But when I add this into the table 

Then I only get these in my table

Any ideas what may be going wrong?

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.

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).

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.

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.

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.