SCCM 2012 Reporting for dummies: How to use SQL Management Studio to build your SCCM Queries

k, so in the last blog I said that SQL and WQL are a lot alike. In this quick blog I’m going to talk about how to create a query for your SCCM data using views. It means having access to the SQL database but it’s a nice shortcut if you’re looking to construct a query to pull up specific data that would otherwise take a long time to find through the SCCM. I would put this tutorial as a moderately difficult one and it only becomes more useful once you learn more about the guts of SCCM. This is because you’ll require some time navigating around the tables and views to get the data you want.

Since we’re working with views and not committing any changes to the database (we’re not going to edit the data) it should not cause any issues to work within SQL management studio on the database.

The reason why it is easier to build queries this way is because you are able to view the individual views to see if they are indeed the correct views that contain the data you are looking for. Once you know which views you wish to use, you can then refine and constrain the data in your final query.

Firstly, open up SQL management studio and open up your SCCM 2012 database. It should be under Databases > CM_[SITECODE] where [SITECODE] is the site code used for the site the data is stored in.

You’ll need to then expand the Views node to see all the views that make the data stored in the configuration manager database more readable.

A nice trick if you’ve worked with Views before or have a specific view you would like to look up is the ability to filter the views. Right click the Views node and hover on Filter then click on Filter Settings.

You’ll notice that you can filter by Name, Schema, Owner or the Creation Date of that view. Cancel this for now.

I’m here to show you how to quickly create views to see what tables may have the data you require in it and what you need to do to help you quickly build queries so right click on the Views node and click on New View…

This should open up an Add Table form. From here you can select the Tables orViews you require.

We’re going to create a view that shows all machines that have a specific chassis type (desktops) so we need to add in “v_R_System” and “v_GS_SYSTEM_ENCLOSURE” views by finding them in the Views tab and then clicking Add. You’ll notice that these views then get added into the visual query builder.

Now that those views are added in, we’ll create a join between those two views on a specific attribute which is “ResourceID”. If you are familiar with SQL, this will ensure that the data between the two views are consistent and correct. We do this by dragging the “ResourceID” attribute from the v_R_System view on top of the “ResourceID” attribute from the v_GS_SYSTEM_ENCLOSURE view. You’ll get a pipe icon that will show this link as well as the query builder below will change to reflect how your query will run.

In the last tutorial, this would have represented the “Join” tab in the query properties.

So now we have a query that comprises of two views, both joined together by the ResourceID.

We’ll now need to select what information we want shown in our query so we only pull back data we want to see on the machines that are desktops. This is the easy part. Just check the checkbox on the attributes you want pulled back.

From the v_R_System view, we’re going to check ResourceID, ResourceType,Client0, Name0, Resource_Domain_OR_Workgr0 and SMS_Unique_Identifier.

From the v_GS_SYSTEM_ENCLOSURE view, we’ll check ChasisTypes0.

You should now notice the query builder dynamically filling up every time you make a change to the visual query builder.

As per our last tutorial, this would be where you would constrain the data you see on the “General” tab of the query properties.

We now have a query that selects specific attributes to show from the two views. We just need to add one more constrain and that is to only show desktops.

We can do this by navigating to the ChassisTypes0 row and putting in the value of “3” for desktops in the filter column. This will filter the results to show only those that are equal to 3 in the ChassisType0 field. For a full list of what the chassis types correspond to, check out this list from technet:

http://blogs.technet.com/b/breben/archive/2009/07/21/chassis-type-values-in-sccm.aspx

This is similar to the “Criteria” tab in the previous tutorial for the query properties.

And voila! We’ve now constrained the data to show only desktops in your SCCM database from those two tables.

We can confirm this by right clicking on either the query builder or visual query builder and choosing to execute SQL. This will then show in the results pane below.

Now don’t go plugging this query into the “Show Query Language” area for your query properties or create query wizard because it won’t work. Why?

The query we’ve just build is a SQL query. Not a WQL query. SCCM uses WQL, we just used SQL management studio to build a SQL query.

Now why have we gone through building a SQL query when we needed to build a WQL query for SCCM? Because the two are very similar. In fact, all we really need to do is make a few tweaks to the query and you should be good to go! Here are the tweaks I’ve made to our query:

Before

SELECT dbo.v_R_System.ResourceID,dbo.v_R_System.ResourceType,dbo.v_R_System.Name0,dbo.v_R_System.Resource_Domain_OR_Workgr0,dbo.v_R_System.SMS_Unique_Identifier0,dbo.v_R_System.Client0,dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
FROM dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = N’3′)

After

SELECT SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
FROM SMS_R_System
INNER JOIN SMS_G_System_SYSTEM_ENCLOSURE ON SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = “3″

Just pop that into your SCCM “Show Query Language” when creating or editing a query/collection and off you go!