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
Files attached below:
