Chapter 3. Understanding Configuration Manager Data
In This Chapter
Using Data Classes and SQL Views
Using Tools for Creating Reports
Demonstrating SQL Server Management Studio
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.
Tip: Changing the Heartbeat Discovery Cycle to Daily
To increase the reliability of the data captured by ConfigMgr, you can change the heartbeat discovery cycle from 7 days (default) to daily. This change generally has no adverse effect.
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.
Note: SQL Source Files for the Listings in this Chapter
The SQL queries included in this chapter are available as online content. See Appendix C, “Available Online,” for additional information.
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.
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.
Tip: Changing Hardware Inventory Cycle
To increase the reliability of the data captured by ConfigMgr, the authors recommend changing the hardware inventory cycle from 7 days (default) to daily. This change generally has no adverse effect on ConfigMgr server or client performance.
Although there are no hard-and-fast rules about setting the hardware inventory schedules, most ConfigMgr MVPs and senior consultants make the same recommendation for setting hardware inventory to daily. The authors estimate that there is 10% increase in database size and a minimal increase in CPU usage and disk input/output (I/O) when this change is made.
However, the value to an organization of having the most up-to-date inventory in most cases outweighs these minimal increases to database size, CPU usage, and disk I/O. As with any other changes to ConfigMgr, the authors recommend that you evaluate the value and impact to your company before making this change.
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.
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.
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.
Note: Impact of Using the Software Inventory Data Views
Try to avoid using the software inventory classes. You might think that based on their name, these would be the most popular set of SQL views, but this is not the case. There are many reasons, in the authors’ opinion, why these views are not popular, but the most important is that the process of gathering the data to populate these SQL views is extremely slow, and clients will notice their hard drive being accessed for extended periods of time during the data-gathering process. Unlike the hardware inventory classes or the heartbeat discovery class, which take less than four minutes to run without clients even noticing that the inventory process is running on their computer, the software inventory classes can run for hours and hours.
The query in Listing 3.4 provides a count of Internet Explorer versions. Figure 3.4 shows the results of Listing 3.4.
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'
Tip: Changing the Software Inventory Cycle
The authors recommend turning off the software inventory cycle due to its overhead. However, if you keep it on, change it from 7 days (default) to run every 14 to 21 days in order to improve performance.
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.
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.
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.
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.
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'
Tip: Creating Software Metering Rules
The authors recommend creating software metering rules for any software that has a significant cost and is not installed in the base image of your computers. It is best to create these rules as early as possible, as it takes approximately 75 days to get useful data from software metering reports.
It is not necessary or ideal to create software metering rules for products installed on every computer in your environment, such as Microsoft Word, due to the amount of overhead and data collected by the software metering Inventory.
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.
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.
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.
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.
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
Tip: State Messages
Refer to https://technet.microsoft.com/en-us/library/bb932203.aspx for a list of topic types and their state messages reported by ConfigMgr clients. Note that although the link is for ConfigMgr 2007, the information also applies to later versions of ConfigMgr.
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.
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.
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'
Using Tools for Creating Reports
This section of the chapter explores the SQL Server Management Studio tool. This is the first of three major tools you can use to create reports for ConfigMgr. Chapter 1, “Installing SQL Server Reporting Services,” detailed the steps for installing SQL Server Management Studio 2014.
Chapter 6, “Building a Basic Report,” explains the two remaining tools:
SQL Server Data Tools Business Intelligence (SSDT-BI) for Visual Studio 2013
These tools are used to design SSRS reports for ConfigMgr as well as other System Center products, such as Operation Manager and Service Manager.
Introducing SQL Server Management Studio
Using the SQL Server Management Studio tool is the recommended way to create the queries required to build and design ConfigMgr reports. SQL Server Management Studio allows you to access, configure, manage, administer, and develop SQL Server components. It combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager with script editors to provide access to SQL Server to administrators and developers of all skill levels. SQL Server Management Studio allows you to easily find SQL views and their columns in the ConfigMgr database. Using this tool, you can also write and execute queries as well as confirm results before creating a custom ConfigMgr report. Chapter 4, “Transact-SQL Primer,” can help you become familiar with this core tool as well as how to write proper SQL queries.
Connecting to the ConfigMgr Database Server
Once the SQL Server Management Studio tool is installed on your workstation, launch it and follow these steps:
1. Connect to the database server used for ConfigMgr by entering the server name and clicking Connect (see Figure 3.10).
Tip: Problems Connecting to Database
If you do not have the permissions required to connect to the database server, either run the SQL Server Management Studio tool using the RUN AS command and specify an account with the proper access or grant your user account the proper permissions on the SQL Server database.
2. Once connected, you see the main interface of SQL Server Management Studio, as shown in Figure 3.11.
Executing a Query in SQL Server Management Studio
To create and execute a query, open a new query by clicking New Query in the SQL Server Management Studio menu bar. After the query page opens, it is important to make sure you change the database source to the ConfigMgr database. To do this, select the database from the dropdown menu, as shown in Figure 3.12.
Enter your query in the New Query page and click Execute. When you execute your query, the Results section appears at the bottom of your query page. Figure 3.13 shows an example of this section.
Using the Object Explorer
The Object Explorer, displayed on the left side of SQL Server Management Studio, provides the ability to drill down into the ConfigMgr database and get a list of all ConfigMgr views as well as the available columns that can be used within your queries. Notice that the expanded dbo.v_R_System_Valid view in Figure 3.14 contains many columns.
Tip: Invalid Object Name Error Message
When executing a query, if you receive an “Invalid object name” error message (see Figure 3.15), it usually means that you forgot to change the target database to the ConfigMgr database (as shown in Figure 3.12).
Tip: Changing the Default Database in SQL Server Management Studio
To permanently set the default SQL Server Management Studio database to be the ConfigMgr database, follow the instructions in the blog post at http://www.enhansoft.com/blog/invalid-object-name-in-sql-server-management-studio-ssms.
Demonstrating SQL Server Management Studio
The purpose of this section is to help you become familiar with the SQL Server Management Studio tool discussed in this chapter. You will create and execute a query and look at views.
1. Open SQL Server Management Studio and connect to your ConfigMgr database server. See the “Connecting to the ConfigMgr Database Server” section of this chapter for detailed information on how to perform this step.
2. Using the dropdown list of available databases, change the database to your ConfigMgr site database.
3. Open a new query by clicking New Query in the SQL Server Management Studio menu bar.
4. Open the PatchReport.txt file provided in the online content for this book (see Appendix C for information) and then copy and paste the contents to the new query page.
5. Click Execute to execute the query.
6. Review the results.
Looking at Views
Follow these steps to look at a view:
1. Using the Object Explorer, expand the Databases folder and the ConfigMgr database.
2. Drill down into Views and note the available columns for the following table views:
dbo.v_R_System
dbo.v_GS_Computer_System
dbo.v_Add_Remove_Programs
Summary
ConfigMgr gathers and stores a large amount of data across many different tables in its database. This chapter has reviewed the most common and important data classes, as well as their corresponding SQL data views, which are also stored in the database. It has also discussed how to launch and connect to the ConfigMgr database as well as execute some sample queries, using the SQL Server Management Studio tool.
Many sample queries have been provided in this chapter. Some of these queries are fairly simple and others a bit more complex. Don’t worry about trying to understand how to write them just yet! Detailed explanations and examples about how to write SQL queries will be given in later chapters.