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.

	 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'