ConfigMgr Console Client Activity

April 8, 2024 by Jim Webb

Problem

Client has reported a HW inventory. HW info is blank under ‘Client Activity’ in ConfigMgr console.

Reason

The stored procedure that updates that view runs once every 24 hours and hasn’t ran since the client submitted its inventory.

Fix

Wait, or run the stored procedure CH_SyncClientSummary using the SQL code listed below.

SQL Code

Get the client’s last inventory date.

declare @RID int=0
set @RID = (Select ResourceID from v_r_system where Netbios_Name0 = 'PC001')
select * from v_gs_workstation_status where ResourceID = @RID

declare @RID int=0

set @RID = (Select ResourceID from v_r_system where Netbios_Name0 = 'PC001')

select * from v_gs_workstation_status where ResourceID = @RID

Get the client’s HW inventory date from the v_CH_ClientSummary view. In this example, the HW inventory was blank.

declare @RID int=0
set @RID = (Select ResourceID from v_r_system where Netbios_Name0 = 'PC001')
select * from v_gs_workstation_status where ResourceID = @RID

declare @RID int=0

set @RID = (Select ResourceID from v_r_system where Netbios_Name0 = 'PC001')

select * from v_gs_workstation_status where ResourceID = @RID

Run the stored procedure to update the view. After the stored procedure has ran, we re-run the query above to get the client’s HW inventory date from the view.

declare @return_value int
exec @return_value = [dbo].[CH_SyncClientSummary]
select 'Return Value' = @return_value

declare @return_value int

exec @return_value = [dbo].[CH_SyncClientSummary]

select 'Return Value' = @return_value

Backup SSRS Reports

January 29, 2022 by Jim Webb

If you need to backup your SSRS reports you can download each report individually, or use PowerShell to backup all of them quickly.

The information in this post was taken from the following articles:
https://www.recastsoftware.com/resources/how-do-you-backup-all-of-your-custom-configmgr-reports/
and
http://www.sqlmusings.com/2011/03/28/how-to-download-all-your-ssrs-report-definitions-rdl-files-using-powershell/

The website that the Recast article referrers to is no longer available and is only accessible via the web archive link given in the article. This post is so the script is easily available for later use.

The script has been updated and is available on GitHub.

The script requires two parameters, the SSRS FQDN, and the directory save the reports to. If the directory doesn’t exist, the script will create it.

The script assumes that HTTPS has been enabled on the SSRS site. If HTTPS has not been enabled, the script will need to be modified to switch back to HTTPS.

Backup-SSRSReports.ps1 -ReportServer "cm01.corp.viamonstra.com" -BackupDirectory C:\Temp\ReportBackup

1	Backup-SSRSReports.ps1 -ReportServer "cm01.corp.viamonstra.com" -BackupDirectory C:\Temp\ReportBackup

A progress bar is used to show progress.

Reports have been backed up to the location passed to the script.

Working with ConfigMgr folders

January 29, 2022 by Jim Webb

When working with ConfigMgr and PowerShell you’ll need to interact with folders.

Get folder
Create folder
Delete folder

Pre ConfigMgr 2111

The folder structure can be accessed using the site code as a PSDrive. For example, PS1:\.

Each workspace and node have different folder structures within them. To see the options use the command below.

The folder structure used in PowerShell doesn’t always match the folder structure in the console. Generally, the console shows the items using the plural of the word, while PowerShell uses the singular of the word. In the console it’s Applications, while in PowerShell it’s Application.

get-item PS1:\*
Application
BootImage
ConfigurationBaseline
ConfigurationItem
DeviceCollection
Driver
DriverPackage
OperatingSystemImage
OperatingSystemInstaller
Package
Query
SoftwareMetering
SoftwareUpdate
TaskSequence
UserCollection
UserStateMigration
VirtualHardDisk
SmsProvider

get-item PS1:\*

Application

BootImage

ConfigurationBaseline

ConfigurationItem

DeviceCollection

Driver

DriverPackage

OperatingSystemImage

OperatingSystemInstaller

Package

Query

SoftwareMetering

SoftwareUpdate

TaskSequence

UserCollection

UserStateMigration

VirtualHardDisk

SmsProvider

List folders

To list a folder use the following:

Get-Item PS1:\Application\* | select Name

Name
----
PatchMyPC
Test

Get-Item PS1:\Application\* | select Name

Name

----

PatchMyPC

Test

Create folder

To create a folder use the following:

New-Item PS1:\Application\SuperCoolApplications

1	New-Item PS1:\Application\SuperCoolApplications

Remove folder

To remove a folder use the following:

remove-item PS1:\Application\SuperCoolApplications

1	remove-item PS1:\Application\SuperCoolApplications

Post ConfigMgr 2111

ConfigMgr added four new cmdlets to deal with folders:

