WQL to SQL

If you want to do any deep troubleshooting with System Center 2012 Configuration Manager (SCCM/ConfigMgr), or even simple custom report creating, one of the main headaches you will hit is figuring out what SQL tables and views are involved with what you are interested in.  Scouring the internet is one way, as is doing a SQL trace.  A net search is probably an easy first try.  Doing a SQL trace can be difficult, especially on an active site.  I'm going to share with you one trick that those who have been using SMS/SCCM/ConfigMgr over the years just intuitively know.  That trick is.. the SMSProv.log.

To understand the SMSProv.log you need to understand its use.  Anytime anyone clicks something in the ConfigMgr admin console that is a WMI call into the ConfigMgr Provider.  That provider does a translation from a WMI call into proper t-SQL syntax.  You can leverage that when trying to figure out what tables are involved in something you do from the UI.

For example, lets say you wanted to know what tables were involved in looking up software metering rules.  I opened my smsprov.log in cmtrace, selected the 2nd from last line, then clicked the software metering node in the UI.  A bunch of stuff entered the log below my selected line and scrolling down I find several groupings of WQL and SQL lines.  With a little practice you will find that many are about general UI navigation and can be ignored.  Sifting through that I found this:

As you can see (if you click the image to see it clearly and not in its fuzzy form..., sorry about that), there is an "execute WQL" line followed by an "Execute SQL" line.  In this case the 2nd set are the most interesting to me and I see that the main SQL view being used is vSMS_MeteredProductRule. With that knowledge I could proceed into SQL to look at what is in the table, perhaps building a report to combine it with other data.