Querying and Reporting Data in Microsoft SCCM 2012 R2 by David Papkin

This post by David Papkin Querying and Reporting Data in Microsoft SCCM 2012 R2 Configuration Manager

 

Microsoft® System Center 2012 R2 Configuration Manager stores a large amount of data about the devices in your environment. You may not always want to perform all management tasks on all of the devices simultaneously. Therefore, to help you locate devices in your environment that meet specific criteria, you can create queries. You then can use these queries to create collections or to find clients and install the Configuration Manager client on them. This post describes queries and the methods to create and run them. Additionally, you may want to run reports about your environment. To be able to run Configuration Manager reports, you have to install a reporting services point, which this module details.

Introduction to Queries

In any database query, you define the information that you want to obtain in the form of a query statement. The query engine then searches the database for items that match your criteria. Finally, the query result displays the data that matches your criteria. Queries in Configuration Manager also operate in the same manner. In this post, you will learn that queries in Configuration Manager consist of optional and required components that result in queries ranging from simple to complex.

What Is a Query?
A query is a specific set of instructions that extract information about a defined set of objects. You can use a query in Configuration Manager to obtain almost any information from the site
database. This includes items such as specific types of computers, user groups, sites, collections, and applications. This means that you can also query your database for information such as the number of clients that have free space of less than 50 megabytes (MB) and the number of clients in a particular site. One caveat is that the inventory information in the database is as current as the last inventory cycle. You may run a particular query to locate a computer, which may have changed since the last inventory and therefore no longer meets the criteria of the query.You build queries in Configuration Manager on the WMI Query Language (WQL), which is based on Windows Management Instrumentation (WMI). WMI is similar to Structured Query Language (SQL). You can use preconfigured queries or create your own custom queries to search the site database. When creating custom queries in Configuration Manager, you can use the built-in wizard in design mode to choose the components of your query, or you can use the wizard in the query language mode to type your own WQL queries. Although design mode provides an easier interface to use when creating queries,
you cannot create all queries by using design mode. For instance, when using aggregation commands in WQL, you can see and manage only the query in query language mode.
Note: You also can use WQL to query WMI data from local computers that run a Windows® operating system. For more information about WQL, see

http://go.microsoft.com/fwlink/?LinkId=389318.

You can perform two types of queries in Configuration Manager:
• Data queries. You can use data queries for extracting information that relates to resource discovery or inventory data. In general, the primary purpose of data queries is to build collections.
• Status message queries. This type of query has a very specific use. The Site Status and Component Status nodes show you status messages that relate to a specific site system or component. Although there are some filtering options, these may not be sufficient when troubleshooting an issue. Therefore, you can use status message queries to create custom queries that return status messages, including from clients. The primary purpose of status message queries is to locate stored status messages.
Objects and Attributes
You can use queries in Configuration Manager to search the site database for any object. It is
important for you to know how the database stores information about the objects. All objects
have attributes and values that you can query; however, not all objects have the same attributes and values. For example, both user resources and system resources have a name; however, user resources do not have installed software.

Objects and Attributes

Objects and Attributes

Every object type is defined by a set of attribute classes, which are further defined by individual attributes. For example, the System Resource object type is defined by attribute classes such as processor, disk drives, and installed software, which together characterize the discovery data and inventory data of a system resource. These attribute classes have their own unique attributes. The attributes define the values stored in the database, such as current clock speed for processors or partitions for disk drives.Most object types, such as the Site object type, have only one attribute class and few attributes, whereas the System Resource object type has more than 200 attribute classes and thousands of attributes. Attribute classes are directly related to SQL Server tables and Web-Based Enterprise Management (WBEM) classes. In database terms, the attribute class represents a table, the attributes represent the column headers, and the actual data collected is stored in the rows.Queries search against only one object type at a time. By default, Configuration Manager queries the System Resource object type.
Configuration Manager has 27 object types. Some of the object types include Application Conflict Data, Deployment Asset Details, and Security Roles.

 

This concludes the post by David Papkin Querying and Reporting Data in Microsoft SCCM 2012 R2 Configuration Manager