Query Alert Captions that don't match real Entity Captions

Note this is a SQL query (not SWQL).

This query will show alert captions that do not match their true entity captions. This is a continuing problem within many Orion environments where nodes get updated and they're left with stale alert captions that don't match. This can help you clean that mess up.

SELECT
	 sub1.RealCaption
	,sub1.AlertCaption
FROM ( SELECT CASE
	 WHEN a.EntityType = 'Orion.Nodes'
		THEN n.Caption
	WHEN a.EntityType = 'Orion.NPM.Interfaces'
		THEN i.Caption
	WHEN a.EntityType = 'Orion.Volumes'
		THEN v.Caption
	WHEN a.EntityType = 'Orion.APM.Application'
		THEN app.Name
	WHEN a.EntityType = 'Orion.APM.Component'
		THEN com.Name
	WHEN a.EntityType = 'Orion.APIPoller.ApiPoller'
		THEN api.Name
	WHEN a.EntityType = 'Orion.SEUM.Transactions'
		THEN t.Name
	WHEN a.EntityType = 'Orion.SEUM.TransactionSteps'
		THEN NULL -- need to join records for name here?
	WHEN a.EntityType = 'Orion.Groups'
		THEN c.Name
	WHEN a.EntityType = 'Orion.Engines'
		THEN e.ServerName
	WHEN a.EntityType = 'Orion.F5.LTM.Server'
		THEN f5s.Name
	WHEN a.EntityType = 'Orion.F5.LTM.Pool'
		THEN NULL -- need to join other records here?
	ELSE 'Error'
	END AS [RealCaption]
	,a.EntityCaption AS [AlertCaption]
FROM SolarWindsOrion.dbo.AlertObjects AS a
LEFT JOIN SolarWindsOrion.dbo.NodesData AS n ON a.EntityType = 'Orion.Nodes' AND a.RelatedNodeId = n.NodeID
LEFT JOIN SolarWindsOrion.dbo.Interfaces AS i ON a.EntityType = 'Orion.NPM.Interfaces' AND a.RelatedNodeId = i.InterfaceID
LEFT JOIN SolarWindsOrion.dbo.Volumes AS v ON a.EntityType = 'Orion.Volumes' AND a.RelatedNodeId = v.VolumeID
LEFT JOIN SolarWindsOrion.dbo.SEUM_Transactions AS t ON a.EntityType = 'Orion.SEUM.Transactions' AND a.EntityNetObjectId = CONCAT('T:', t.TransactionId)
LEFT JOIN SolarWindsOrion.dbo.SEUM_TransactionSteps AS ts ON a.EntityType = 'Orion.SEUM.TransactionSteps' AND a.EntityNetObjectId = CONCAT('T:', ts.TransactionStepId)
LEFT JOIN SolarWindsOrion.dbo.Containers AS c ON a.EntityType = 'Orion.Groups' AND a.EntityNetObjectId = CONCAT('C:', c.ContainerID)
LEFT JOIN SolarWindsOrion.dbo.Engines AS e ON a.EntityType = 'Orion.Engines' AND a.EntityNetObjectId = CONCAT(':', e.EngineID)
LEFT JOIN SolarWindsOrion.dbo.APM_Application AS app ON a.EntityType = 'Orion.APM.Application' AND a.EntityNetObjectId = CONCAT('AA:', app.ID)
LEFT JOIN SolarWindsOrion.dbo.APM_Component AS com ON a.EntityType = 'Orion.APM.Component' AND a.EntityNetObjectId = CONCAT('AM:', com.ID)
LEFT JOIN SolarWindsOrion.dbo.F5_LTM_Server AS f5s ON a.EntityType = 'Orion.F5.LTM.Server' AND a.EntityNetObjectId = CONCAT('F5S:', f5s.ServerID)
LEFT JOIN SolarWindsOrion.dbo.APIPoller_ApiPoller AS api ON a.EntityType = 'Orion.APIPoller.ApiPoller' AND a.EntityNetObjectId = CONCAT('OAPI:', api.Id)
--LEFT JOIN SolarWindsOrion.dbo.F5_LTM_Server AS f5p ON a.EntityType = 'Orion.F5.LTM.Pool' AND a.EntityNetObjectId = CONCAT('F5P:', f5p.ServerID)  
) AS sub1
WHERE sub1.RealCaption != sub1.AlertCaption AND sub1.RealCaption != 'Error'

Anonymous