Category: SQL

  • What SCOM Objects are in Maintenance Mode (SQL Query)

    If you want to find out what objects are currently in Maintenance Mode, here’s a nice bit of SQL Code you can use for just that. I’ve added a more useful English Reason than just the code because SCOM stores the code itself in the Database. It’s a lot easier in my opinion to identify […]

  • SCOM Health Check SQL Report v1.0 (coming soon!)

    I’ve had so much positive feedback about my original SCOM Health Check Script and also the updated v2 one which a lot of people thanked me for updating it with the fresh new style. However I also got some great feedback asking if I could turn it into a SCOM Report that could be run, […]

  • Get alerts from Management Servers using SQL

    There’s a lot of ways we can retrieve a list of alerts from SCOM, but nothings quicker than going to the Database directly. So here’s a bit of SQL that can grab all alerts from a Management Server, but formats the output a bit nicer. So for example rather than outputting the severity as a […]

  • Get a list of SCOM Management Servers and Gateway Servers from SQL

    I recently received a question asking about getting some of the information from a table in my health check script directly from SQL. So in response to that, here’s some SQL code that will pull out the Management Servers and the Gateways from the SCOM Database, along with some performance collection data about each server.

  • How to get the number of alerts in the last 24 hours using SQL

    Here’s how to get the number of alerts in the last 24 hours using SQL. Thanks Michael for the question.   SELECT COUNT(*) AS [Number of Alerts per Day] FROM Alert WITH (NOLOCK) WHERE TimeAdded > dateadd(hh,-24,getutcdate())   Hope that helps.

  • Retrieve all Open SCOM Alerts from the Operations Manager SQL Database (SQL Query)

    How can we get a list of all Open SCOM Alerts from the Operations Manager Database using a SQL Query? Well there are simpler queries, but this is the query I like to use because it adds a lot more information to the output…

  • SCOM 2016 – The EXECUTE permission was denied on the object ‘sp_help_jobactivity’, database ‘msdb’, schema ‘dbo’

    So I’ve just installed SCOM 2016 in my lab (well actually I’ve probably done it over a dozen times this weekend testing out scripts etc) and my Management Server suddenly popped up this message (after selecting the new Maintenance Schedules in the SCOM Console).   Date: 10/15/2016 8:39:18 PM Application: Operations Manager Application Version: 7.2.11719.0 […]

  • SCOM Objects Not Un-Discovering

    A little while back we ran into a problem where a Web site would not undiscover after removing it from the IIS Server and the IIS guys were getting alerts for a Web Site that no longer existed! What should happen in a normal environment is that SCOM will detect that the site no longer exists […]

  • What Websites are we Monitoring with SCOM?

    Even though I love using Windows PowerShell, sometimes you just can’t beat diving into the database even for quick and dirty tasks. Today I was asked to get a list of all the Websites running on Windows 2008, so since I had a connection open to my Operational Database server, I decided to use that. […]

  • Retrieving a list of all SCOM Agents

    If you want to grab a list of all of your Agents, you can do that a few ways. 1. In the console from the “Administration View”. 2. Use the “Get-SCOMAgent” cmdlet. 3. A SQL Query against the OperationsManager Database. use OperationsManager SELECT * FROM ManagedEntityGenericView INNER JOIN ManagedTypeView ON ManagedEntityGenericView.MonitoringClassId = ManagedTypeView.Id WHERE (ManagedTypeView.Name […]