Tracking down where Operations Manager stores information – Part 2

Welcome to "how to do crazy stuff for Operations Manager using SQL, XML and PowerShell" (that was the original name for this blog post before I decided to make this into a series of blog posts which will focus on doing off the wall crazy stuff to meet business requirements in Operations Manager). Please realize that I am not a DBA. I am a geek who focuses on Operations Manager. So my queries and approaches may not be optimal, but they were worked for me!

In this blog post we will look into using this same set of steps to do some more powerful items with Operations Manager:

  • Finding all rules and monitors which execute scripts
  • Finding all rules and monitors which run at a specific interval

In Part 1 of this series we investigated 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. We used these tricks to track down where images which were uploaded into the topology or image widgets were stored in Operations Manager. Using these same techniques we can answer some very interesting questions about Operations Manager. For this specific blog post we will be focusing on SQL queries and XML, in others we will work with PowerShell and SQL.

 

Finding rules and monitors which execute scripts

For this example, I created a sample management pack with various types of rules and monitors which have the capability to run a script. When creating these rules and monitors I created them with unique naming so that they would be easy to locate in the database. Based upon my searches (see Part 1 for the SQL script link) I found that the tables which were storing the rules and monitors were Dbo.monitor and Dbo.module.

After finding these the rules and monitors were stored in these tables, it was now viable to search the fields available for the specific script references which I had created in my sample management pack.

For dbo.module there were three conditions which indicated a script was being run:

  • The modulename field included had a value of either ‘ExecuteScript’ or ‘ExecuteCommand’.
  • The ModuleConfiguration field included the XML tag <ScriptName> to indicate that a script was being called.

For dbo.monitor there was one condition that I found which indicated that a script was being run:

  • The ConfigurationXML field included the XML tag <ScriptName> to indicate that a script was being called.

Ok, stepping back and looking at this – this is an extremely powerful trick here. We can use SQL to query the database and XML is stored in one of the fields. Since we can find the XML code we can now do searches with a field to match XML tags in the management pack!

The resulting SQL queries to report on rules and monitors call scripts within Operations Manager:


select
*
from
dbo.module
where
modulename =

‘ExecuteScript’
or modulename

=
‘ExecuteCommand’

select
*
from
dbo.module
where
ModuleConfiguration like

‘%<ScriptName>%’

select
*
from
dbo.monitor
where
ConfigurationXML like

‘%<ScriptName>%’

 

An example of the results of the first query is shown below: (which also shows that we could match by the specific ManagementPackId to restrict the query to a single management pack)


The queries I have above represent the ways that scripts are stored in the OperationsManager database as far as I could find during my testing. Please note that there may be others beyond what I’m identifying above.

 

Finding all rules and monitors which run at a specific interval

I had a request to find all rules and monitors which were executing at a specific interval (60 seconds in this case). To determine this we could search all of the XML files for specific tags but that takes a lot of work to move that into a format which is readable. So we can use the trick above (SQL query with an XML tag match) to determine these conditions as well. The two queries below show how to find rules and monitors which include the tag matching our specific criteria (IE: those running every 60 seconds). By specifying both the start <IntervalSeconds> and the end </IntervalSeconds> we can be sure to only match those rules and monitors which run at 60 seconds instead of those running for 600 seconds as an example.

 

select
*
from
dbo.module
where
ModuleConfiguration like

‘%<IntervalSeconds>60</IntervalSeconds>%’

select
*
from
dbo.monitor
where
ConfigurationXML like

‘%<IntervalSeconds>60</IntervalSeconds>%’

 

An example of the results of the first query is shown below: (which also shows that we could match by the specific ManagementPackId to restrict the query to a single management pack)

As with the earlier one of these the queries I have above represent the ways that scripts are stored in the OperationsManager database as far as I could find during my testing. Please note that there may be others beyond what I’m identifying above.

 

Summary: By combining the information that we can query out of SQL with content matching for XML tags we can answer some questions which historically would not have been easy to answer. In the next part of this series we will look at using SQL and PowerShell to provide us with information available between different management groups.

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.