Automatically create device collections based on software metering rules

I had a lot of software  at a costumer which needed to be uninstalled automatically because of expensive licencing. I stumbled upon this article from systemcenterdudes where Nicolas Pilon have created queries and collections in SCCM containing exactly what i needed. Based on that article i created a powershell script to do that for me.

Define which metered files to define collections from

To limit the script not to create collections for every metered file, I choose to “tag” a metered file by typing “Licensed” in the description field. The Name field has to be the name from ARP/Appwiz.cpl as the field will be used for the collection query by the script.

See an example here of a tagged metered file:

If the ARP name of an appliccation has an variable to the name simply use a wildcard as show below on Visual Studio:

The script

The script have some variables that can be suited to your needs. The script will create the device collection folder that you have defined in $CollectionDevFolderName

Variables:

[code language="powershell"]
#Variables
$DriveName = "SCCMSite"
$DeviceLimitingCollection = "All Systems"
$CollectionDevFolderName = "Query Rules\Software\Software Meetering"
$CollectionDevFolderPath = $DriveName + ":" + "\DeviceCollection\$CollectionDevFolderName"
$SoftwareMeteringCommentTrigger = "Licensed"
$NumberOfDaysSinceUse = 120
[/code]

Running the script on the site server will create the examples as shown:

Outcome

Here is the collections created. Running the script again will skip the creation of existing collections.

Complete code

<#

    .SYNOPSIS
   
    .DESCRIPTION

    .PARAMETER

    .EXAMPLE

    .NOTES
    Author: Morten Rnborg
    Date: 10-09-2018
    Last Updated: 03-02-2019
    https://mroenborg.com
#>

################################################
function Random-StartTime
{
	[string]$RandomHour = (Get-Random -Maximum 23) 
	[string]$RandomMinute = (Get-Random -Maximum 59)
	[string]$RandomStartTime = $RandomHour + ":" + $RandomMinute
	return $RandomStartTime
}
#Get the sitecode
Get-WMIObject -Namespace "root\SMS" -Class "SMS_ProviderLocation" | foreach-object{if ($_.ProviderForLocalSite -eq $true){$SiteCode=$_.SiteCode}} 

#Import module
Import-Module(Join-Path $(Split-Path $env:SMS_ADMIN_UI_PATH) ConfigurationManager.psd1)

#Variables
$DeviceLimitingCollection = "All Systems"
$CollectionDevFolderName = "Query Rules\Software\Software Meetering"
$CollectionDevFolderPath = $SiteCode + ":" + "\DeviceCollection\$CollectionDevFolderName"
$SoftwareMeteringCommentTrigger = "Licensed"
$NumberOfDaysSinceUse = 120

#Set location
Set-Location($SiteCode + ":") -ErrorAction Stop

Write-Host "***********************************************START SCRIPT***********************************************"
Write-Host "Running in the context of '$($env:USERNAME)'"

#Check if the collection folder exist
if (!(Test-Path $CollectionDevFolderPath))
{
    #Variables
    $Folders = $CollectionDevFolderPath.Split("\")
    $FolderPath = $null

    #Check each folder for existence
    Foreach($Folder in $Folders){

        #Define this folder path
        $FolderPath += ( $Folder + "\")
        
        #If not there, create it
        if(!(Test-Path $FolderPath.TrimEnd("\"))){
            Write-Host  ("Device collection folder `"$FolderPath`" not existing, creating it..")
            New-Item $FolderPath
        }
    }

}

#All metering rules Get-CMSoftwareInventory
$ManagedMeteringRules = Get-CMSoftwareMeteringRule | Where-Object {$_.Comment -eq $SoftwareMeteringCommentTrigger}

Foreach($Rule in $ManagedMeteringRules){

    #Variables
    $Schedule = New-CMSchedule -Start (Random-StartTime) -RecurInterval Days -RecurCount 1
    $FileName = ($Rule.FileName)
    $ProductName = ($Rule.ProductName)
    $InstalledCollName = ("$ProductName | Installed")
    $LastUseLastXDaysCollName = ("$ProductName | Used in the last $NumberOfDaysSinceUse days")
    $WarningZoneCollName = ("$ProductName | Not used in the last $NumberOfDaysSinceUse days")
    $InstalledQuery = "SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like `"$ProductName`""
    $LastUsageInLastXDaysQuery = "SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,  SMS_R_SYSTEM.Client from SMS_R_SYSTEM  inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID    INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID WHERE SMS_MeteredFiles.FileName = `"$FileName`"  AND DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < $NumberOfDaysSinceUse"
    $CollectionDescription = "Auto created"

    #Create the colleciton containing the devices having the softwre installed
    if($Collection = Get-CMDeviceCollection -Name $InstalledCollName){
        
        #The collection already exist
        Write-Host "Device collection '$InstalledCollName', already exist"
    }else{

        #Create the collection
        try {
            Write-Host "Creating device collection '$InstalledCollName' and query"
            $InstallCollection = New-CMDeviceCollection -Name $InstalledCollName -LimitingCollectionName $DeviceLimitingCollection -RefreshType Periodic -RefreshSchedule $Schedule -Comment $CollectionDescription
            Add-CMDeviceCollectionQueryMembershipRule -Collection $InstallCollection -QueryExpression $InstalledQuery -RuleName "Using '$ProductName' productname"
            Move-CMObject -FolderPath $CollectionDevFolderPath -InputObject $InstallCollection
        }
        catch {
            Write-Host ("$_")
        }
    }

    #Create the collection containing users of the software within the last X days
    if($Collection = Get-CMDeviceCollection -Name $LastUseLastXDaysCollName){
    
        #The collection already exist
        Write-Host "Device collection '$LastUseLastXDaysCollName', already exist"
    }else{

        #Create the collection
        try {
            Write-Host "Creating device collection '$LastUseLastXDaysCollName' and query"
            $LastUseXCollection = New-CMDeviceCollection -Name $LastUseLastXDaysCollName -LimitingCollectionName $InstalledCollName -RefreshType Periodic -RefreshSchedule $Schedule -Comment $CollectionDescription
            Add-CMDeviceCollectionQueryMembershipRule -Collection $LastUseXCollection -QueryExpression $LastUsageInLastXDaysQuery -RuleName "Using $FileName"
            Move-CMObject -FolderPath $CollectionDevFolderPath -InputObject $LastUseXCollection
        }
        catch {
            Write-Host ("$_")
        }
    }

    #Create the collection containing users who have not used it in the last X days
    if($Collection = Get-CMDeviceCollection -Name $WarningZoneCollName){
    
        #The collection already exist
        Write-Host "Device collection '$WarningZoneCollName', already exist"
    }else{

        #Create the collection
        try {
            Write-Host "Creating device collection '$WarningZoneCollName' including colls"
            $Collection = New-CMDeviceCollection -Name $WarningZoneCollName -LimitingCollectionName $InstalledCollName -RefreshType Periodic -RefreshSchedule $Schedule -Comment $CollectionDescription
            Add-CMDeviceCollectionIncludeMembershipRule -Collection $Collection -IncludeCollectionName $InstalledCollName
            Add-CMDeviceCollectionExcludeMembershipRule -Collection $Collection -ExcludeCollectionName $LastUseLastXDaysCollName
            Move-CMObject -FolderPath $CollectionDevFolderPath -InputObject $Collection
        }
        catch {
            Write-Host ("$_")
        }
    }
}