This chapter is from the book
This chapter is from the book
This chapter is from the book
Inside the ConfigMgr Database
The ConfigMgr database stores all information about your ConfigMgr environment. This includes information about all agent-managed clients, Intune-managed mobile devices, site information, and discovery information. ConfigMgr supports only Microsoft SQL Server for hosting its database. By default, the database is named CM_ followed by the three-digit alphanumeric code for the ConfigMgr site in question.
Microsoft does not publish the database schema, as it can change without notice. Instead, Microsoft publishes information about the views and works to keep these views static between releases.
ConfigMgr Tables and Views
ConfigMgr’s database, like any other SQL Server database, contains information in a series of tables. Tables store information in two dimensions. One dimension of each table is defined by a set of columns. The other dimension is the rows or instances of the column sets.
As discussed in the previous section, Microsoft does not publicly document these tables. SQL Server provides another method of accessing information: using views. Consider views as virtual tables; a view is essentially a query exposing data from one or more tables. Microsoft publishes information about these views, and every built-in report in ConfigMgr is based on these views. The idea is not to isolate information from customers but rather to provide a stable platform for them to build reports and solutions. In the event that a table needs to be changed, Microsoft modifies the associated view to ensure that existing reports are not impacted. Chapter 21 provides more details on views and their usage along with other methods of reporting on ConfigMgr data.
Using SQL Server Management Studio
While most day-to-day ConfigMgr administration does not require accessing the site database, it is useful to know how to view information in the database. This can be helpful for support purposes and to quickly view information to support creating scripts (or other applications) or reporting. The following steps walk you through using SQL Server Management Studio, the SQL Server administrative interface. (See Chapter 21 for additional information on SQL Server Management Studio and the basics of T-SQL.)
To access the ConfigMgr views, perform the following steps:
Open SQL Server Management Studio by going to Start -> All Programs -> Microsoft SQL Server <version> -> SQL Server Management Studio.
After the console launches, enter the name of the SQL Server hosting the ConfigMgr site database.
After the connection completes, expand the <servername>\database\CM_<site code>\views in the left-hand pane.
Exploring the ConfigMgr Database
The following sections discuss how to view various types of data stored in the ConfigMgr database and show how this information appears in the sample environment used by this book. Chapter 21 includes a more comprehensive list of various object types and their corresponding views in the database.
Collections
Let’s start with one of the most critical ConfigMgr object types, the collection. (You glimpsed the use of collections with WMI in the “WMI on Configuration Manager Servers” section, earlier in this chapter.) In WMI, the ConfigMgr collection class is named SMS_Collection. In the site database, the view is very similarly named v_Collection, with the only change in naming being the move from SMS_ to v_. (This is covered further in the tip “Finding Other Views in the Database Schema Dynamically,” later in this chapter.) Figure 3.15 shows what v_Collection looks like. These columns match up nicely with the information you see when looking at SMS_Collection properties. You will see this pattern repeated frequently when moving between WMI and SQL.
)
FIGURE 3.15 v_Collection displayed using SQL Server Management Studio.
Figure 3.15 shows the CollectionID column as the unique (within the hierarchy) identifier for the collection. This is an important column when you want to link v_Collection to other views. You can also use this column with v_FullCollectionMembership to return devices or users in a given collection.
In addition, there are views created dynamically for each collection, named v_CM_RES_COLL_<collectionID>. For example, the view v_CM_RES_COLL_SMS00001 represents the default All Systems collection. As you create collections, more of these dynamic collections are created, each containing the member users or devices of that collection. This is useful when you are writing scripts or queries that need to access collection members and you need to use a specific collection every time. If you need to parameterize, or take the collection as input to the script/query you are writing, using v_FullCollectionMembership provides a better view.
Hardware Inventory
Hardware inventory views are interesting for several reasons. They follow a similar naming standard as collection views, where their WMI name (for example, SMS_G_System_PC_BIOS) has the SMS_G_System turned into v_GS (for example, v_GS_PC_BIOS). These views are also dynamically created and modified in several different scenarios. When processing hardware inventory for a newly added class, when adding a new property, or when the value of property is too long to fit into the existing column for the property in the site database, SMSExec automatically alters the table schema to fit the new incoming data. The corresponding view to that table is created or modified to accommodate the newly defined table definition.
Client Settings
Client settings are also found in the site database views and stored in v_SMSMClientAgentConfig_Base. Note that settings are quite often stored in XML-typed columns in the client settings view. This can make reporting or reading a query difficult in some instances. SQL Server natively supports handling XML data in T-SQL queries; Listing 3.2 shows an example of how to extract the endpoint protection definition update fallback source and order it using the T-SQL CROSS APPLY command.
LISTING 3.2 Extracting the Endpoint Protection Definition Update Fallback Source
SELECT AMSettings.Name, CliSettings.PropertyName,
AMSettingsValue.FallbackSource.value('.', 'nvarchar(max)') AS 'FallbackSource'
FROM vSMS_AntimalwareSettings AS AMSettings
JOIN vSMS_ClientAgentConfig_Base AS CliSettings
ON AMSettings.ID = CliSettings.SettingsID
CROSS APPLY CliSettings.XmlValue.nodes('/StringArrayXML/Value') AS
AMSettingsValue(FallbackSource)
WHERE CliSettings.PropertyName = 'FallbackOrder'
This should return something similar to the information shown in Figure 3.16, with readable information for each endpoint protection policy defining the fallback order to use.
)
FIGURE 3.16 Query results showing how XML data is presented when handled via T-SQL.
LISTING 3.3 Searching the Definitions of All Database Views for a Particular String
DECLARE @varObjectToSearchFor varchar(max) SET @varObjectToSearchFor = '%ResourceID%' SELECT udf.name AS [Name] FROM sys.objects AS udf LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id WHERE (udf.type = 'V') AND (ssmudf.definition LIKE @varObjectToSearchFor OR smudf.definition LIKE @varObjectToSearchFor) ORDER BY [Name] ASC