Welcome to Part 4 on how to do crazy stuff for Operations Manager using SQL, XML and PowerShell.

In Part 1 we covered places where Operations Manager could store data and used unique search stings to see what that data is stored either in the XML, registry, or in the databases.
In Part 2 we covered how to find rules and monitors which match specific conditions (like running scripts or items which run at a specific interval).

In Part 3 we covered how to use PowerShell and SQL to work with multiple management group configurations.

In this blog post we will look into where overrides are stored in Operations Manager and see what audit type information is available for these overrides.

  • Is there auditing information stored in Operations Manager when an override is created?
  • Another area that information is stored…

Is there auditing information stored in Operations Manager when an override is created?

Operations Manager uses sealed management packs with default health models pre-configured. As an example, there are built-in thresholds for free disk space, processor utilization and such. This default behavior can be changed for a specific server, a class of servers or a group of servers using an override. A commonly asked question in Operations Manager is how we can determine who changed behavior of Operations Manager’s default health model through the creation of an override (IE: Who made this override and why?)

To determine where this information is stored and see what additional information is available I used the same approach that has been discussed in this series (searching the registry, xml files, and the database) to identify where a unique string was stored. To do this I created the override in a new management pack and exported the new management pack. Within the exported management pack I found the unique string as shown below. A review of the XML does not give any indication that the user who created the override is included within the XML content.

By using this ID string I could now search the database for this unique reference as shown below. This was stored in dbo.MonitorOverride and two other tables (dbo.ManagementPack and CS.ContainerSource).

CS.ContainerSource fields:

Tablename    

ContainerSourceRowId    

ManagementPackRowId    

ElementTypeName    

ElementGuid    

ElementSystemName    

TargetManagedEntityTypeRowId    

ContextManagedEntityTypeRowId    

ContextManagedEntityGuid    

LastModifiedDateTimeUtc    

LastModifiedBatchId

 

Dbo.Managementpack fields:

Tablename    

ManagementPackId    

MPIsSealed    

MPName    

MPFriendlyName    

MPVersionDependentId    MPVersion    

MPKeyToken    

MPReadOnly    

MPXMLInvalid    

MPLastModified    

MPCreated    

MPXML    

MPSchemaTypes    

 

The important record appears to be in the MonitoringOverride table. The actual record with the table names are shown below.

Dbo.MonitoringOverride fields and values:

Tablename: Dbo.MonitoringOverride

MonitorOverrideId: FDE77960-55B4-069A-5423-5FF0CD62D26F

OverrideName: OverrideForMonitorMicrosoftSQLServer2012DBEnginePageLifeExpectancyMonitorForContextMicrosoftSQLServerDBEnginec32d633770fa431ca42880646d00dc0a

MonitorId: 2ED6547B-A29B-4EF5-7B96-0C362F108D9E

OverrideableParameterId: F4C6D627-66B7-6500-8F56-956A99F9387C

TypeContext: B87E82BC-1A85-4DE3-330A-13133CF5F9C3    

InstanceContext: CD1CC89C-A131-D696-C52E-22CF29899FB6

Value: false

Enforced: 0

ManagementPackId: 0238CCEA-22A3-2F34-81AC-838F407D97BC

Comment: NULL

TimeAdded: 2014-06-24 16:10:10.640

LastModified: 2014-06-24 16:10:10.640

 

From what is written to the database in each of these tables, we can tell that an override was created but there does not appear to be any fields which indicate who made the override. So while we can’t tell who created the override we can tell what overrides were created on a daily basis. This makes sense as we can use the Generic Reports Library to report on when overrides are created (this is discussed at http://social.technet.microsoft.com/Forums/systemcenter/en-US/69e2881d-b2ce-4734-a936-17bdae3fb8d3/audit-scom-overrides?forum=operationsmanagergeneral. CSF – show an example report.

Conclusion: To the original question "how we can determine who changed behavior of Operations Manager’s default health model through the creation of an override?" From what I’m seeing in the database, I don’t believe that this information is stored so I cannot see any way that we could generate this type of report with the out of the box functionality of Operations Manager.

To mitigate this issue we would need to restrict who can create overrides, and report when overrides are created in our management group.

 

Another area that information is stored…

In the first part of this blog post series we identified three areas where data can be stored for Operations Manager:

  • Registry
  • XML files
  • Database

What’s interesting is that there is also a fourth one which I hadn’t considered yet. With the upcoming release of the updated version of Advisor, there is a new area where Operations Manager information is stored – in the Cloud. In my lab environment I am testing out the new Advisor (if you haven’t done so yet, this is definitely worth checking out at https://preview.systemcenteradvisor.com/). Within Advisor, you connect your Operations Manager environment to Advisor on the Administration pane in the section shown below.

You choose your account to connect to Advisor (Microsoft Account or Organizational Account) and then you add the agents that you want to advisor in Operations Manager.

From within the Advisor portal you can now choose what additional functionality that you want to add. This is done in the portal by adding Intelligence Packs. The screenshot below shows that the intelligence packs have been added in my environment for Capacity Planning, System Update Assessment, Malware Assessment and Log Management.

Once the Integration Packs are chosen, Operations Manager integrates the appropriate management packs for these functions. The screenshot below which shows the addition of management packs which match the functionality choices that I made within Advisor (Capacity Planning, System Update Assessment, Malware Assessment and Log Management).

This is an interesting update in terms of where Operations Manager stores data as it provides an excellent value-add for Operations Manager functionality but since it’s stored in the cloud the best way to validate what options are chosen will be to go directly to the portal and see what options have been chosen to integrate into Operations Manager.

Summary: This blog post provided insights into why auditing information is not readily available in Operations Manager for when overrides are generated and also introduced a new area where Operations Manager can store information. In the next part of this blog series we will investigate how to use PowerShell and Excel together to provide an interesting piece of information when integrating Operations Manager into a third party ticketing system.