How do I get the equivalent SWQL for AlertHistoryView in SQL?

kenyamson

I need the EventTypeWord, ActiontypeID and Title.

Unfortunately, SWQL studio seems to only have the following:

I tried looking into other tables but can't find the equivalent.

The problem lies with the modern dashboard not allowing SQL query.

Thanks a lot!

Find more posts tagged with

sql

SWQL

Modern Dashboards

Accepted answers

wluther

It looks like I left out the... well, the LEFT on that JOIN.

SELECT TOP 1000
AlertHistory.AlertHistoryID
,AlertHistory.EventType
,CASE AlertHistory.EventType
	WHEN '0' then 'Triggered'
	WHEN '1' then 'Reset'
	WHEN '2' then 'Acknowledged'
	WHEN '3' then 'Note'
	WHEN '4' then 'AddedToIncident'
	WHEN '5' then 'ActionFailed'
	WHEN '6' then 'ActionSucceeded'
	WHEN '7' then 'Unacknowledge'
	WHEN '8' then 'Cleared'
	Else 'Unknown' 
END  as EventTypeWord
,AC.ActionTypeID
,AC.Title
,TimeStamp
FROM Orion.AlertHistory
LEFT JOIN Orion.Actions AS AC ON AlertHistory.ActionID = AC.ActionID
ORDER BY TimeStamp DESC

That looks much moar gooder now...

Additionally, I swapped out the CASE format to only call AlertHistory.EventType up front. Like I said, there are a bunch of ways to do it, but figured the first way might help other folks later on, who are new to this stuff (just like I still am), and thought the previous way might be easier to follow.

All comments

wluther

@kenyamson The EventTypeWord, as created in the view, is a case statement, which should be fairly straightforward to replicate, if needed.

The entirety of the view, at least to me, in my db, is below.

USE [SolarWinds]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create view [dbo].[AlertHistoryView] as
	SELECT AH.[AlertHistoryID], ACo.AlertID, AH.[TimeStamp], 
CASE AH.[EventType]
WHEN 0 then 'Triggered'
WHEN 1 then 'Reset'
WHEN 2 then 'Acknowledged'
WHEN 3 then 'Note'
WHEN 4 then 'AddedToIncident'
WHEN 5 then 'ActionFailed'
WHEN 6 then 'ActionSucceeded'
WHEN 7 then 'Unacknowledge'
WHEN 8 then 'Cleared'
Else 'Unknown' 
END  as EventTypeWord , 
ACo.[Name], ACo.[ObjectType], ACo.[Enabled] as AlertEnabled, ACo.[Frequency], 
AH.[Message], AH.[AccountID], AH.[AlertActiveID], AH.[AlertObjectID], AH.[ActionID], AC.ActionTypeID, AC.Title,
AO.RealEntityType, AO.TriggeredCount, AO.EntityNetObjectId, AO.EntityCaption, AO.EntityDetailsUrl, AO.RelatedNodeCaption, AO.RelatedNodeDetailsUrl, AO.RelatedNodeId, AO.LastTriggeredDateTime, AO.AlertNote,
ACo.[AlertRefID], ACo.[BlockUntil], ACo.[NotifyEnabled], ACo.[LastEdit], ACo.[CreatedBy], ACo.[Canned], ACo.[Category], ACo.[Severity]
  FROM [AlertHistory] AH With (Nolock)
  INNER JOIN AlertObjects AO With (Nolock) ON AH.AlertObjectID = AO.AlertObjectID
  INNER JOIN AlertConfigurations ACo With (Nolock) ON ACo.AlertID=AO.AlertID
  LEFT JOIN Actions AC With (Nolock) ON AH.ActionID = AC.ActionID

GO

When I need to figure out how something in SolarWinds works, I start digging around (usually mindlessly) in whatever I can click on in the db and folders/files. When I need to convert something from SQL to SWQL, and I'm able to find the SQL, I typically need to break each piece apart and work on it little by little (limitation of my tiny brain).

After breaking each piece apart, I can see where each of the different fields within the view come from.

FROM [AlertHistory] AH
AH.[AlertHistoryID]
,AH.[TimeStamp]
,CASE AH.[EventType]
	WHEN 0 then 'Triggered'
	WHEN 1 then 'Reset'
	WHEN 2 then 'Acknowledged'
	WHEN 3 then 'Note'
	WHEN 4 then 'AddedToIncident'
	WHEN 5 then 'ActionFailed'
	WHEN 6 then 'ActionSucceeded'
	WHEN 7 then 'Unacknowledge'
	WHEN 8 then 'Cleared'
	Else 'Unknown' 
