Systems Management

Cut Through The Noise: Better Reporting with MOM and SMS

Richard Threlkeld

At a Glance:

• Reports from MOM and SMS
• Querying the right data
• Data views provided by SMS and MOM
• Generating reports

Systems Management Server

MOM 2005

SQL Server

If you have more than one server or desktop in your organization, managing and monitoring your infrastructure is most likely a priority for you. To that end, you’ve probably heard

of Systems Management Server (SMS) and Microsoft® Operations Manager (MOM). SMS is the Microsoft product that allows you to efficiently roll out software updates and configuration apps across your organization. MOM monitors servers and apps across your network and collects information on such things as server performance. Both solutions collect a wide variety of useful data that you can view, sort, and run reports on. Both SMS and MOM store their reporting data in SQL Server™ and make it available for querying. To get the most out of SMS and MOM reporting you need to know how this data is organized and how to query the database. You also need to know how the data gets from the client to the database and at what frequency this happens.

Reports from both SMS and MOM come in two types: management reports, which are used to get a picture of the overall state of the systems, and troubleshooting reports, which are used by the support teams. The hardest part of creating SMS reports is determining exactly where to get the data; the documentation can be somewhat lacking on this point. Fortunately, the Views you need to query for data are somewhat intuitively named, and reference scripts help you find what is needed.

Reports from SMS

When creating SMS reports, remember that the default security context that SMS 2003 sets up has read-only access to Views, so don’t waste time modifying table security settings or looking for data there. To grant a person or group rights to view SMS reports, the best practice is to create a group in Active Directory® and add it to the SMS Reporting Users group on the SMS Reporting Point. Then you can use that Active Directory group to control access. The SMS Reporting Users group must also be granted Read security rights to the Report object inside SMS.

Views You Can Use

SMS creates many useful views as well as many others that you can ignore. For example, ignore any view that begins with _RES_COLL_ or v_RES_COLL_, for these are the Collections that SMS has replicated over to Views. You can still query Collections, but it is better to use a variable of the Collection. You should ignore the collections that begin with v_HS_ as these are matched to the HIST tables, which contain archive copies of old data. The v_GS_ views contain the most recent data.

You should use the v_R views whenever it is possible as they will match the current resource (User, System, Group) to the data you want to report on. They contain the column that is most easily used to match with joins—the ResourceID. You can actually see how these map together by issuing the query

SELECT * FROM v_ResourceMap 

or by looking up the SMS_ResourceMap in the SMS SDK.

At this point you’ve probably started looking at all the views in your SMS database and found that some items look similar to your SMS Administrator console. You may have also found some things that you are familiar with in your SMS_DEF.MOF. For example, these views should be familiar:

v_AdvertisementInfo
v_Collection
v_DistributionPoint
v_GS_ADD_REMOVE_PROGRAMS
v_GS_PROCESSOR
v_GS_SoftwareFile
v_PackageStatus
v_R_System
v_StatusMessage

If you were to run a simple SELECT statement against v_DistributionPoint, you might think that this could be used to report on your Distribution Points. However, it’s actually used to match up the status of the distribution points to a particular package to which they have been added. This is one of many examples in which the name of the view doesn’t necessarily represent the kind of data that will be returned. This is why you’ll need to experiment with SELECT statements like the following to find the best places to get data:

SELECT * FROM <INSERT VIEW>

This is also a good time to become familiar with the SQL Server Query Analyzer (or, in SQL Server 2005, Management Studio). Using it to play around with SELECT statements is a safe way to learn the syntax as long as you only use SELECT, not INSERT or other commands that make changes.

If you need data on Advertisements, Site, or Clients, Status Messages can often provide the actual data you need. For example, if you were looking to find which clients failed to run a particular Advertisement you would need to pull data from the v_StatusMessage View and limit the Components. Let’s take a look at the default Reports that are included in SMS 2003 since many utilize this View (there is a Category with several reports). First, navigate to the Reports node in the SMS Administrator console. Next, double-click a specific report and hit the Edit SQL Statement button, as shown in Figure 1.

Figure 1** Edit SQL Statement **

You will then see the equivalent of a Query Builder. However, if you do not enter a valid statement, the error that is returned when you hit OK isn’t the easiest to read. As a result, most people design reports in Query Analyzer. Highlighting a column and then clicking the Values button, as shown in Figure 2, is very helpful if you want to know what data is present.

Figure 2** Creating Queries **

There are times that you might want to limit a report to something like a Collection or Advertisement, which is where prompts come into play. The example in Figure 3 uses the name of a system as the only prompt (there can be multiple prompts). The SQL statement that matches the prompt to a record must be supplied and it must be unique.

Figure 3** Prompt Properties **

You could have just as easily limited this to a Collection by issuing the following SQL statement:

