Understanding Configuration Manager Data

This chapter is from the book

System Center Configuration Manager Reporting Unleashed

Learn More Buy

This chapter is from the book

This chapter is from the book 

System Center Configuration Manager Reporting Unleashed

Learn More Buy

Before discussing how to write reports, let’s take a few steps back and consider where Configuration Manager (ConfigMgr) stores its inventory data and how to find that information.

As you may know, ConfigMgr collects a large amount of data out-of-the-box. Understanding how data is collected and stored within the site database can help you produce SQL Server Reporting Services (SSRS) reports that expose this raw data. This chapter discusses the major ConfigMgr data classes and how those classes are used, and it outlines some sample SQL views and queries. It also introduces you to the SQL Server Management Studio tool.

Using Data Classes and SQL Views

Many SQL database administrators (DBAs) prefer to use SQL tables rather than views. The question arises: Is it better to use a SQL table or a SQL view for queries in ConfigMgr reporting? The short answer is that Microsoft only supports using SQL views to access ConfigMgr data. Here’s a longer answer:

• Microsoft does not support creating queries against SQL tables because of the risk of table locking issues. If you are running a query directly against a SQL table at the same time ConfigMgr is trying to update or write to that table, there is a possibility that the table will be locked by your query and the ConfigMgr update will fail.

• It is also best to use SQL views for reporting because Microsoft does not guarantee that SQL tables will remain the same after you install updates, service packs, or major version upgrades. If a table name is changed, removed, or modified, your reports might return errors or cease to run as intended. By referencing the ConfigMgr SQL views, all the reports, queries, and view names used throughout this book are accurate and work in all versions of ConfigMgr 2007, 2012, 2012 R2, and 1511 environments.

While this chapter provides examples of SQL views, it is impractical to cover all the SQL views. To obtain additional information on views used in ConfigMgr, you can reference Microsoft’s ConfigMgr SQL schema via the following links:

• ConfigMgr: At this writing, ConfigMgr 1511 had recently been released, and as such, no official SQL schema had yet been released.

• ConfigMgr 2012: The ConfigMgr 2012 SQL schema can be referenced at http://technet.microsoft.com/en-us/library/dn581954.aspx.

• ConfigMgr 2007: Information on the ConfigMgr 2007 SQL schema is available at http://technet.microsoft.com/en-us/library/dd334611.aspx.

Using Discovery Classes

Discovery data generally comes from the following ConfigMgr discovery options:

• Active Directory System Discovery

• Active Directory User Discovery

• Heartbeat Discovery

• Network Discovery

You can enable and configure these discovery options for your environment in your ConfigMgr site. ConfigMgr discovery then begins gathering data based on your configurations—for example, gathering all systems from a specific Active Directory organization unit (OU) or domain. As a general rule, the SQL view for each of the discovery options starts with either v_R_* or v_RA_*. Table 3.1 lists the commonly used SQL views related to discovery classes.

TABLE 3.1 Discovery Data Views

The query shown in Listing 3.1 lists all computers and their last discovery times for heartbeat discovery. Figure 3.1 shows the results of Listing 3.1.

FIGURE 3.1 Discovery data sample results.

LISTING 3.1 Discovery Data Sample Query

SELECT
  RV.Netbios_Name0 as 'Pc Name',
  RV.User_Name0 as 'User Name',
  AGD.Agenttime as 'Discovery Time'
FROM
  dbo.v_R_System_Valid RV
  INNER JOIN dbo.v_AgentDiscoveries AGD ON RV.ResourceID = AGD.ResourceId
WHERE
  AGD.AgentName = 'Heartbeat Discovery'
ORDER BY
  RV.Netbios_Name0

Using Hardware Inventory Classes

As the title of this section implies, data within the hardware inventory classes is obtained from hardware inventory. However, hardware inventory covers additional data, including all the data collected from Windows Management Instrumentation (WMI), the Windows Registry, and the actual hardware details for each ConfigMgr client. These inventory classes will be the main source of data for most of your reports. While it may seem counterintuitive, almost every query uses data from hardware classes.

These inventory classes also maintain history data that you can use in your reports. None of the other inventory classes maintain history data. As a general rule, there are two SQL view name identifiers for these items:

• The SQL view names that start with v_GS_* identify the latest and current hardware inventory data.

• The v_HS_* views identify history data for the hardware inventory.

Table 3.2 and Table 3.3 list hardware inventory data views.

TABLE 3.2 Current Hardware Inventory Data Views

TABLE 3.3 History Hardware Inventory Data Views

