Tag Archives: #OpsMon

SCOM: Alerting when a user is added or removed from a Distribution Group in AD

Although this is similar to alerting when a user is added to a security group there are a few things that need to be changed.

Target: Windows Domain Controllers
Log: Security
Event ID: 5136
EventDescription contains: “Name of Distribution Group”

User added
EventDescription contains: %%14674

User removed
EventDescription contains: %%14675

Restoring SCOM databases

Recently I’ve had to recover a SCOM environment. This process required me to restore the SQL backups of both the OperationsManager and Data Warehouse databases.

After stopping the SDK service on our RMS, trying to restore the backup from the management studio under Tasks > Restore  > Database I came across a frustrating error “Exclusive access could not be obtained as the database is in use.”

A bit of research led me to an easier way to perform the restore, as well as check what is blocking the exclusive access,

First in SQL management studio select your master database and click new query:

USE MASTER
ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-This will make it so only one connection to the database can be made.
-Run the following command to see where any recurring connections to database are coming from.

EXEC SP_WHO2

-Check this list, looking under the DBName column.  If the database is listed, check the ProgramName, and HostName column to see who is attempting to connect.
-If it is not a service, or other application that would automatically reconnect which can be shut down, note the number in the SPID column to kill the connection, and immediately begin the backup.  Replace SPID below with just the number.

KILL SPID
RESTORE DATABASE DATABASENAME FROM DISK = ‘X:\PATHTO\BACKUP.BAK’
GO

-If this completes successfully, we can set the newly restored database back to multi user mode.

ALTER DATABASE DATABASENAME SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

After restoring your OperationsManager database you will also have to re-enable your SQL Broker as it is required in order for your SCOM discoveries to work.

To check if your SQL broker is enabled run the following query, returning a value of ‘0’ means that the Broker is disabled.

SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'

To enable the Broker user the following queries:

ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OperationsManager SET ENABLE_BROKER
ALTER DATABASE OperationsManager SET MULTI_USER

 

 

Alerting when an account is set to not expire

We had a requirement to alert when an account was created in AD with the “Password will not expire” flag on and when an existing account is changed to a password that will not expire for audit purposes.

It can be done using the following alert generating rule:

Expire1

Expire2

 

The reason for the %%2089 is that events on the domain controller are generated using codes which are then converted to English in the event viewer. Something to bear in mind when creating rules to look at DC event logs.

Note: This event is for AD 2008 only.