begin
 if (@__filterwildcard = ‘‘)
  SELECT DISTINCT CollectionID, Name 
    FROM v_Collection 
    ORDER BY Name
 else
  SELECT DISTINCT CollectionID, Name 
    FROM v_Collection
    WHERE CollectionID like @__filterwildcard
    ORDER BY Name
end

If you were to click on the Edit SQL Statement button in Figure 3 you would be presented with the same screen you saw in Figure 2 for navigating through the available views and selecting columns. An example of limiting a report to a specific system, as taken from the base reports, is shown here:

begin
 if (@__filterwildcard = ‘‘)
  SELECT DISTINCT SYS.Netbios_Name0 
    FROM v_R_System SYS 
    WHERE SYS.Client0=1 
    ORDER By SYS.Netbios_Name0
 else
  SELECT DISTINCT SYS.Netbios_Name0 
    FROM v_R_System SYS 
    WHERE SYS.Client0=1 and 
      SYS.Netbios_Name0 LIKE @__filterwildcard
    ORDER By SYS.Netbios_Name0 
end

You can also easily change some properties that determine how a report is displayed, such as automatic refresh and creating a chart (you must have Office Web components installed on the IIS server in order to do this).

Specific returned values from your SQL statement can also be linked to other reports as prompts. For example, if you ran a report that returned a list of all advertisements targeted at one computer, it could be linked to drill down to the status message details for the distribution (see Figure 4).

Figure 4** Link to a Report **

SMS 2003 is better at organizing data than earlier versions of SMS. For instance, to get information more easily without weeding through Status Messages you can use v_ClientAdvertisementStatus. However, if you’re reporting on the actual messages inside of a Status Message (for instance, the Status MIFs in your Packages like ismif32.exe will send), then you will still need to query a Status Message view.

Now that you’ve seen how to open up and take a look at some of the default views, go ahead and sort them and then look at some that are based on Status Messages. Copy the SQL out of the Report Wizard and use Notepad or Query Analyzer to view it. Take a look at some of the other Reports on Client information such as Hardware or Operating System details. Familiarize yourself with how and where the data is stored.

There are several ways to create new reports, the easiest of which is by getting a MOF file from someone else and importing the Report that they created into your site. Simply right-click the Reports node in the SMS Administrator console, select All Tasks | Import Objects, and walk through the Import wizard. To export your own reports, simply work backwards—right-click on a report, select All Tasks | Export Objects, and walk through the Export wizard.

Duplicating a report within your SMS site is even easier. To do this, right-click an existing report and select All Tasks | Clone. (It’s important to view the reports you’ve already created so you know if one has a similar style or data.) Next, simply enter the new name of your report and then complete the steps shown earlier to view or modify a SQL statement for a report. When viewing reports, you will see a Values button in many of them. These are the prompts that I previously discussed.

The great thing about using SQL is that you can just enter a wildcard character such as "%" to match all possible values, or click the Values button to browse the values that are currently available for input. Once you have selected a value for the prompt, click the Display icon to view the report. A sample report is shown in Figure 5.

Figure 5** SMS Report Sample **

Reporting from MOM

If you are comfortable creating reports in SMS at this point by playing with SQL and using the Report Builder interface, then creating MOM reports won’t be much of a stretch. The MOM reporting schema is documented by Microsoft, so the task of finding where the data resides is much easier. Note that you will be using the SystemCenterReporting database and not the OnePoint database for your queries. You should stick to views named SC_*_View, staying away from the SC_Rel*_View names (Relationship Views). These different types of views can be broken down very easily since data is replicated from the OnePoint database in a controlled manner. The different types of views are described in Figure 6.

Figure 6 MOM Views

The Fact and Dimension views will be used the most for reporting on Events and Alerts. The Class views will be more commonly used for reporting on operating system details.

Step by Step

If you installed Visual Studio® after installing SQL Reporting Services you can always go back and modify the current installation. However, you should install Visual Studio first to save time. To create a report, launch Visual Studio and select Business Intelligence Projects, then choose New Report Project Wizard. At this point you will get the first page of the Report Wizard and a data source to query must be created. You need to be running this under an account that has rights to the MOM database to at least query everything (DataReader rights will be sufficient). You can choose any name, but for my example, I’ll call it SystemCenterReporting, the same as that of the database. Click the Edit button to select the server running the SystemCenterReporting database and select the database itself. Also make sure to use Windows NT® integrated security. Click Next.

Figure 7Sample Query

SELECT AF.LocalDateTimeRaised AS Time, CD.FullComputerName AS Computer, 
       AF.AlertName as Name, AF.AlertDescription AS Description, 
       AF.RepeatCount AS RepeatCount
FROM   SC_AlertFact_View AF 
INNER JOIN SC_AlertLevelDimension_View ALD ON AF.AlertLevel_FK = ALD.SMC_InstanceID 
INNER JOIN SC_ComputerDimension_View CD ON AF.Computer_FK = CD.SMC_InstanceID
WHERE ALD.AlertLevelName LIKE ‘Warning’
GROUP BY AF.LocalDateTimeRaised, CD.FullComputerName, 
         AF.AlertName, AF.AlertDescription, AF.RepeatCount