There is one very important exception to the SQL views listed in Table 3.2 and Table 3.3 that doesn’t start with v_GS_ or v_HS_—this is the v_Add_Remove_Programs view. This view provides exactly the same column information as v_GS_ADD_REMOVE_PROGRAMS and v_GS_ADD_REMOVE_PROGRAMS_64, with the data of both views combined. This view is extremely helpful for viewing both 32-bit and 64-bit Add/Remove Programs entries.

The query in Listing 3.2 lists all computers that have the software product Warranty Information Reporting v3 installed. To find a different software title, replace Warranty Information Reporting v3 with the software title for which you are looking. Figure 3.2 shows the results of Listing 3.2.

FIGURE 3.2 Hardware inventory sample results.

LISTING 3.2 Hardware Inventory Sample Query

SELECT
  RV.Netbios_Name0 as 'Pc Name',
  RV.User_Name0 as 'User Name',
  ARP.DisplayName0 as 'Application Name',
  ARP.Version0 as 'Version',
  ARP.InstallDate0 as 'Install Date'
FROM
  dbo.v_R_System_Valid RV
  INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID
WHERE
  ARP.DisplayName0 = 'Warranty Information Reporting v3'
ORDER BY
  RV.Netbios_Name0

Listing 3.3 shows a query that lists all computers where the hard drive size has changed from a previous inventory cycle. This query is a good example of how you can use the history SQL views and current hardware inventory views together in a single query. Figure 3.3 shows the results of Listing 3.3.

FIGURE 3.3 Hardware inventory history sample results.

LISTING 3.3 Hardware Inventory History Sample Query

SELECT DISTINCT
  RV.Netbios_Name0 as 'Pc Name',
  RV.User_Domain0 as 'User Name',
  GD.DeviceID0 as 'Device ID',
  GD.Size0 as 'Current HD Size',
  HD.Size0 as 'Historic  HD Size'
FROM
  dbo.v_R_System_Valid RV
  INNER JOIN v_GS_DISK GD ON RV.ResourceID = GD.ResourceID
  INNER JOIN v_HS_DISK HD ON RV.ResourceID = HD.ResourceID
WHERE
  GD.Size0 <> HD.Size0 and GD.DeviceID0 = HD.DeviceID0

Using Software Inventory Classes

Data from software inventory classes comes from the software inventory action run by the ConfigMgr client on computers, which runs every seven days by default. This inventory gathers details from individual files. In the ConfigMgr software inventory settings, you define which file types will be inventoried (for example, *.exe from all hard drives). ConfigMgr stores details from the files inventoried, such as file name, version, size, path to the file, modified date, and so on. Table 3.4 lists the software inventory data views.

TABLE 3.4 Current Software Inventory Data Views

The query in Listing 3.4 provides a count of Internet Explorer versions. Figure 3.4 shows the results of Listing 3.4.

FIGURE 3.4 Software inventory sample results.

LISTING 3.4 Software Inventory Sample Query

SELECT
  SF.FileName,
  replace(left(SF.FileVersion,2), '.','') as 'IE Version',
  Count (Distinct SF.ResourceID) as 'Total Installs'
FROM
  dbo.v_GS_SoftwareFile SF
  INNER JOIN dbo.v_FullCollectionMembership fcm ON SF.ResourceID=FCM.ResourceID
WHERE
  SF.FileName = 'iexplore.exe'
  and SF.FilePath like '%Internet Explorer%'
GROUP BY
  SF.FileName,
  replace(left(SF.FileVersion,2), '.','')
ORDER BY
   'IE Version'

Using Software Update Inventory Classes

Software update reports are among the hardest reports to write. Writing a single software update SSRS report could take from four hours to multiple weeks. The complexity of using these views is due to the way the data is stored in ConfigMgr. The data spans multiple views that are required to be joined before the information is useful to the report reader. The primary views for software updates are listed in Table 3.5.

TABLE 3.5 Software Update Data Views

Listing 3.5 shows a query that provides a count of all missing software updates for each computer in the All Systems collection in ConfigMgr. Figure 3.5 shows the results of this query.

FIGURE 3.5 Software update sample results.

LISTING 3.5 Software Update Sample Query

SELECT DISTINCT
 CS.Name0,
 CS.UserName0,
 CASE
  when (sum(case when UCS.status=2 then 1 else 0 end))>0
   then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)
   as varchar(10))+ ' Patches'))
  else 'Good Client'
 end as 'Status',
 WS.lasthwscan as 'Last HW scan'