Get-CMFolder
New-CMFolder
Remove-CMFolder
Set-CMFolder

Get-CMFolder

New-CMFolder

Remove-CMFolder

Set-CMFolder

To be continued…

Creating collections based on boundary groups

December 4, 2021 by Jim Webb

Full disclosure, the script below is based on the script by Jonathan Lefebvre at System Center Dudes.

The script below will create a folder structure “Operational\Boundary Groups based collection”. If a collection already exists, a message is disabled on the screen, otherwise, the collection is created and move to the folder.

The goal of the changes is to make it so the script can be scheduled to run nightly or weekly. This makes the creation of collections based on boundaries no longer manual.

#############################################################################
# Author  : Jonathan Lefebvre-Globensky
# Website : www.SystemCenterDudes.com
# Twitter : @jlefebvregloben, @SCDudes
#
# Version : 1.0
# Created : 2020/04/23
# Modified : 
#
# Purpose : This script create collections based on each found Boundary groups in an environment.
# Blog post related : 
# Previous script to create collections with folder structure :https://www.systemcenterdudes.com/powershell-script-create-set-maintenance-collections
# Want more default operationnal collection? See Benoit Lecours powershell script --> https://gallery.technet.microsoft.com/Set-of-Operational-SCCM-19fa8178
#
#
# Updated by: Jim Webb
# Modified date: 12/3/21
#
#############################################################################

#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":"

#Set the prefix for collections to be created
$CollectionPrefix='BG - '

#Define possible limiting collections
$LimitingCollection = "All Systems"
#$LimitingCollection = "All Workstations"
#$LimitingCollection = "All Workstations - Admin"
#$LimitingCollection = "All Servers"

#Refresh Schedule
$Schedule = New-CMSchedule –RecurInterval Days –RecurCount 1

If (!(Test-Path -Path "$($SiteCode.name):\DeviceCollection\Operational\Boundary Groups based collection"))
{
    #Create sub folder for Boundary groups collection
    new-item -Name 'Boundary Groups based collection' -Path $($SiteCode.Name+":\DeviceCollection\Operational")
    #$FolderPath = $SiteCode.name+":\DeviceCollection\Boundary Groups based collection"
}

$FolderPath = $SiteCode.name+":\DeviceCollection\Operational\Boundary Groups based collection"

#Get the list of all Boundary groups
$BoundaryGroups=Get-CMBoundaryGroup

