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.”
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'
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.