FROM
 dbo.v_UpdateComplianceStatus as UCS
 LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM as CS
  on CS.ResourceID = UCS.ResourceID
 INNER JOIN v_CICategories_All as catall2
  on catall2.CI_ID = UCS.CI_ID
 INNER JOIN v_CategoryInfo as catinfo2
  on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
  and catinfo2.CategoryTypeName = 'UpdateClassification'
 LEFT OUTER JOIN v_GS_WORKSTATION_STATUS as WS
  on ws.resourceid = CS.ResourceID
 LEFT OUTER JOIN dbo.v_FullCollectionMembership as FCM
  on FCM.ResourceID = CS.ResourceID
WHERE
 UCS.Status = '2'
 and FCM.CollectionID = 'SMS00001'
GROUP BY
 CS.Name0,
 CS.UserName0,
 WS.lasthwscan,
 FCM.CollectionID
ORDER BY
 CS.Name0,
 CS.UserName0

Using Software Metering Inventory Classes

Data collected during the software metering inventory cycle is based on the software metering rules you create in ConfigMgr. As such, until at least one rule is created, no data appears within these SQL views. Table 3.6 lists the software metering data views.

TABLE 3.6 Software Metering Data Views

The query shown in Listing 3.6 displays the start and stop times for all software metering data beginning with a specific date, in this case February 16, 2013. Figure 3.6 displays an example of the results from executing this query.

FIGURE 3.6 Software metering sample results.

LISTING 3.6 Software Metering Sample Query

SELECT
  RV.Netbios_Name0,
  MRIB.ProductName,
  MD.StartTime,
  MD.EndTime
FROM
  dbo.v_R_System_Valid RV
  INNER JOIN dbo.v_MeterData MD ON RV.ResourceID = MD.ResourceID
  INNER JOIN dbo.v_MeterRuleInstallBase MRIB ON MD.FileID = MRIB.MeteredFileID
WHERE
    MD.starttime > '2013-02-16'

Using Status Message Classes

Although status messages are not gathered from inventory classes, they contain the details and results of ConfigMgr client actions. For example, when a deployment is sent to a ConfigMgr client, it sends a status message back the ConfigMgr server, saying that it has received the deployment notice. When the ConfigMgr client starts to download an application, the client sends a status message indicating that the download has started. Table 3.7 lists the status message data views.

TABLE 3.7 Status Message Data Views

The query shown in Listing 3.7 returns the last ConfigMgr backup for each site server. Notice that the stat.MessageID is 5035. Figure 3.7 shows the results of this query.

FIGURE 3.7 Status message sample results.

LISTING 3.7 Status Message Sample Query

SELECT
  stat.MachineName as 'Server',
  max(Time) as Time
FROM
  dbo.v_StatusMessage as stat
WHERE
  stat.Component = 'SMS_SITE_BACKUP'
  AND stat.MessageID = 5035
GROUP BY
    stat.MachineName

Using State Messages

Specific components of ConfigMgr clients use state messages to report details of a specific event, such as software updates, client health, and configuration items. State messages are broken into topic types, which identify the client component, and the StateID, which identifies a specific status for the component. Each topic type contains multiple state IDs. The v_StateName SQL view maps topic types and their respective state IDs to a descriptive state name. Popular views that use state messages are identified in Table 3.8.

TABLE 3.8 State Message Views

The query shown in Listing 3.8 returns the last enforcement message for software updates scanned by the system named GJ5. Sample results from this query are shown in Figure 3.8.

FIGURE 3.8 Software update state message sample results.

LISTING 3.8 Software Update State Message Sample Query

SELECT
  sys.Name0,
  UI.ArticleID,
  UI.BulletinID,
  UI.Title,
  sn.StateName as Status
FROM
  v_UpdateComplianceStatus UCS
  JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID
  JOIN v_R_System sys ON UCS.ResourceID=sys.ResourceID
  JOIN v_StateNames sn ON UCS.LastEnforcementMessageID=sn.StateID
WHERE
  sys.Netbios_Name0 LIKE 'GJ5'
  AND sn.TopicType = '402'
ORDER BY
  ui.ArticleID,
  sn.StateName

Using Collection Data Classes

There are several SQL views for collection classes—one for each collection within your environment. From an administrator’s perspective, however, there are only two SQL views you need to use, as shown in Table 3.9.

TABLE 3.9 Collection Data Views

Running the query in Listing 3.9 provides a list of all computer names within the All Systems collection. Sample results are shown in Figure 3.9.

FIGURE 3.9 Collection data sample results.

LISTING 3.9 Collection Data Sample Query

SELECT
  RV.Netbios_Name0,
  RV.User_Name0
FROM
  dbo.v_R_System_Valid RV
  INNER JOIN dbo.v_FullCollectionMembership FCM ON RV.ResourceID = FCM.ResourceID
  INNER JOIN dbo.v_Collection Coll ON FCM.CollectionID = Coll.CollectionID
WHERE
   Coll.Name = 'All Systems'