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 !
[SquareFixedAdInParagraph]
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
[SquareFixedAdInParagraph]
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 !