One of the coolest ‘features’ of inventory is the fact that it logs information to the database when it processes the files. Granted, I’m biased on this opinion because I helped make it happen. Unfortunately, I haven’t seen anyone talk about it yet so it’s either not as cool as I thought or people don’t really know about it. So, in hopes that it’s the latter, here’s a quick blog on it. 🙂
The “InventoryLog” table only exists on the primary sites, so if you’re in a hierarchy you’ll have to query the primary(ies) directly instead of querying the CAS. However, I do have a user voice to get it added to replication so if you find it useful go ahead and vote it up here
If you look at the distinct “LogText” items on your server you’ll see the type of data that is being stored. Obviously, your records will be different than the following sample:
So, we could use this information to know how often a particular machine is having issues or to know how often the system is forcing resyncs, or to look for trends in these issues.
For example, about a year ago I created a report that grouped the records in this table by week and got counts of the issues to see if we could see what the biggest buckets were. I wrote the query to run on the CAS and use linked servers to the primaries so I could also group the records by primary site. Here’s what that looked like back then:
As you can see, I immediately picked up on the “Exceed MIF size limit. Discard” records. The numbers weren’t the highest but it’s a pretty straightforward issue so decided to look at that first. The first table shows the total of all sites for the last four weeks and the second table shows the counts by primary site for the last week – highlighting the cell that has the highest counts
It was apparent that some sites were worse than others – so I ran a PowerShell script to check the “Max MIF Size” of all the sites. Here was the output:
Those values are in MB. And, unfortunately for us, we did not have a consistent size across all sites so the ones with the lowest values obviously were the problem sites. We can get some pretty big MIFs due to some of the noisy classes we collect – such as “Recently Used Apps”. So, we decided to increase our Max MIF Size on all sites to 50MB (to account for the largest file size we had received).
And then we monitored…obviously, by using the “InventoryLog” table:
Note: these pictures are taken from old status reports…hence my comments as well.
And, a couple weeks later:
I realize that was a very simple example, but a real-life example nonetheless.
For some of the issues you can find more details in the “LogDetail” column. For example:
In this case, we see that for the issue “Non-existent row. Resync” it collects the procedure which first hit the error. In this case, the procedure didn’t find a record for the machine/instance so it couldn’t do anything with the data. And to mitigate the risk that SCCM does not have all the data from the client, the server would request a resync from the client.
Other issues (items in the “LogText” column) may have a “LogDetail” that has the machine’s GUID, something else that helps (or doesn’t 🙂 ) or you may need to look at “ServerReportVersion” and “ClientReportVersion” to get more info. For example, if you see a “LogText” of “Different major. Resync” that means that the version in the report (MIF) sent by the client was not the same as the one the Server was expecting. In this case it will have the client resync to make sure we are not out of sync with the client.
To actually see what the client sent and what the server expected for the major version you have to divide the value by 4294967296. If it were a different minor you’d have to get the mod of the value and the same value. That, or you could just use the view “vInventoryLog”. 🙂
SELECT LogID -- Primary Key! ,MachineID ,LogTime AS [LogTimeUTC] ,CONVERT(int,ServerReportVersion/4294967296) AS [ServerMajor] ,ServerReportVersion%4294967296 AS [ServerMinor] ,CONVERT(varchar(5),CONVERT(int,ServerReportVersion/4294967296))+'.'+CONVERT(varchar(5),ServerReportVersion%4294967296) AS [ServerVersion] ,CONVERT(int,ClientReportVersion/4294967296) AS [ClientMajor] ,ClientReportVersion%4294967296 AS [ClientMinor] ,CONVERT(varchar(5),CONVERT(int,ClientReportVersion/4294967296))+'.'+CONVERT(varchar(5),ClientReportVersion%4294967296) AS [ClientVersion] ,LogText ,LogDetail FROM dbo.InventoryLog WHERE LogText = N'Different major. Resync';
Hopefully now that you know about this log table you’ll be able to use it in your investigations. If you do, please share how it was helpful!