SCCM Metering query

UPDATE 04/10/2011: the main query ("query_metering_data") has been changed, so that it even displays computers with no metering data

In a multinational company,you have to be ready for a software audit.

The people in charge of license control go mad when they realise you have 500 computers with Microsoft Visio Professional, when you are licensed for only 450 copies !
Then you have to find 50 users which have it installed but don't need it. If you ask them , they will all say they use it every day :-)

Software Metering in Configuration Manager is extensively covered on the web, so I will spare you the introduction.

Using SCCM, I wanted to uninstall unused software on PCs  automatically, based on Software Metering data.
This can be done and is covered in forums, but my customer wanted to first check who was scheduled for removal. Imagine if you uninstalled Microsoft Project Standard from the laptop of the vice president of IT !

I developped a VB.NET application which is centered around a DataGridView, the data source being the SCCM SQL database server. Here's an overview.

First, list all the metering rules

Dim query_rules As String = "select productname from v_MeterRuleInstallBase" & _
  " GROUP BY productname ORDER BY productname"

 Double clicking a metering rule displays the metering data for this metering rule, for the collection specified.
Now here's the core of this : the SQL query to make sense of the metering data.

Dim query_metering_data = "select sys.Name0 AS Name,sys.User_Name0,mru.MeteredFileID,mru.ResourceId,MAX(TimeKey) As TimeKey, MAX(LastUsage) AS 'LastUsage' , MAX(lastseen.LastHWScan) AS 'Last hardware scan',sf.FilePath" & _
            " from v_MeterRuleInstallBase mru" & _
            " LEFT JOIN v_MonthlyUsageSummary mus ON (mru.MeteredFileID = mus.FileID AND mru.ResourceID = mus.ResourceID)" & _
            " JOIN v_r_system sys ON mru.ResourceID = sys.ResourceID" & _
            " JOIN v_GS_WORKSTATION_STATUS lastseen ON mru.ResourceID = lastseen.ResourceID" & _
            " JOIN v_fullcollectionmembership m ON mru.ResourceID = m.ResourceID" & _
            " JOIN v_GS_Softwarefile sf ON m.ResourceID = sf.ResourceID AND mru.meteredFileID = sf.FileID" & _
            " WHERE mru.ProductName = '" & Metering_rule & "'" & _
            " AND m.CollectionID = '" & TextBox_CollectionID.Text & "'" & _
            " GROUP BY sys.Name0,sys.User_Name0,mru.MeteredFileID,mru.ResourceID,sf.FilePath" & _
            " ORDER BY sys.Name0"

I've added a right click menu to remove false positives (like the $PatchCache$ path ). The list can be sorted by any column desired.

• Ensuring the last hardware scan is recent tells you the client looks OK
• The FilePath is helpful, we found people using portable applications run from USB stick were metered!
• Sort by "Last Usage" to find who has not used it the metered software for a long time.
• The desired cells can be copied to excel, or directly as Comma Separated Values with the "Copy to clipboard as CSV"

My customer uses a list of computers as a CSV file, to assign an uninstall program. This required another tool (which is not covered in this post).