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 on the SCOM Data Warehouse 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 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 !