Home SYSTEM CENTER T-SQL : Search for Historical Alerts and Events inside the SCOM Data Warehouse for a specific server and between dates

T-SQL : Search for Historical Alerts and Events inside the SCOM Data Warehouse for a specific server and between dates

by Cengiz Kuskaya

Description

There are several T-SQL queries available on the internet especially from Microsoft PFE Kevin Holman regarding searching for Events or Alerts on the OpsMgr Operational Database but not for the Data Warehouse database. In some troubleshooting scenarios or forensic analysis historical data (events or alerts) related to a specific server can be required. Enjoy it !

T-SQL Query

Search for Alerts inside the SCOM Data Warehouse Database for a specific server and between dates :

USE OperationsManagerDW
SELECT        Alert.vAlertParameter.ParameterValue, Alert.vAlert.AlertName, Alert.vAlert.AlertDescription,
Alert.vAlert.RaisedDateTime, Alert.vAlertDetail.DWCreatedDateTime, Alert.vAlert.RepeatCount
FROM          Alert.vAlert INNER JOIN
Alert.vAlertParameter ON Alert.vAlert.AlertGuid = Alert.vAlertParameter.AlertGuid INNER JOIN
Alert.vAlertDetail ON Alert.vAlertParameter.AlertGuid = Alert.vAlertDetail.AlertGuid
WHERE         Alert.vAlertParameter.ParameterValue like '%ServerName%' AND Alert.vAlert.RaisedDateTime BETWEEN '2017-01-20 19:48:57.323' AND '2017-02-17 19:48:57.323'
ORDER BY Alert.vAlert.RaisedDateTime DESC

Search for Events inside the SCOM Data Warehouse Database for a specific server and between dates :

USE OperationsManagerDW
SELECT        vEventLoggingComputer.ComputerName, Event.vEvent.EventDisplayNumber, Event.vEventDetail.RenderedDescription, Event.vEvent.DateTime, vEventLoggingComputer.LastReceivedDateTime
FROM          Event.vEvent INNER JOIN
Event.vEventDetail ON Event.vEvent.EventOriginId = Event.vEventDetail.EventOriginId INNER JOIN
vEventLoggingComputer ON Event.vEvent.LoggingComputerRowId = vEventLoggingComputer.EventLoggingComputerRowId
WHERE        vEventLoggingComputer.ComputerName like '%ServerName%' AND Event.vEvent.DateTime BETWEEN '2017-01-17 14:45:00.310' AND '2017-02-17 14:45:00.310'
ORDER BY Event.vEvent.DateTime DESC

 
Good luck !