MSEndpointMgr

How to use SMSProv.log to build reports

When working in ConfigMgr I frequently find information in the console is placed in inconvenient or inefficient locations especially when working with large chunks of information. Today I want to talk about a trick I’ve used for a long time to pull information out of ConfigMgr in a more efficient manner.

What is the SMSProv.Log

According to Microsoft’s technical reference for log files the SMSProv.Log file does the following:

  • “Records activities performed by the SMS Provider. Configuration Manager console activities use the SMS Provider”
  • “Records WMI provider access to the site database.”
https://technet.microsoft.com/en-us/library/hh427342.aspx

This is a complex way of saying, anything that any user does using the ConfigMgr console is reflected for a short period of time within the SMSProv.log file. That means almost anytime you open a collection, or look at the properties of a deployment or software update the action is reflected in the log file.

Using the SMSProv.log

So now that we know what the SMProv.log is how do we use that information to create informative and efficient reports? Let’s take a look at a real-world example, superseded updates. When looking in the ConfigMgr console at updates it’s easy to identify if an update is superseded or not but its difficult to determine what those updates are in a clear concise manner. If we crack open the ConfigMgr console we know we can get the list of updates that supersede from the console with a couple of quick clicks. First, we open the ‘Software Library’ workspace and expand the ‘Software Updates’ -> ‘All Software Updates’ section. Once we pull up some updates lets select an update in a superseded for example I’ve chosen ‘KB4015383’. If you right-click the patch and check its properties you’ll see some tabs these tabs contain information that is otherwise not available.

This information isn’t found anywhere else in the console and finding this information is shall we say inconvenient especially if you were looking for every patch. Something like this is especially important when dealing with something like WannaCrypt when you need to find all of the possible patches that could be installed to remediate a vulnerability.

OK, but what does this have to do with the SMSProv.log? If you close the properties dialogue box open the SMSProv.log and then re-open the properties of the update you’ll notice that the log will move by with some information note that if you have a lot of users accessing the console you might need to search for your username in the log entries. It will look something like this:

What you’ll notice in the second box, is the results of the WQL and SQL query that is sent to pull the information back that populates the console. This happens anytime you pull up information in the console. Taking a closer look at the ‘Execute SQL’ statement:

select  all su.LocaleID
     , cir.FromCIID
     , cir.ToCIID
     , su.ArticleID
     , su.BulletinID
     , su.DisplayName
     , su.CIInformativeURL 
from vSMS_CIRelation AS cir 
INNER JOIN fn_ListUpdateCIs(1033) AS su ON ((cir.FromCIID = su.CI_ID AND cir.ToCIID = 16819111) OR (cir.ToCIID = su.CI_ID AND cir.FromCIID = 16819111))   
where cir.RelationType = 6

Based on the above SQL statement we can now learn a few things about update relationship information within the ConfigMgr database but I think this will start getting away from the original intent if we aren’t careful.

If there is interest in the comments I’ll explain this a little more in-depth in the future suffice it to say we can use this information to track down various pieces of dependency information within the database and build a nice SQL query like this that accepts any KB and gets the associated superseded updates.

Select v_UpdateInfo.ArticleID
  , CASE
    WHEN vSMS_CIRelation.FromCIID = v_UpdateInfo.CI_ID THEN V_Updateinfo.ArticleID
    END AS 'Update that is supersceding'
  , CASE 
    when vSMS_CIRelation.ToCIID = TOF.CI_ID THEN TOF.ARticleID
  END as 'Update that was Supersceded'
from vSMS_CIRelation
Left Outer Join v_UpdateInfo on vSMS_CIRelation.FromCIID = v_UpdateInfo.CI_ID
LEFT OUTER JOIN v_UpdateInfo as TOF on vSMS_CIRelation.ToCIID =TOF.CI_ID
where vSMS_CIRelation.RelationType = '6'

Conclusion

Hopefully, this post was a helpful education on how to use the SMSProv.log to pick out what certain commands from the console are doing so that you can more easily access that data. The goal of this was to help teach you something that can be used in a variety of different ways including things like pulling apart the deployment status view in the ConfigMgr console and more! I look forward to reading the comments to see what ideas and uses you’ve come up with by leveraging information gleaned from the SMSProv.log.

(7927)

Jordan Benzing

Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.

4 comments

    • Absolutely.

      The SMSProv Log will contain both the WQL version of the query AND the SQL verison.

      -J

  • I have used this method before to help with building reports, but you do need to be careful as what appears in the log file cannot always be directly translated into workable SQL code for a report. For instance, in your example, it uses the database table ‘vSMS_CIRelation’ and this would need to be changed to just ‘v_CIRelation’ to work as the vSMS tables are not available for reporting using SSRS.

    • Correct,

      there are certain ‘views’ and tables that have to be translated around specifically for SSRS. However, SSRS is it’s own special unique animal.

      Cheers,

      -J

Sponsors