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


Files attached below:

Alert_Reports.zip

Comments (12)

  1. Kevin Holman says:

    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:

    http://blogs.technet.com/kevinholman/archive/2008/06/27/creating-a-new-data-source-for-reporting-against-the-operational-database.aspx

    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:

    http://blogs.technet.com/kevinholman/archive/2008/09/03/using-opsmgr-to-see-which-servers-have-not-been-logged-on-to-via-rdp.aspx

  2. Kevin Holman says:

    If you can explain that again in English – I will do my best.  🙂

  3. Richard says:

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

  4. Richard says:

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

  5. kldsts says:

    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.

  6. kldsts says:

    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.

  7. kldsts says:

    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

  8. kldsts says:

    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.

  9. kldsts says:

    can come one help me out on this.

  10. kldsts says:

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

  11. Steven says:

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

Skip to main content