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).

SCCM Package replication status gadget for Windows 7 Sidebar

Making sure your packages are replicating correctly in your SMS hierarchy is important.
Sometimes, you even want to know as soon as possible when a package has been replicated!
This has been useful when a new office is being build far away, and I want to inform the fields technicians on site when they'll be allowed to push out software.

Therefore, I present the "PackageStatusDetailSummarizer" gadget for Windows 7 Sidebar!

>>DOWNLOAD<<


It's like  the "Package Status" view in the "Configuration Manager Console", just neater in a gadget :-)

Inspiration from

• "Software Updates" gadget of the Configuration Manager SDK Samples
• The WMI class SMS_PackageStatusDetailSummarizer from the SMS SDK
• Windows Sidebar
• Windows Sidebar Object Reference

To install in Windows 7:

1. drop the folder "PackageStatusDetailSummarizer.Gadget" in %userprofile%\appdata\Local\Microsoft\Windows Sidebar
2. right click on your desktop, choose "Gadgets", you should see it in the list

3. right click, "install"
4. configure by setting the options, just like a normal gadget
5. Be careful with the refresh interval in the options, if you set it to low you can hurt your site's performance.

If you want to customize it,just close the gadget and edit the files in the folder you have copied (see the "Windows Sidebar" link)
The possibilites are endless : check the deployment of packages,advertisements, software updates, site health etc..

SCCM : converting programs to Windows 7

This is a VBS program I wrote for a customer, who has a SCCM 2007 R2 environment.

They had over 400 programs in various packages, and they weren't sure if all of them would run under Windows 7.

Visually, we had to make sure this box was ticked.

After a little digging in the SCCM SDK (link), and a script from Stuart James, the following will go through ALL programs in ALL packages. If a program is not set to "Run on all platforms", we add that it can run on "All x86 Windows 7"

It outputs changes with comma separated values (csv) so you can send this to a file and dress it up nice in Excel for your boss :-)

'===================================== 
'SetRunFromTS - Sets all programs to be able to run in Windows 7 x86
'Author: Patrick Paumier / Stuart James 
' 
'Requirements: Change line 22 to connect to your site server
' 
'Usage: CScript xxx.vbs or double click 
'===================================== 


'Check we're using CScript and if not then relaunch 
If "CSCRIPT.EXE" <> UCase(Right(WScript.Fullname, 11)) Then 
Set WshShell = WScript.CreateObject("WScript.Shell") 
WshShell.Run "CSCRIPT.EXE /nologo " & WScript.ScriptFullName 
Wscript.Quit 
End If 

' Setup a connection to the local provider. 
Set swbemLocator = CreateObject("WbemScripting.SWbemLocator") 
Set swbemServices= swbemLocator.ConnectServer("MY-SCCM-SERVER", "root\sms") 
Set providerLoc = swbemServices.InstancesOf("SMS_ProviderLocation") 

For Each Location In providerLoc 
If location.ProviderForLocalSite = True Then 
Set swbemServices = swbemLocator.ConnectServer(Location.Machine, "root\sms\site_" + Location.SiteCode) 
Exit For 
End If 
Next 

'Main call 
QueryPrograms swbemServices 

Sub QueryPrograms(connection) 

On Error Resume next 

Dim programs 
'Dim program 
' Run the query. 
Set programs = connection.ExecQuery("Select * From SMS_Program WHERE PackageID='TDE0024F'") 

If Err.Number<>0 Then 
Wscript.Echo "Couldn't get programs" 
Wscript.Quit 
End If 
For Each program In programs 
ModifyProgram connection,program.PackageID, program.ProgramName 
Next 
If programs.Count=0 Then 
Wscript.Echo "No packages found" 
End If 

End Sub 

Sub ModifyProgram (connection, existingPackageID, existingProgramName) 

' Build a query to get the specified package. 
packageQuery = "SMS_Package.PackageID='" & existingPackageID & "'" 

' Run the query to get the package. 
Set package = connection.Get(packageQuery) 
' Output package name and ID. 
wscript.echo VBCrLf 'New Line
Wscript.StdOut.Write package.PackageID & "," 'Package ID
Wscript.StdOut.Write package.Name & "," 'Package Name
' Build a query to get the programs for the package. 
programQuery = "SELECT * FROM SMS_Program WHERE PackageID='" & existingPackageID & "'" 
' Run the query to get the programs. 
Set allProgramsForPackage = connection.ExecQuery(programQuery, , wbemFlagForwardOnly Or wbemFlagReturnImmediately) 
'The query returns a collection of program objects that needs to be enumerated. 
For Each program In allProgramsForPackage                
If program.ProgramName = existingProgramName Then 
'Get all program object properties (in this case we specifically need some lazy properties).
programPath = program.Put_
Set program = connection.Get(programPath) 

' Output the program name 
Wscript.StdOut.Write program.ProgramName & "," ' Program Name
Wscript.StdOut.Write program.ProgramFlags & "," 'Program Flags

If program.ProgramFlags AND 2^27 Then
Wscript.StdOut.Write "OK for all platforms"
Else
' RUN_ON_SPECIFIED_PLATFORMS is set.
Win7OK = FALSE
For Each myOS in program.SupportedOperatingSystems
osver = Left(myOS.MinVersion,3)
If osver="6.1" And myOS.Platform="I386" Then
Wscript.StdOut.Write "OK for Windows 7" ' Name: " & myOS.Name & " MinVersion: "& myOS.MinVersion & " MaxVersion: " & myOS.MaxVersion & " Platform: " & myOS.Platform
Win7OK = TRUE
Exit For
End If
Next
If Win7OK = FALSE Then
'Add Windows 7 32bit platform
' Create 
Set tempSupportedPlatform = connection.Get("SMS_OS_Details").SpawnInstance_
' Populate tempSupportedPlatform values.    
tempSupportedPlatform.MaxVersion = "6.10.9999.9999"
tempSupportedPlatform.MinVersion = "6.10.0000.0"
tempSupportedPlatform.Name       = "Win NT"
tempSupportedPlatform.Platform   = "I386"

' Get the array of supported operating systems.
tempSupportedPlatformsArray = program.SupportedOperatingSystems   

' Add the new supported platform values (object) to the temporary array.
ReDim Preserve tempSupportedPlatformsArray (Ubound(tempSupportedPlatformsArray) + 1)
Set tempSupportedPlatformsArray(Ubound(tempSupportedPlatformsArray)) = tempSupportedPlatform

' Replace the SupportedOperatingSystems object array with the new updated array.
program.SupportedOperatingSystems = tempSupportedPlatformsArray

' Save the program.
program.Put_

' Output success message.

Wscript.StdOut.Write "Added Win7"
End If
End If
End If        
Next 
End Sub