EmailRSS

Creating SCCM Collections for All BIOS, Models, and Applications

Posted on by

The script, below, has been getting a lot of use lately in a few environments. I thought it might be useful in your SCCM environment. The core of the script will allow you to take any series of inputs and create SCCM collections for each one.

Here are three ways that I’ve used it recently.

To query SCCM for a list of all BIOS versions in an environment. You can then use those collections to target BIOS updates to or to follow up on machines that are stuck on older versions.

Building on the same point above, this script can be used to create collections for every hardware model so that driver updates can be specifically targeted.

Finally, and in an unusual case, it was used to create a collection for every application in the environment. The collections list the computers with that installed application. This was used to remove obsolete programs, verify licenses, and generally clean up the devices.

The SQL query and scripts for these three scenarios are at the end of this article. But first, the main script!

SCCM PowerShell Script to Create Device Collections from a CSV

To start, you will need a list of inputs – normally in a CSV (you could modify the first line to query SCCM directly). Each line in the CSV should contain what you are looking for. It will also be used to build the collection query.

Next, you will need to change the following variables. These control what the collections look like:

  • $Schedule : Sets the update interval
  • $CollectionFolder : Controls where the collections are placed,
  • $LimitingCollection : Sets the limiting collection. 🙂
  • $Collection : Defines the name and query. Create a test collection in SCCM first and then copy its query into this line.

With all of that, you can customize and run this script. Your end result will be dynamically generated collections built from virtually anything that you can query with SCCM. Just do yourself a favor, don’t see all of these to incrementally update. 🙂

$Inputs = import-csv ".\allsoftware.csv"

#Connect to SCCM
    #Load Configuration Manager PowerShell Module
    Import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1')

    #Get SiteCode
    $SiteCode = Get-PSDrive -PSProvider CMSITE
    Set-location $SiteCode":"

    #Error Handling and output
    Clear-Host
    $ErrorActionPreference= 'SilentlyContinue'
    $Error1 = 0

#All Collection Settings
    #Refresh Schedule
    $Schedule = New-CMSchedule –RecurInterval Days –RecurCount 7


#Create Collections From Inputs
    foreach ($Input in $Inputs){
    $Input = $Input | select -ExpandProperty Inputs

    #Collection Query
    $Collection = @{Name = "Application Query | $Input "; Query = "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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like '$Input' or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like '$Input'"}

    #Create Default Folder 
    $CollectionFolder = @{Name = "Query"; ObjectType = 5000; ParentContainerNodeId = 0}
    Set-WmiInstance -Namespace "root\sms\site_$($SiteCode.Name)" -Class "SMS_ObjectContainerNode" -Arguments $CollectionFolder

    #Create Default limiting collections
    $LimitingCollection = "All Systems"

    #Create Collection

    New-CMDeviceCollection -Name $Collection.Name -Comment "All devices with with installed software $Input" -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
    Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Collection.Name -QueryExpression $Collection.Query -RuleName $Collection.Name
    }

SCCM SQL Query to See All Installed Applications

DECLARE @CollID AS VARCHAR(8);
SET @CollID = 'SMS00001'

SELECT  DisplayName0 As 'Product Name'
FROM  v_Add_Remove_Programs As ARP 

JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID  

WHERE  FCM.CollectionID = @CollID 
GROUP BY DisplayName0
ORDER BY DisplayName0

PowerShell Script to List all Device Models in SCCM

See this script: https://github.com/SCConfigMgr/ConfigMgr/blob/master/Client/Get-CMDeviceModels.ps1

PowerShell Script to List all BIOS Versions in SCCM

Grab the script, linked directly above. Change the $ComputerSystem variable to:

Get-WmiObject -Namespace "root\SMS\site_$($SiteCode)" -Class SMS_G_System_PC_BIOS | Select-Object -Property SMBIOSBIOSVersion

Leave a Reply