SCOM SQL queries

<!--[if lt IE 9]>


Comments (30)
  1. cdufour says:

    Excellent! Thx!

  2. Pramod says:

    Thanks for providing updated SQL queries.!!

  3. Kamal says:

    Excellent! Thanks

  4. dro says:

    Dude you are the absolute man when it comes to ops mgr. I am going piggyback off these queries to create tableau visualizations in an IT Ops dashboard for our organization. Thanks again.

  5. Marlon says:

    Many thanks!

  6. Scratulous says:

    Kevin, I took the liberty of wrapping your wonderful queries into an old SAPIEN Technologies PrimalForms (Community Edition) GUI for everyone’s convenience. I use this GUI everyday, and am very satisfied how it performs. I also have included in the script the Out-Excel function adapted from “The Scripting Guys”. It can be remarked or left in as well as the Out-String to $RichTextBox1 as desired. I hope everyone enjoys the GUI as muck as I do. Happy SCOMing, Dave

    KH – I deleted the script from this post because it made reading comments impossible. See technet gallery link.

      1. Scratulous says:

        Sorry, wrong paste of the gallery upload this should be correct.

    1. Dave Bowman - "Scratulus" says:

      THX Kevin…Sorry for the script dump on the blog…was over excited to get it posted…Dave

  7. SaveEarthGreen says:

    Hey Kevin,

    How can we find members of a given group when we query Dataware house database table.

    below one gives the information when we query database table

    select TargetObjectDisplayName as ‘Group Members’
    from RelationshipGenericView
    where isDeleted=0
    AND SourceObjectDisplayName = ‘All Windows Computers’
    ORDER BY TargetObjectDisplayName

    1. Kevin Holman says:

      I just added a huge section with examples of how to do just that. I have had that forever…. this blog format is getting out of hand… it makes it hard to edit in Live Writer. I hope it doesn’t get a lot bigger. 🙂

      1. sankara says:


        Is there any way to get the list of database users from all the connected servers and instance as report on monthy basis

  8. Boris says:

    Thanks for the queries!
    I am trying to do a select from my QRadar SIEM against the data base to retrieve important security events like “user locked out” “user added to group” etc.

    I played around with the “EventView” but cannot find the events I am looking for (locked out user, while watching the table). The SCOM operator guys created e-mail alerts if these events raise, but I cannot see/resolve the relationship in the data base.

    anybody tried anything similar before?


  9. michael leo says:

    I am looking for a query that spits out all the hostnames that have “checked in” in the past x number of days.

    Is there such a thing?


    1. Kevin Holman says:

      We collect the 6022 event, which is logged every 15 minutes on all agents. So just use the event queries, and you can look for servers that have at least one events within x timeframe.

      1. michael leo says:

        Excellent! Thanks for the reply

  10. Arun says:

    Hi Kevin,

    Can you please help me to build new report/get SQL querie to get the process name which has high utilization on a server.
    is it possible to find out this information.

  11. CloudInMyHead says:

    Awesome !
    Thanks for sharing.

    1. sankara says:

      Hi ,

      I just want to get the failed backup jobs for all the SQL server from SCOM any help?

  12. Gourav Kumar says:

    Hi Kevin,

    Please suggest me any query by using that i can get all the rule and Monitors of any particular MP.

  13. shekkeer says:

    HI Kevin

    Can we get management pack name when we give event id

  14. kurniawancw says:

    How go pull network work device name, ios version,ip address,location,serial number from scom 2012 database, really need your help. Thanks

  15. JimmyEu says:

    Hi Kelvin, these are awesome!
    i having Invalid object name ‘EventAllView’. Any Idea , anyone?

    1. JimmyEu says:

      Ahha, i found the reason. thanks

  16. Peter Hakesley says:

    A useful query I use for my Dashbaord is the count of Unhandled Alerts Critical or warning – just change the Severity to 1 for Warning.

    select count(*)
    from AlertView
    where ResolutionState = 0
    AND TicketID is NULL
    AND Severity = 2
    AND LanguageCode = ‘ENU’

  17. Hi,

    I didnt find the exact thread to post, as its related to SQL posting it here
    SCOM data warehouse and OpsDB consuming 95%+ Memory Utilization. We already have 32GB of memory. Is there any way we can reduce the high memory utilization?

    Any help is much appreciated

  18. Hi All,

    Is there any way, we can get the all rule and monitor and discovery etc which is linked with “xyz” group for overrides / alert suppression?.

    I used the mentioned below query which are fine but not giving the complete details.


Comments are closed.

Skip to main content