Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Auditing on Alerts from the Data Warehouse

Auditing on Alerts from the Data Warehouse

  • Comments 12
  • Likes

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:

  • PingBack from

  • This looks very good Kevin, I have been looking for a report like this, how do i import into opsmgr?

  • You can upload the reports I post for the opsDB if you create a data source... and then create a folder to upload them to:

    To import you simply upload the file to a folder.

    I document how to create reports from scratch - using a SQL query in a basic example here:

  • Thanks for the information, Have you ever tried to map the alert to which management pack raised the alert?

  • This is good may i know how can i get a open alerts pending for closing with respective of operations group instead of users. Please help me on this query.

  • If you can explain that again in English - I will do my best.  :-)

  • I want to find the open alerts based on the operator groups. i.e we have operators group and users are assigned in the groups. the alerts are assigned to users now i want to find the count of assigned alerts based on operators group instead of users as given above.

    please let me know if you are not clear i can give example.

  • operator group we have


    AV opgroup1 - user1,user-2,user-3

    AD opgroup2 - user1,user-4,user-2

    EX opgroup3 - user7,user-5,user-3

    SQL opgroup4- user6.

    I need the report as shown below if we have 25 alerts where alerts are assigned

    user1  - 2 AV alerts , 2 AD alerts.

    user2  - 5 EX alerts, 1 AV alerts

    user3  - 2 EX alerts, 2 AV alerts.

    user7  - 2 EX alerts,

    user6  - 6 SQL alerts,

    user4  - 3 AD alerts,

    user5  - 2 EX alerts,

    reports should be as below.

    ----------   ------ -------

    AV opgroup1  |  5

    AD opgroup2  |  5

    EX opgroup3  |  6

    SQL opgroup4 |  6

  • Hi Kevin,

    Hope you understand my requirement. In detail the alerts raised and assigned/ closed based on the operator group we need a count instead of alerts closed by each individual operator as your first script above.

    Please let me know if you need more details for the request.

    Thanks in advance.

  • can come one help me out on this.

  • can some one help me to find the relation between alerts table and operators group ?

  • how we can generate out monthly report for the alerts closed/resolved for particular month from the SQL?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs