CHAPTER 2
Configuration Manager custom reporting

Microsoft System Center 2012 Configuration Manager includes a number of different built-in reports that can be used to retrieve information from the Configuration Manager database. These reports can provide great visibility into your overall environment. Configuration Manager uses Microsoft SQL Server as its backend database engine. During hardware and software scans, Configuration Manager scans the local machine and sends the information collected to the backend database to be stored there. Since the data collected resides in an SQL database, Transact-SQL (T-SQL) queries can be used to query the database and retrieve information about the data stored in it.

Configuration Manager creates several database tables and views during the site server installation. These views and tables are queried by built-in queries to generate the reports. If the default reports are not sufficient for your needs, however, you can generate custom reports by creating custom T-SQL queries. This chapter discusses design issues concerning the SQL database and underlying tables and views used for querying the database and generating custom reports.

Configuration Manager SQL database design

During the installation of the Configuration Manager 2012, the Configuration Manager database is created. By default the name of the Configuration Manager database is CM_<SiteCode>. The Configuration Manager database is spread across multiple tables.

To examine the database using SQL Management Studio, go to All Programs, Microsoft SQL Server, SQL Server Management Studio, and select CM_<SiteCode>to open SQL Management Studio. If you expand the CM_<SiteCode> database, you will see all the database objects pertaining to the Configuration Manager database as shown in Figure 2-1.

FIGURE 2-1 The Configuration Manager database in SQL Management Studio.

Configuration Manager database tables and views

Configuration Manager creates several tables and views during the installation of the site server. Having a solid understanding of these tables and views is critical for creating custom reports in Configuration Manager. This section provides some tips for identifying the relevant table or view to be used for custom SQL queries.

Open SQL Management Studio, select New Query, and then select Configuration Database. Next run the following query to list all configuration database tables:

Select * From Information_Schema.Tables order by Table_Name

The query result is shown in Figure 2-2.

FIGURE 2-2 Configuration Manager tables and views.

You can search any tables or columns in the Configuration Manager database. For example, you might want information about MAC addresses. Start by finding the table or view that contains this information. To do this, run the following query to get all tables and views that contain a column name like %MacAddress%:

Select * from INFORMATION_SCHEMA.COLUMNS
Where COLUMN_NAME like '%MacAddress%'

The result is shown in Figure 2-3.

FIGURE 2-3 Searching the database for MAC addresses.

The difference between SQL tables and views

Both views and tables are database object types. In simple terms, views are stored or named select queries. Tables are made up of columns and rows. A column is a set of data, which belongs to a same data type. A row is a sequence of values, which can be from different data types. Each column is identified by a column name, and each row is uniquely identified by the table primary key.

Views

The body of each view is a SELECT statement. Views are called the “virtual tables” of the database. Though the views are stored in the database, they are not run until they are called using another SELECT statement. When they are called using SELECT statements, their stored SELECT queries are executed and show the results. Since views have only SELECT queries as their bodies, they do not need a large space. Views provide several benefits.

Once the view is created, it can be called again and again using its name, without rewriting the SELECT query.

Since views are pre-compiled objects, they execute faster than executing their SELECT queries (the Body of the views) separately.

Views can be used to restrict the table data access. Therefore, they can play an important role in data security, as well.

Tables

A table is a collection of rows that store data. Each row in a table can include data from a different data type, and each row must be identified by a unique identifier (primary key). You use INSERT, UPDATE, and DELETE queries to insert a new row, update an existing row value, and delete a row from the table. SELECT queries are used to retrieve data from tables. If necessary, you can use ALTER TABLE queries to change the table structure after it is created. Tables need more space than views to store data content. There are several types of tables in databases.

Internal tables

External tables

Temporary tables

Because views offer many advantages over tables as detailed earlier, it is best to query views instead of tables when you query the Configuration Manager database. In short, when you are querying the Configuration Manager database, always query the configuration views instead of the table.

Common Configuration Manager views

The Configuration Manager database contains several views. It’s worthwhile to know some of the common views that contain useful information.

One of the most useful views the V_R_System view shown in Figure 2-4. This view is derived from the discovery data table. It contains information about the Configuration Manager clients.

FIGURE 2-4 Configuration Manager view V_R_System

Another frequently used view is V_GS_Workstation_Status, which you can use to pull the last hardware scan time.

V_GS_ADD_REMOVE_PROGRAMS is another useful view that contains add and remove data collected from Configuration Manager clients.

For network adapters, it is common to query the V_GS_NETWORK_ADAPTER view to see the different columns. If the network adapter configuration data is required, start by querying V_GS_NETWORK_ADAPTER_CONFIGUR.

Similarly, you can use the V_GS_COMPUTER_SYSTEM view for determining the hardware model and manufacturer.

You should develop good familiarity with the different views as you start creating custom queries. But you always need to remember to use views instead of tables when you are querying the database and creating your custom T-SQL queries.

NOTE The configuration database contains different types of views. For example, views that start with V_GS contain current data while those that start with V_HS contain historical data.

Working with SQL inner and left outer joins

The Configuration Manager database uses a relational database design, but the information stored in it resides on different database tables in many different places. Writing custom queries not only requires a good understanding of the database table schema but also knowledge of T-SQL. When the information you want resides in a single view, simple SELECT statements work well. However, when the information to be retrieved resides in multiple tables, you must join the tables to get the required data.

For example, if you need to pull the information about all of the machines in the Configuration Manager database, you would use a join. To do this, you run the following T-SQL query:

Select Name0 from v_R_System

The result is shown in Figure 2-5.

FIGURE 2-5 Pulling the names of machines in the Configuration Manager database.

Now you might want to add another column that shows when the last hardware inventory occurred for these machines. To do this, you need to reference another view named V_GS_ Workstation_Status. You also need to use a join, so start by using the following inner join.

Select SYS.Name0,
       wk.LastHWScan
From v_R_System SYS
Inner Join v_GS_WORKSTATION_STATUS wk on
SYS.ResourceID=WK.ResourceID

The results are shown in Figure 2-6.

FIGURE 2-6 An example of an inner join and the corresponding result.

As you can see from Figure 2-6, the record set dropped from three records to one. An inner join returns the matching records in both tables. The V_GS_Workstation_Status view contains only the record for the one machine shown in this figure, while the V_R_System view contains the three records shown previously in Figure 2-5. So an inner join by design looks for matching records in both tables.

But what if you need to report the data for all machines irrespective of whether the LastHWScan data for the machine is available? In this scenario you could use a left outer join (commonly known as a left join) as shown in Figure 2-7. Despite the fact that some of the data is not available in the V_GS_Workstation_Status table, the SQL query returned all the data set available on the V_R_SYSTEM view. Here is the query you use:

Select SYS.Name0
       wk.LastHWScan
 from v_R_System SYS
left Join v_GS_WORKSTATION_STATUS wk on
 SYS.ResourceID=WK.ResourceID

FIGURE 2-7 An example of an outer join and the corresponding result.

These examples illustrate the power of using inner and outer joins when querying the Configuration Manager database.