Menu Close

Auditing on Alerts from the Data Warehouse

Do you want auditing information on how many alerts are being closed or modified by your OpsMgr users?

You can use the following queries to get this information from the data warehouse, and I have attached some reports below as well:

To get all raw alert data from the data warehouse to build reports from:

select * from Alert.vAlertResolutionState ars inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid inner join Alert.vAlert alt on ars.alertguid = alt.alertguid

To view data on all alerts modified by a specific user:

select ars.alertguid, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount from Alert.vAlertResolutionState ars inner join Alert.vAlert alt on ars.alertguid = alt.alertguid where statesetbyuserid like '%username%' order by statesetdatetime

To view a count of all alerts closed by all users:

select statesetbyuserid, count(*) as 'Number of Alerts' from Alert.vAlertResolutionState ars where resolutionstate = '255' group by statesetbyuserid order by 'Number of Alerts' DESC

In the reports I have attached, you can pick a date and a time window, and run these same basic queries

image

image

Leave a Reply

Your email address will not be published. Required fields are marked *