Home > Articles

Understanding Configuration Manager Data

Learn how ConfigMgr stores its inventory data and how to find that information. Also get familiar with SQL Server Management Studio, the query writing tool.

This chapter is from the book

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:

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

SQL View



Lists all systems discovered by ConfigMgr.


Lists all users discovered by ConfigMgr.


Lists all active clients within ConfigMgr. This is a subset of v_R_System.

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

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

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

SQL View



Lists basic details about a computer, such as ­manufacturer, model, and user name.


Provides details about hard drives attached to a computer.


Provides details about 32-bit Add/Remove Programs data for computers.


Provides details about 64-bit Add/Remove Programs data for computers.

TABLE 3.3 History Hardware Inventory Data Views

SQL View



Provides history data for the basic details of a computer.


Provides history data for the hard drives attached to a computer.


Provides history data for the 32-bit Add/Remove Programs data for computers.


Provides history data for the 64-bit Add/Remove Programs data for computers.

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

  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'
  dbo.v_R_System_Valid RV
  INNER JOIN dbo.v_Add_Remove_Programs ARP ON RV.ResourceID = ARP.ResourceID
  ARP.DisplayName0 = 'Warranty Information Reporting v3'

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

  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'
  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
  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

SQL View



Provides status details about the last software scan cycle.


Provides details about all inventory files.

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

  replace(left(SF.FileVersion,2), '.','') as 'IE Version',
  Count (Distinct SF.ResourceID) as 'Total Installs'
  dbo.v_GS_SoftwareFile SF
  INNER JOIN dbo.v_FullCollectionMembership fcm ON SF.ResourceID=FCM.ResourceID
  SF.FileName = 'iexplore.exe'
  and SF.FilePath like '%Internet Explorer%'
  replace(left(SF.FileVersion,2), '.','')
   '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

SQL View



Provides compliance status details for each PC’s software update.


Provides details about software update categories.

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

  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'
 dbo.v_UpdateComplianceStatus as UCS
  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'
  on ws.resourceid = CS.ResourceID
 LEFT OUTER JOIN dbo.v_FullCollectionMembership as FCM
  on FCM.ResourceID = CS.ResourceID
 UCS.Status = '2'
 and FCM.CollectionID = 'SMS00001'

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

SQL View



Lists all gathered software metering data.


Lists all software metering rules.

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

  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
    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

SQL View



Provides status messages. This view is generally used in conjunction with v_StatMsgAttributes and

v_StatMsgInsStrings to get the complete status message information.


Lists the attributes for a status message.


Lists status messages.

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

  stat.MachineName as 'Server',
  max(Time) as Time
  dbo.v_StatusMessage as stat
  stat.Component = 'SMS_SITE_BACKUP'
  AND stat.MessageID = 5035

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

SQL View



Maps topic types and their state IDs to descriptive names.


Provides the last client health state reported by ConfigMgr clients.


Provides the evaluation state messages for assigned configuration items (CIs).


Provides the compliance state for software updates scanned by ConfigMgr clients.

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

  sn.StateName as Status
  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
  sys.Netbios_Name0 LIKE 'GJ5'
  AND sn.TopicType = '402'

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

SQL View



Lists all collections and the CollectionID of each.


Lists the membership of each collection (user account, computer, and security group).

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

  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
   Coll.Name = 'All Systems'

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020