END  as EventTypeWord
,AH.[Message]
,AH.[AccountID]
,AH.[AlertActiveID]
,AH.[AlertObjectID]
,AH.[ActionID]



INNER JOIN AlertObjects AO With (Nolock) ON AH.AlertObjectID = AO.AlertObjectID
,AO.RealEntityType
,AO.TriggeredCount
,AO.EntityNetObjectId
,AO.EntityCaption
,AO.EntityDetailsUrl
,AO.RelatedNodeCaption
,AO.RelatedNodeDetailsUrl
,AO.RelatedNodeId
,AO.LastTriggeredDateTime
,AO.AlertNote



INNER JOIN AlertConfigurations ACo With (Nolock) ON ACo.AlertID=AO.AlertID
,ACo.AlertID
,ACo.[Name]
,ACo.[ObjectType]
,ACo.[Enabled] as AlertEnabled
,ACo.[Frequency]
,ACo.[AlertRefID]
,ACo.[BlockUntil]
,ACo.[NotifyEnabled]
,ACo.[LastEdit]
,ACo.[CreatedBy]
,ACo.[Canned]
,ACo.[Category]
,ACo.[Severity]


LEFT JOIN Actions AC With (Nolock) ON AH.ActionID = AC.ActionID
,AC.ActionTypeID
,AC.Title

Right up front is the EventTypeWord, which is just a case statement to make it easier to read. There are many ways to populate that data, but a simple SWQL case statement should replicate it exactly.

SELECT TOP 1000
CASE
	WHEN AlertHistory.EventType = '0' then 'Triggered'
	WHEN AlertHistory.EventType = '1' then 'Reset'
	WHEN AlertHistory.EventType = '2' then 'Acknowledged'
	WHEN AlertHistory.EventType = '3' then 'Note'
	WHEN AlertHistory.EventType = '4' then 'AddedToIncident'
	WHEN AlertHistory.EventType = '5' then 'ActionFailed'
	WHEN AlertHistory.EventType = '6' then 'ActionSucceeded'
	WHEN AlertHistory.EventType = '7' then 'Unacknowledge'
	WHEN AlertHistory.EventType = '8' then 'Cleared'
	Else 'Unknown' 
END  as EventTypeWord
,TimeStamp
FROM Orion.AlertHistory
ORDER BY TimeStamp DESC

And for the last 2, ActionTypeID & Title, they both come from the Orion.Actions table, and you should be able to use the same join as the SQL, for the most part.

SELECT TOP 1000
CASE
	WHEN AlertHistory.EventType = '0' then 'Triggered'
	WHEN AlertHistory.EventType = '1' then 'Reset'
	WHEN AlertHistory.EventType = '2' then 'Acknowledged'
	WHEN AlertHistory.EventType = '3' then 'Note'
	WHEN AlertHistory.EventType = '4' then 'AddedToIncident'
	WHEN AlertHistory.EventType = '5' then 'ActionFailed'
	WHEN AlertHistory.EventType = '6' then 'ActionSucceeded'
	WHEN AlertHistory.EventType = '7' then 'Unacknowledge'
	WHEN AlertHistory.EventType = '8' then 'Cleared'
	Else 'Unknown' 
END  as EventTypeWord
,AC.ActionTypeID
,AC.Title
,TimeStamp
FROM Orion.AlertHistory
JOIN Orion.Actions AS AC ON AlertHistory.ActionID = AC.ActionID
ORDER BY TimeStamp DESC

I probably had a misstep somewhere in there, as usual, and it might not answer your questions 100%, but hopefully this is at least somewhat helpful and can point you in a decent direction.

Thank you,

-Will

wluther

It looks like I left out the... well, the LEFT on that JOIN.

SELECT TOP 1000
AlertHistory.AlertHistoryID
,AlertHistory.EventType
,CASE AlertHistory.EventType
	WHEN '0' then 'Triggered'
	WHEN '1' then 'Reset'
	WHEN '2' then 'Acknowledged'
	WHEN '3' then 'Note'
	WHEN '4' then 'AddedToIncident'
	WHEN '5' then 'ActionFailed'
	WHEN '6' then 'ActionSucceeded'
	WHEN '7' then 'Unacknowledge'
	WHEN '8' then 'Cleared'
	Else 'Unknown' 
END  as EventTypeWord
,AC.ActionTypeID
,AC.Title
,TimeStamp
FROM Orion.AlertHistory
LEFT JOIN Orion.Actions AS AC ON AlertHistory.ActionID = AC.ActionID
ORDER BY TimeStamp DESC

That looks much moar gooder now...

kenyamson

This really helped! Thanks a lot @wluther for the help and tips.