Share via

WQL to SQL

  • Article

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:

smsprov.log screen shot

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.

Comments

  • Anonymous
    February 08, 2016
    Thanks Mike. Do all actions from the console get translated here in this log, or is there a way to increase the verbosity to see all actions in the console translated into SQL language here?
  • Anonymous
    February 09, 2016
    Almost all UI actions are, unless they are something that doesn't interact with the SMS provider like task sequence media creation.