All posts in SQL

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 the reason as server was put into maintenance that just code 8 or 9.

Here it is, enjoy.

Read more

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, scheduled and emailed from SCOM itself. Then you’ll be pleased to know that I’ve been working on it and soon plan to release my version 1.0 SCOM Health Check Report. So this new report will be like the previous one as far as content goes, giving you the same sort of information, except instead of being written in Windows PowerShell I wrote this using SQL Queries and SQL Report Builder. I’ve then taken that and populated it into a SCOM Management Pack so you guys can simply import it straight into SCOM and schedule it to email you…how easy is that!

It has a slightly different look to my previous reports and I’m still playing around with styles so I haven’t actually settled on a final one but it will most likely look something like this..

SCOM Report v1.0








Read more

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 0, 1 or 2…it outputs the english word, Informational, Warning or Critical. Same with the Monitor or Rule. Just a bit easier to read than working out if a 0 or a 1 is a monitor or rule.

Read more

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
Severity: Error

The EXECUTE permission was denied on the object ‘sp_help_jobactivity’, database ‘msdb’, schema ‘dbo’.
The data access service account might not have the required permissions

Read more

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 and remove it from being monitored. In this particular case that wasn’t happening.

As I move through this blog post some of this information I’ll make specifically about an IIS Web Site but the SQL and PowerShell will all be the same process for pretty much any object that is not undiscovering even when the actual object no longer exists.

Read more

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.


use OperationsManager
select [Path], [Description_7FEEF780_F4BD_AF93_ED3F_F026EEAAEAEA], [LogFileDirectory_0499BCD1_1959_14CC_AEB8_0A5CC9AC09D3], [Path_21E71E34_E24F_B3EC_B0EB_4670B79C9382] from dbo.MT_Microsoft$Windows$InternetInformationServices$2008$WebSite as IIS
Inner Join BaseManagedEntity as BME on IIS.BaseManagedEntityId = BME.BaseManagedEntityId


Now if you’re only interested in finding out what websites a specific server monitors, then just add on a simple Where Clause…

use OperationsManager
select [Path], [Description_7FEEF780_F4BD_AF93_ED3F_F026EEAAEAEA], [LogFileDirectory_0499BCD1_1959_14CC_AEB8_0A5CC9AC09D3], [Path_21E71E34_E24F_B3EC_B0EB_4670B79C9382] from dbo.MT_Microsoft$Windows$InternetInformationServices$2008$WebSite as IIS
Inner Join BaseManagedEntity as BME on IIS.BaseManagedEntityId = BME.BaseManagedEntityId

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 = ‘microsoft.systemCenter.agent’)
order by ManagedEntityGenericView.DisplayName