No, they didn’t want a dump of all Rules and Monitors contained by all imported MPs, but more an overview of what servers and workstations are being monitored by SCOM and to what extend. Like: server X is managed by SCOM R2 and has been discovered as an Windows 2003 based Server hosting the DC role, DNS and DHCP. Server Y is also managed by SCOM R2 which is a print- and fileserver. And server Z is also managed by SCOM R2, which is a SQL 2008 R2 box, running Analysis services and a DB engine as well.
Everything in an Excel workbook with some Filters on it would be great here. But that was the least to think about. First question was: How to go about it? Some good queries were required here. But targeted against what DB? And against what Objects? And yes, I know my way around in SQL, but I am not a SQL guru who builds all kinds of queries on the fly. So some manual labor was required as well.
So what to do? I thought it over for a while, discussed it with the rest of the team and we wrote down our list of ‘ingredients’ in order to create such an Excel workbook. These are the ‘ingredients’ we required:
- List of Agents installed on the managed servers/workstations
This will give us a great starting point for the list since it will show all managed servers and workstations. Of course, there are some Agentless managed systems but these aren’t really Windows systems, so no worries there.
- List of Groups
Groups are a great way to get an insight of the SCOM R2 environment. Why? Every MP contains many Groups of its own. These Groups are populated with the Discovered systems and then the rules/monitors are targeted against it and/or these Groups are used to contain Views in the Console. So when you know what Groups are present in your environment, you have basically a good idea what is being Discovered (and thus monitored) in SCOM R2.
- Group Members
When you know the members of the Groups present in SCOM, you know how the managed servers are looked upon/recognized/monitored by SCOM R2. A server which is a member of the Windows Server 2003 Computer Group and the SQL 2008 DB Engine Group, you know this server is being monitored based on the Server OS and SQL Server MP.
OK, now we had our list of ingredients. But where and how to get them? Time for some shopping. But just stepping in the car without knowing to what shopping centre to go to (Walmart or Stop & Shop) isn’t very smart…
Again, good thinking was required. At the end we decided to use the Data Warehouse and not the Operational DB. Why? First of all, the DW is better structured, thus the place to be. Secondly, looking at the Reports, this where they all tap into. And what we were about to create, was a Report after all. Even though it would be more work than a few mouse clicks…
Even though we decided to use the DW, for the first ingredient we did not require the DW nor any SQL query. Just a simple PS cmdlet would do here: Get-Agent| Select DisplayName would do here. Piping the output to a simple text file would do just fine, so the PS cmdlet became: Get-Agent| Select DisplayName > C:\Temp\Agents.txt.
Now it was time to address Items 2 & 3. Google is my best friend when it comes down to searching and soon I found the website of much respected Jonathan Almquist. I follow his blog already for a long time but somehow these two postings had eluded me:
These queries are grouped in a table on this webpage. Great queries they are. I tried them first in my test environment and they work like a charm.
So I used the same queries in the production environment. The first query gave me all the Groups present in SCOM. I exported the result to a csv file and copied it. I edited the copy, dumping all Groups which didn’t seem relevant to me. The groups about DNS, OCS, Exchange and the like were kept.
These Groups I used in the second query, one-by-one… Every time I exported the results as well.
Based on the csv-files I built an Excel workbook, depicting all monitored servers and their functionality. Cost me some serious time, but when this workbook is properly maintained, it will be a great help.
The Next Level
Duh! All the manual labor! But I knew that it would take too much time to build queries which create such a Report, because the Excel workbook was needed fast. But it is on my mind after having created this Excel Workbook. So I have to take a deep dive into SQL but I am going to look how far I will get by in order to create a Report like this:
So hopefully (when time permits), to be continued… And yes, any input is welcome. I know about the SCC Health Reports. One Reports shows all the Groups present in SCOM. It is a start…