foreach($BoundaryGroup in $BoundaryGroups)
{
    #Define Collection name
    $CollectionName=$CollectionPrefix+$BoundaryGroup.Name
    #Write-Host $CollectionName
         
    Try
    {
        If (!(Get-CMDeviceCollection -Name $CollectionName))
        {
            #Create Collections
            New-CMDeviceCollection -Name $CollectionName -Comment "Collection based on boundary group of the same name" -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
            Write-host *** Collection $CollectionName created ***

            #DEfine Collection query
            $CollectionQuery="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 where SMS_R_System.ResourceId in (select resourceid from SMS_CollectionMemberClientBaselineStatus  where  SMS_CollectionMemberClientBaselineStatus.boundarygroups like '%"+$BoundaryGroup.Name+"%') and SMS_R_System.Name not in ('Unknown') and SMS_R_System.Client = '1'"
            # Write-Host $CollectionQuery
            #Add collection query
            Add-CMDeviceCollectionQueryMembershipRule -CollectionName $CollectionName -QueryExpression $CollectionQuery  -RuleName "Boundary Group" |out-null

            # Moving collection to folder
            Try
            {
                Move-CMObject -FolderPath $FolderPath -InputObject (Get-CMDeviceCollection -Name $CollectionName)
                Write-host *** Collection $CollectionNAme moved to the $folderPath folder ***
            }
            Catch
            {
                Write-host -ForegroundColor Red ("There was an error moving the: " + $CollectionName + " collection.")
            }
        }
        else
        {
            Write-Warning "Collection `"$CollectionName`" already exists."
        }
    }
    Catch
   {
        Write-host -ForegroundColor Red ("There was an error creating the: " + $CollectionName + " collection. Possible cause is that there's already a collection with that name.")
    }

}

#############################################################################

# Author : Jonathan Lefebvre-Globensky

# Website : www.SystemCenterDudes.com

# Twitter : @jlefebvregloben, @SCDudes

# Version : 1.0

# Created : 2020/04/23

# Modified :

# Purpose : This script create collections based on each found Boundary groups in an environment.

# Blog post related :

# Previous script to create collections with folder structure :https://www.systemcenterdudes.com/powershell-script-create-set-maintenance-collections

# Want more default operationnal collection? See Benoit Lecours powershell script --> https://gallery.technet.microsoft.com/Set-of-Operational-SCCM-19fa8178

# Updated by: Jim Webb

# Modified date: 12/3/21

#############################################################################

#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":"

#Set the prefix for collections to be created

$CollectionPrefix='BG - '

#Define possible limiting collections

$LimitingCollection = "All Systems"

#$LimitingCollection = "All Workstations"

#$LimitingCollection = "All Workstations - Admin"

#$LimitingCollection = "All Servers"

#Refresh Schedule

$Schedule = New-CMSchedule –RecurInterval Days –RecurCount 1

If (!(Test-Path -Path "$($SiteCode.name):\DeviceCollection\Operational\Boundary Groups based collection"))

{

#Create sub folder for Boundary groups collection

new-item -Name 'Boundary Groups based collection' -Path $($SiteCode.Name+":\DeviceCollection\Operational")

#$FolderPath = $SiteCode.name+":\DeviceCollection\Boundary Groups based collection"

}

$FolderPath = $SiteCode.name+":\DeviceCollection\Operational\Boundary Groups based collection"

#Get the list of all Boundary groups

$BoundaryGroups=Get-CMBoundaryGroup

foreach($BoundaryGroup in $BoundaryGroups)

{

#Define Collection name

$CollectionName=$CollectionPrefix+$BoundaryGroup.Name

#Write-Host $CollectionName

Try

{

If (!(Get-CMDeviceCollection -Name $CollectionName))

{

#Create Collections

New-CMDeviceCollection -Name $CollectionName -Comment "Collection based on boundary group of the same name" -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null

Write-host *** Collection $CollectionName created ***

#DEfine Collection query

$CollectionQuery="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 where SMS_R_System.ResourceId in (select resourceid from SMS_CollectionMemberClientBaselineStatus where SMS_CollectionMemberClientBaselineStatus.boundarygroups like '%"+$BoundaryGroup.Name+"%') and SMS_R_System.Name not in ('Unknown') and SMS_R_System.Client = '1'"

# Write-Host $CollectionQuery

#Add collection query

Add-CMDeviceCollectionQueryMembershipRule -CollectionName $CollectionName -QueryExpression $CollectionQuery -RuleName "Boundary Group" |out-null

# Moving collection to folder

Try

{

Move-CMObject -FolderPath $FolderPath -InputObject (Get-CMDeviceCollection -Name $CollectionName)

Write-host *** Collection $CollectionNAme moved to the $folderPath folder ***

}

Catch

{

Write-host -ForegroundColor Red ("There was an error moving the: " + $CollectionName + " collection.")

}

else

{

Write-Warning "Collection `"$CollectionName`" already exists."

}

Catch

{

Write-host -ForegroundColor Red ("There was an error creating the: " + $CollectionName + " collection. Possible cause is that there's already a collection with that name.")

}

Results from the first run of the script.

Results after running the script a second time.

Finding computers without a piece of software

December 3, 2021 by Jim Webb

Finding computers with a piece of software, say the Edge browser, is fairly easy with ConfigMgr. Simply create a collection that looks for the software in question and query SMS_G_System_INSTALLED_SOFTWARE.

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.ClientType, SMS_R_System.NetbiosName, SMS_R_System.OperatingSystemNameandVersion
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 = "Microsoft Edge"

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.ClientType, SMS_R_System.NetbiosName, SMS_R_System.OperatingSystemNameandVersion

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 = "Microsoft Edge"

Finding computers without a piece of software can be more difficult. The trick its to create a query that looks for computers that are NOT in the query that finds the software in question.

select SMS_R_System.NetbiosName 
from  SMS_R_System 
where SMS_R_System.NetbiosName not in (select SMS_R_System.NetbiosName 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 = "Microsoft Edge")

select SMS_R_System.NetbiosName

from SMS_R_System

where SMS_R_System.NetbiosName not in (select SMS_R_System.NetbiosName 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 = "Microsoft Edge")

In the query above, we are saying give me all of the computers that are not in the results returned. So if the results returned contain a list of computers that have Edge installed, a computer not in that list would be a computer without Edge.

In order to create this query, you need to set the “Criterion Type” to SubSelected values. Then you can select the operator, which in this case is “is not in”, then either browse for or paste the subselect query.

It’s worth noting that people smarter than me recommend using ResourceId instead of NetBiosName.

Subselect Query for SCCM Queries or Collections (WQL)

Check application revision on client

November 25, 2021 by Jim Webb

Param (
	[string]$ComputerName = $env:COMPUTERNAME,
    [string]$Application
)

Write-Host "Getting information from computer $ComputerName."

if ($Application)
{
    Get-WmiObject -ComputerName $ComputerName -Namespace root\ccm\clientsdk -Class CCM_Application | Where-Object {$_.Name -like "*$Application*" } | Select-Object Name, Revision, InstallState, ApplicabilityState, ConfigureState | Sort-Object Name | Format-Table -AutoSize
}
else
{
    Get-WmiObject -ComputerName $ComputerName -Namespace root\ccm\clientsdk -Class CCM_Application | Select-Object Name, Revision, InstallState, ApplicabilityState, ConfigureState | Sort-Object Name | Format-Table -AutoSize
}

Param (

[string]$ComputerName = $env:COMPUTERNAME,

[string]$Application

)

Write-Host "Getting information from computer $ComputerName."

if ($Application)

{

Get-WmiObject -ComputerName $ComputerName -Namespace root\ccm\clientsdk -Class CCM_Application | Where-Object {$_.Name -like "*$Application*" } | Select-Object Name, Revision, InstallState, ApplicabilityState, ConfigureState | Sort-Object Name | Format-Table -AutoSize

}

else

{

Get-WmiObject -ComputerName $ComputerName -Namespace root\ccm\clientsdk -Class CCM_Application | Select-Object Name, Revision, InstallState, ApplicabilityState, ConfigureState | Sort-Object Name | Format-Table -AutoSize

}

Delete root\ccm namespace with PowerShell

November 24, 2021 by Jim Webb

Delete the root\ccm namespace on the local computer.

Get-WmiObject -query "Select * From __Namespace Where Name='CCM'" -Namespace "root" | Remove-WmiObject

1	Get-WmiObject -query "Select * From __Namespace Where Name='CCM'" -Namespace "root" \| Remove-WmiObject

Delete the root\ccm namespace on a remote computer.

$Server = "ServerName"
Get-WmiObject -query "Select * From __Namespace Where Name='CCM'" -Namespace "root" -ComputerName $Server | Remove-WmiObject

1 2	$Server = "ServerName" Get-WmiObject -query "Select * From __Namespace Where Name='CCM'" -Namespace "root" -ComputerName $Server \| Remove-WmiObject

Clear SCCM Cache

November 24, 2021 by Jim Webb

Borrowed from https://sccm-zone.com/deleting-the-sccm-cache-the-right-way-3c1de8dc4b48

Using the PowerShell code below will clear the ConfigMgr cache the correct way.

## Initialize the CCM resource manager com object
[__comobject]$CCMComObject = New-Object -ComObject 'UIResource.UIResourceMgr'

## Get the CacheElementIDs to delete
$CacheInfo = $CCMComObject.GetCacheInfo().GetCacheElements()

## Remove cache items
ForEach ($CacheItem in $CacheInfo) {
    $null = $CCMComObject.GetCacheInfo().DeleteCacheElement([string]$($CacheItem.CacheElementID))
}

## Initialize the CCM resource manager com object

[__comobject]$CCMComObject = New-Object -ComObject 'UIResource.UIResourceMgr'

## Get the CacheElementIDs to delete

$CacheInfo = $CCMComObject.GetCacheInfo().GetCacheElements()

## Remove cache items

ForEach ($CacheItem in $CacheInfo) {

$null = $CCMComObject.GetCacheInfo().DeleteCacheElement([string]$($CacheItem.CacheElementID))

}

Collection query for reporting

November 24, 2021 by Jim Webb

In order to add a collection prompt to a custom report you need to add an additional dataset to your report that queries for all available collections, includes the collection name and ID, and sorts the list by name.

Below an an example of the required code to retrieve all collections from ConfigMgr.

SELECT        CollectionName, CollectionID
FROM            v_Collections
ORDER BY CollectionName

SELECT CollectionName, CollectionID

FROM v_Collections

ORDER BY CollectionName

Below is the output from SSMS.

To use the query in a report you need to add a new dataset. In this example the dataset is called “AllCollections.”

Now we can add the collection query to the report and the main dataset.

In order to make a report utilize the collection query, we need to add the view v_FullCollectionMembership_Valid to the report and join it to v_R_System_valid on the ResourceID column.

Now we need to modify the main report query to take advantage of the ConfigMgr collections. This is accomplished by modifying the report to filter on a variable. You do this by checking the box next to the “CollectionID” column from v_FullCollectionMembership_Valid box.

Now we’re ready to add the filter. Under the “Filter” column in the “CollectionID” row, enter ‘=@CollID’ without the quotes.

Now when you run the query you’ll be prompted for a collection ID. In the screenshot, SMS00001, or All Systems, is used.

Results from the query.

SQL to retrieve all collections

March 20, 2021 by Jim Webb

Below is the SQL code needed to retrieve all of the collections from ConfigMgr. This is useful when creating custom reports that need to be scoped to a specific collection. The query below selects the CollectionName and CollectionID columns and sorts the CollectionName column alphabetically.

SELECT        CollectionName, CollectionID
FROM            v_Collections
ORDER BY CollectionName

SELECT CollectionName, CollectionID

FROM v_Collections

ORDER BY CollectionName

Results from the query on my test system.