HAVING (7 >= DATEDIFF(Day, MAX(AF.DateTimeOfLastEvent), GETDATE()))

After you Test Connection successfully, click OK. Next you will see the Query Design page; this is where you will input your SQL Statement for querying the SystemCenterReporting database. If you already have the query handy you can just paste it in. If not, you can build it in the Query Builder. However, the GUI isn’t as robust as the aforementioned SQL Query Analyzer so I recommend that you build your statement beforehand and paste it in. The example report shown in Figure 7 will give you all Alerts that have been generated in the last seven days so you can get started walking through this demo.

If you want a less advanced query for the purpose of learning how it works, you can also run the following basic query to pull all Alerts:

SELECT * FROM SC_AlertFact_View

Next, you must determine how you want the report to look by selecting a Tabular or Matrix view. Choose where you want the values that you returned (in the SQL statement you pasted before, this is the columns in the SELECT part) to be displayed in the report. This does take a bit of thought as it must make logical sense in the grouping. Some data might be more appropriate for the details section of a report rather than the main page. In the Report Wizard, you can choose to modify the layout of the tables on the page. Finally, select the style you want applied.

Once all the report display choices have been made, you will need to input the location where you installed SQL Reporting Services. If you need to change this later on (perhaps you have a test location for reports before deploying to production) you can do so in the properties of a report by modifying the TargetServerURL value. Click Finish to create the report.

Before you deploy the report to a Web site, you can alter the query or how it’s displayed and see what it will look like in production. To do so, click the Data, Layout, or Preview tab under the toolbar in Visual Studio.

Once you are satisfied, simply click Build | Deploy MOMReport (the project name). Any errors will be listed at the bottom in the Output pane. You are looking for text similar to the following:

------ Build started: Project: MOMReport, Configuration: Debug ------

Build complete -- 0 errors, 0 warnings

------ Deploy started: Project: MOMReport, Configuration: Debug ------

Deploying to https://localhost/ReportServer?%2fMOMReport
Deploying report ‘Report1’.
Deploy complete -- 0 errors, 0 warnings

Viewing the MOM Report

At this point you can view your report (see Figure 8). Go to the location where you installed SQL Reporting Services and you should see a folder called MOMReport with a "! New" property. Drill down and view your report.

Figure 8** MOM Report **

Performance problems across a large set of records is something you will want to watch out for. It’s best to return as few rows as needed, but many times data retrieval cannot be limited in this way. One thing that you can do to track the efficiency of your queries is to check the ExecutionLog table in the ReportServer database for details of when a report was run. With SQL Reporting Services you’ll want to make sure that IIS is functioning properly. SSL can be problematic if IIS Lockdown is run or ports 80 and 443 are blocked, so it’s best to pay attention to which systems you are locking down. The example for MOM reporting here using SQL Reporting services can also be used for SMS reports if you wish. It’s actually a good idea to become familiar with this as the future of reporting across both platforms will be SQL Reporting services, especially as you move further into the System Center family of products.

Charting and other report features are also easier and much more robust to create with SQL Reporting Services. For instance, using the same MOMReport project, create a new report in the Solution Explorer and either click the toolbox on the left of the explorer or click View | Toolbox in Visual Studio. Then you can simply grab the Chart object and drag it onto the report. You’ll see the Chart generated for you. If you are familiar with charting in Microsoft Excel, this will be even easier for you.

At this point you could drop more fields into the chart or set the data source references by right-clicking the chart and selecting Properties. Setting the values for the data is just as easy since you already created a data source as well as a SQL query for this project.

Where to Go Next

There are a number of community resources for building reports in SMS and MOM. Using the default reports as a point of reference and browsing through the database structure will only get you so far and at some point you will need to look at what others have created. Some great free Web sites for getting this data are shown in the "TechNet Online Resources" sidebar.

TechNet Online Resources

For more details on SMS and MOM reporting features, as well as supporting technology, visit these TechNet Online resources.

• SMS Reporting FAQ
  This site will answer many of your questions regarding Web reporting.
• Script Center: Scripting for SMS
  If you need to write scripts for use with SMS, this site will be most valuable. It is updated often, so be sure to check back frequently.
• Script Center: Scripting for MOM
  Whether you need to know the basics for writing scripts for Microsoft Operations Manager, or you need a deeper drilldown, this site should provide the answers.
• SQL Server TechCenter
  The SQL Server TechCenter provides links to all the Microsoft resources available for SQL Server as well as many resources from the SQL community at large, with blogs, webcasts, and online chats.

Richard Threlkeld is a Systems Engineer for 1E Limited. Before working for 1E, Richard worked for two large Fortune 500 organizations. He has done several SMS and MOM deployments and spoken at industry events and community meetings. He can be reached at richardt@1e.com. 1E Limited will donate the proceeds of this article to charity.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.