Tuesday, 10 August 2021

PowerShell-Scripts/Delete devices collections with no members and no deployments

Written by Sreekanth Reddy on August 10, 2021 in collection, deployment, PowerShell with No comments

#############################################################################
# Author  : Benoit Lecours 
# Website : www.SystemCenterDudes.com
# Twitter : @scdudes
#
# Version : 1.1
# Created : 2017/04/05
# Modified : 2017/08/31
#
# Purpose : This script delete collections without members and deployments
#
#############################################################################

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

Write-host "This script delete device collections without members and deployments. You will be prompted before each deletion.`n"
Write-host "Built-in collections and users collections are excluded`n"
Write-host "------------------------------------------------------------------------"
Write-Host ("Building Devices Collections List. This may take a couple of minutes...")
Write-host "------------------------------------------------------------------------`n"

$CollectionList = Get-CmCollection | Where-Object {$_.CollectionID -notlike 'SMS*' -and $_.CollectionType -eq '2' -and $_.MemberCount -eq 0} | Select-Object -Property Name,MemberCount,CollectionID,IsReferenceCollection
#$DeploymentList = Get-CMDeployment | Select-Object -Property CollectionID

Write-Host ("Found " + $CollectionList.Count + " collections without members (MemberCount = 0) `n")
Write-Host ("Analyzing list to find collection without deployments... `n")

foreach ($Collection in $CollectionList)
{
    $NumCollectionMembers = $Collection.MemberCount
    $CollectionID = $Collection.CollectionID
    $GetDeployment = Get-CMDeployment | Where-Object {$_.CollectionID -eq $Collection.CollectionID}
        
    # Delete collection if there's no members and no deployment on the collection
    If ($GetDeployment -eq $null) #$NumCollectionMembers -eq 0 -and 
    {
        # User Prompt
        Write-Host ("Collection " + $Collection.Name +" (" + $Collection.CollectionID + ")" + " has no members and deployments")

        # User Confirmation
        If ((Read-Host -Prompt "Type `"Y`" to delete the collection, any other key to skip") -ieq "Y")
        {
            #Check if Reference collection           
            Try
            {
                #Delete the collection object    
                Remove-CMCollection -Id $CollectionID -Force
                Write-Host -ForegroundColor Green ("Collection: " + $Collection.Name + " Deleted")
            }
            Catch{Write-Host -ForegroundColor Red ("Can't delete " + $Collection.Name + " collection. Possible cause : There's referenced collection or a custom security scope assigned to the collection.")}    
        }
    }
}

Powershell-Create SCCM Collections based on Active Directory OU

Written by Sreekanth Reddy on August 10, 2021 in collection, PowerShell with No comments

<#############################################################################
Author  : Benoit Lecours 
Website : www.SystemCenterDudes.com
Twitter : @scdudes
Version : 1.0
Created : 2019/12/10

The purposes of this script:
1. Create SCCM device collections based on Active Directory Organisational Unit
2. Define the Refresh Schedule of collection to 7 days
3. Create Query Rule for collection membership
4. Move created collection to custom folder
5. Updates collection membership at once.
##############################################################################>

# Import PS modules
Import-Module ActiveDirectory
Import-Module 'D:\Program Files\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'

# SCCM Site
$Site = (Get-PSDRive -PSProvider CMSite).Name
CD ${Site}:

# Defining refresh interval for collection - 7 days
$Schedule = New-CMSchedule –RecurInterval Days –RecurCount 7

##Get User Inputs for Base OU
$Users = Get-ADOrganizationalUnit -Filter * -Properties DistinguishedName,CanonicalName |Select-Object DistinguishedName,CanonicalName
Clear-Host
Write-Host "Here's the list of all OU in your Organisation".ToUpper()
Write-Host ""

For ($i=0; $i -lt $Users.Count; $i++)  {
  Write-Host "$($i+1): $($Users[$i].CanonicalName)"
}

Write-Host ""
[int]$number = Read-Host "Select the number corresponding to the desired top-most OU".ToUpper()
Write-Host ""
Write-Host -ForegroundColor Green "You've selected $($users[$number-1].CanonicalName). The script will create 1 collection for each OU under the selected OU.:".ToUpper()
$SearchBase = $($Users[$number-1]).DistinguishedName


#Get User Input for folder
Write-Host ""
$folderName = Read-Host "Enter the desired folder name. The folder will be created under the Device Collection Node and all collections will be moved to the folder"
New-Item -Name $folderName -Path "${Site}:\DeviceCollection"
$TargetFolder = "${Site}:\DeviceCollection\$folderName"


#Getting Canonical name and GUID from AD OUs based on user input
$ADOUs = Get-ADOrganizationalUnit -SearchBase "$SearchBase" -Filter * -Properties Canonicalname |Select-Object DistinguishedName,CanonicalName

#Create Collections

foreach ($OU in $ADOUs)
{
    $O_Name = $OU.CanonicalName
    $O_GUID = $OU.ObjectGUID

Try
{
    New-CMDeviceCollection -LimitingCollectionName 'All Systems' -Name $O_Name -RefreshSchedule $Schedule -Comment $O_GUID | Out-Null
    Write-host *** Collection $O_Name created ***
}
Catch
{
    Write-host -ForegroundColor Red ("There was an error creating the: " + $O_Name + " collection. Possible cause is that there's already a collection with that name.")
}

# Creating Query Membership rule
Add-CMDeviceCollectionQueryMembershipRule -CollectionName $O_Name -QueryExpression "select *  from  SMS_R_System where SMS_R_System.SystemOUName = '$O_Name'" -RuleName "OU Membership" | Out-Null

# Getting collection ID
$ColID = (Get-CMDeviceCollection -Name $O_Name).collectionid

# Moving collection to folder
Try
{
    Move-CMObject -FolderPath $TargetFolder -ObjectId "$ColID"
    Write-host *** Collection $O_Name moved to the $folderName folder ***
}
Catch
{
    Write-host -ForegroundColor Red ("There was an error moving the: " + $O_Name + " collection.")
}

# Updating collection membership
Invoke-CMDeviceCollectionUpdate -Name $O_Name
}
Write-Host ""
Write-host *** SCRIPT COMPLETED ***

Hardware Inventory SQL Report

Written by Sreekanth Reddy on August 10, 2021 in SQL with No comments

Hardware Inventory SQL Report

DECLARE @Today AS DATE
SET @Today = GETDATE()

DECLARE @BackInTime AS DATE
SET @BackInTime = DATEADD(DAY, -30, @Today )

SELECT DISTINCT 
 SYS.ResourceID,
 SYS.Name0 'Name', 
 SYS.AD_Site_Name0 'ADSite', 
 CS.UserName0 'User Name',
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END AS TopUser,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS, 
 REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack',
 CS.Manufacturer0 'Manufacturer',
 CS.Model0 Model,
 BIOS.SerialNumber0 'Serial Number', 
 CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, 
 BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, 
 (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', 
 SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)', 
 COUNT(RAM.ResourceID) '# Memory Slots',
 REPLACE (cs.SystemType0,'-based PC','') 'Type',
 SUM(D.Size0) / 1024 AS 'Disk Size GB',
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time',
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date',
 CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory',
 CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online',
 SYS.Client_Version0 as 'SCCM Agent Version',
 CPU.Manufacturer AS 'CPU Man.',
 CPU.[Number of CPUs] AS '# of CPUs',
 CPU.[Number of Cores per CPU] AS '# of Cores per CPU',
 CPU.[Logical CPU Count] AS 'Logical CPU Count', 
 US.ScanTime AS ' Windows Updates Scan Time' ,
 US.LastErrorCode AS ' Windows Updates Last Error Code' ,
 US.LastScanPackageLocation AS ' Windows Updates Last Package Location' ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END AS 'PC Type'
FROM
 v_R_System SYS
 INNER JOIN (
 SELECT 
 Name0,
 MAX(Creation_Date0) AS Creation_Date
 FROM 
 dbo.v_R_System 
 GROUP BY
 Name0
 ) AS CleanSystem
 ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
 LEFT JOIN v_GS_COMPUTER_SYSTEM CS 
 ON SYS.ResourceID=cs.ResourceID
 LEFT JOIN v_GS_PC_BIOS BIOS 
 ON SYS.ResourceID=bios.ResourceID
 LEFT JOIN (
 SELECT
 A.ResourceID,
 MAX(A.[InstallDate0]) AS [InstallDate0]
 FROM
 v_GS_OPERATING_SYSTEM A
 GROUP BY
 A.ResourceID
 ) AS X
 ON SYS.ResourceID = X.ResourceID
 INNER JOIN v_GS_OPERATING_SYSTEM OS 
 ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
 LEFT JOIN v_GS_PHYSICAL_MEMORY RAM 
 ON SYS.ResourceID=ram.ResourceID
 LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
 ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
 LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
 ON SYS.ResourceID = U.ResourceID 
 LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
 LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
 LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
 LEFT JOIN v_CH_ClientSummary CH
 ON SYS.ResourceID = CH.ResourceID
 LEFT JOIN (
 SELECT
 DISTINCT(CPU.SystemName0) AS [System Name],
 CPU.Manufacturer0 AS Manufacturer,
 CPU.ResourceID,
 CPU.Name0 AS Name,
 COUNT(CPU.ResourceID) AS [Number of CPUs],
 CPU.NumberOfCores0 AS [Number of Cores per CPU],
 CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
 FROM [dbo].[v_GS_PROCESSOR] CPU
 GROUP BY
 CPU.SystemName0,
 CPU.Manufacturer0,
 CPU.Name0,
 CPU.NumberOfCores0,
 CPU.NumberOfLogicalProcessors0,
 CPU.ResourceID
 ) CPU
 ON CPU.ResourceID = SYS.ResourceID
 LEFT JOIN v_UpdateScanStatus US
 ON US.ResourceID = SYS.ResourceID
WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
 CH.LastOnline BETWEEN @BackInTime AND GETDATE()
 GROUP BY
 SYS.Creation_Date0 ,
 SYS.Name0 , 
 SYS.ResourceID ,
 SYS.AD_Site_Name0 ,
 CS.UserName0 ,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'), 
 REPLACE (OS.CSDVersion0,'Service Pack','SP'),
 CS.Manufacturer0 ,
 CS.Model0 ,
 BIOS.SerialNumber0 ,
 REPLACE (cs.SystemType0,'-based PC','') ,
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
 CONVERT(VARCHAR(26), WS.LastHWScan, 101),
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END,
 CPU.Manufacturer, 
 CPU.[Number of CPUs] ,
 CPU.[Number of Cores per CPU], 
 CPU.[Logical CPU Count],
 US.ScanTime ,
 US.LastErrorCode ,
 US.LastScanPackageLocation ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END ,
 CONVERT (DATE,BIOS.ReleaseDate0) , 
 BIOS.SMBIOSBIOSVersion0 ,
 SYS.Client_Version0 ,
 CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
 ORDER BY SYS.Name0

Hardware Inventory SQL Report
Filter by Collection ID

DECLARE @Today AS DATE
DECLARE @CollectionID nvarchar(8)
SET @Today = GETDATE()

DECLARE @BackInTime AS DATE
SET @BackInTime = DATEADD(DAY, -30, @Today )
SET @CollectionID = 'CM100017'

SELECT DISTINCT 
 dfc.CollectionID,
 SYS.ResourceID,
 SYS.Name0 'Name', 
 SYS.AD_Site_Name0 'ADSite', 
 CS.UserName0 'User Name',
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END AS TopUser,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS, 
 REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack',
 CS.Manufacturer0 'Manufacturer',
 CS.Model0 Model,
 BIOS.SerialNumber0 'Serial Number', 
 CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, 
 BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, 
 (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', 
 SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)', 
 COUNT(RAM.ResourceID) '# Memory Slots',
 REPLACE (cs.SystemType0,'-based PC','') 'Type',
 SUM(D.Size0) / 1024 AS 'Disk Size GB',
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time',
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date',
 CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory',
 CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online',
 SYS.Client_Version0 as 'SCCM Agent Version',
 CPU.Manufacturer AS 'CPU Man.',
 CPU.[Number of CPUs] AS '# of CPUs',
 CPU.[Number of Cores per CPU] AS '# of Cores per CPU',
 CPU.[Logical CPU Count] AS 'Logical CPU Count', 
 US.ScanTime AS ' Windows Updates Scan Time' ,
 US.LastErrorCode AS ' Windows Updates Last Error Code' ,
 US.LastScanPackageLocation AS ' Windows Updates Last Package Location' ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END AS 'PC Type'
FROM
 v_R_System SYS
 INNER JOIN (
 SELECT 
 Name0,
 MAX(Creation_Date0) AS Creation_Date
 FROM 
 dbo.v_R_System 
 GROUP BY
 Name0
 ) AS CleanSystem
 ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
 LEFT JOIN v_GS_COMPUTER_SYSTEM CS 
 ON SYS.ResourceID=cs.ResourceID
 LEFT JOIN v_GS_PC_BIOS BIOS 
 ON SYS.ResourceID=bios.ResourceID
 LEFT JOIN (
 SELECT
 A.ResourceID,
 MAX(A.[InstallDate0]) AS [InstallDate0]
 FROM
 v_GS_OPERATING_SYSTEM A
 GROUP BY
 A.ResourceID
 ) AS X
 ON SYS.ResourceID = X.ResourceID
 INNER JOIN v_GS_OPERATING_SYSTEM OS 
 ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
 LEFT JOIN v_GS_PHYSICAL_MEMORY RAM 
 ON SYS.ResourceID=ram.ResourceID
 LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
 ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
 LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
 ON SYS.ResourceID = U.ResourceID 
 LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
 LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
 LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
 LEFT JOIN v_CH_ClientSummary CH
 ON SYS.ResourceID = CH.ResourceID
 LEFT JOIN (
 SELECT
 DISTINCT(CPU.SystemName0) AS [System Name],
 CPU.Manufacturer0 AS Manufacturer,
 CPU.ResourceID,
 CPU.Name0 AS Name,
 COUNT(CPU.ResourceID) AS [Number of CPUs],
 CPU.NumberOfCores0 AS [Number of Cores per CPU],
 CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
 FROM [dbo].[v_GS_PROCESSOR] CPU
 GROUP BY
 CPU.SystemName0,
 CPU.Manufacturer0,
 CPU.Name0,
 CPU.NumberOfCores0,
 CPU.NumberOfLogicalProcessors0,
 CPU.ResourceID
 ) CPU
 ON CPU.ResourceID = SYS.ResourceID
 LEFT JOIN v_UpdateScanStatus US
 ON US.ResourceID = SYS.ResourceID
 inner join dbo.v_FullCollectionMembership dfc 
 on dfc.ResourceID = sys.ResourceID
WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND dfc.CollectionID = @CollectionID and 
 CH.LastOnline BETWEEN @BackInTime AND GETDATE()
 GROUP BY
 dfc.CollectionID, 
 SYS.Creation_Date0 ,
 SYS.Name0 , 
 SYS.ResourceID ,
 SYS.AD_Site_Name0 ,
 CS.UserName0 ,
 REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'), 
 REPLACE (OS.CSDVersion0,'Service Pack','SP'),
 CS.Manufacturer0 ,
 CS.Model0 ,
 BIOS.SerialNumber0 ,
 REPLACE (cs.SystemType0,'-based PC','') ,
 CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
 CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
 CONVERT(VARCHAR(26), WS.LastHWScan, 101),
 CASE
 WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
 ELSE U.TopConsoleUser0
 END,
 CPU.Manufacturer, 
 CPU.[Number of CPUs] ,
 CPU.[Number of Cores per CPU], 
 CPU.[Logical CPU Count],
 US.ScanTime ,
 US.LastErrorCode ,
 US.LastScanPackageLocation ,
 CASE SE.ChassisTypes0 
 WHEN '1' THEN 'Other' 
 WHEN '2' THEN 'Unknown' 
 WHEN '3' THEN 'Desktop' 
 WHEN '4' THEN 'Low Profile Desktop' 
 WHEN '5' THEN 'Pizza Box' 
 WHEN '6' THEN 'Mini Tower' 
 WHEN '7' THEN 'Tower' 
 WHEN '8' THEN 'Portable' 
 WHEN '9' THEN 'Laptop' 
 WHEN '10' THEN 'Notebook' 
 WHEN '11' THEN 'Hand Held' 
 WHEN '12' THEN 'Docking Station' 
 WHEN '13' THEN 'All in One' 
 WHEN '14' THEN 'Sub Notebook' 
 WHEN '15' THEN 'Space-Saving' 
 WHEN '16' THEN 'Lunch Box' 
 WHEN '17' THEN 'Main System Chassis' 
 WHEN '18' THEN 'Expansion Chassis' 
 WHEN '19' THEN 'SubChassis' 
 WHEN '20' THEN 'Bus Expansion Chassis' 
 WHEN '21' THEN 'Peripheral Chassis' 
 WHEN '22' THEN 'Storage Chassis' 
 WHEN '23' THEN 'Rack Mount Chassis' 
 WHEN '24' THEN 'Sealed-Case PC' 
 ELSE 'Undefinded' 
 END ,
 CONVERT (DATE,BIOS.ReleaseDate0) , 
 BIOS.SMBIOSBIOSVersion0 ,
 SYS.Client_Version0 ,
 CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
 ORDER BY SYS.Name0

WQL Collection Querreys

Written by Sreekanth Reddy on August 08, 2021 in collection, WQL with No comments

Windows 10 computer running 32bits OS

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.SystemType = 'X86-based PC'

Computers that don’t have 64GB free space

Select SMS_R_System.ResourceID, SMS_R_System.NETBIOSname, SMS_G_System_Logical_Disk.FreeSpace

from SMS_R_System

inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceId = SMS_R_System.ResourceId

where SMS_G_System_LOGICAL_DISK.DeviceID = "C:" and SMS_G_System_LOGICAL_DISK.FreeSpace < 65536

Computer with less than 4GB RAM

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_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceId = SMS_R_System.ResourceId where SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory <= 4194304

Computer not SecureBoot enabled

select SMS_R_System.Name, SMS_G_System_FIRMWARE.SecureBoot, SMS_R_System.SystemOUName from SMS_R_System inner join SMS_G_System_FIRMWARE on SMS_G_System_FIRMWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_FIRMWARE.SecureBoot = 0

Move Software Updates between Software Update Groups in SCCM using Powershell

Written by Sreekanth Reddy on August 07, 2021 in PowerShell, Updates with No comments

How does the Powershell script work?

If you want to move a Software Update between different Software Update Groups, you don’t move them, but rather change the membership.

The script that I have provided will take the latest created Software Update Group and add those members to a defined Software Update Group.

The Powershell script

Below is the script I have created for editing the membership of a Software Update Group.

.SYNOPSIS

This script will add all updates from the latest Software Update Group to a defined Software Update Group.

.DESCRIPTION

This script will add all updates from the latest Software Update Group to a defined Software Update Group.

Author: Daniel Classon (www.danielclasson.com)

Version: 1.0

Date: 2018-12-18

.EXAMPLE

.\Add-SUGMembers -DestinationSUG SUG1

This will add all updates contained in the latest Software Updates Group to SUG1.

.DISCLAIMER

All scripts and other powershell references are offered AS IS with no warranty.

These script and functions are tested in my environment and it is recommended that you test these scripts in a test environment before using in your production environment.

PARAM(

[string]$DestinationSUG

)

#Load SCCM Powershell module

Try {

Import-Module (Join-Path $(Split-Path $env:SMS_ADMIN_UI_PATH) ConfigurationManager.psd1) -ErrorAction Stop

}

Catch [System.UnauthorizedAccessException] {

Write-Warning -Message "Access denied" ; break

}

Catch [System.Exception] {

Write-Warning "Unable to load the Configuration Manager Powershell module from $env:SMS_ADMIN_UI_PATH" ; break

}

#Set current drive to SCCM drive

$SiteCode = Get-PSDrive -PSProvider CMSITE

Set-Location -Path "$($SiteCode.Name):\"

Function Write-Log

{

PARAM(

[String]$Message,

[int]$Severity,

[string]$Component

)

$LogPath = $PSScriptRoot

$TimeZoneBias = Get-WMIObject -Query "Select Bias from Win32_TimeZone"

$Date= Get-Date -Format "HH:mm:ss.fff"

$Date2= Get-Date -Format "MM-dd-yyyy"

$Type=1

"<![LOG[$Message]LOG]!><time=$([char]34)$Date$($TimeZoneBias.bias)$([char]34) date=$([char]34)$date2$([char]34) component=$([char]34)$Component$([char]34) context=$([char]34)$([char]34) type=$([char]34)$Severity$([char]34) thread=$([char]34)$([char]34) file=$([char]34)$([char]34)>"| Out-File -FilePath "$LogPath\Add-SUGMembers.log" -Append -NoClobber -Encoding default

}

#Change membership of Software Update to new Software Update Group

$LatestSUG = Get-CMSoftwareUpdateGroup | Select-Object LocalizedDisplayName,DateCreated | Sort-Object DateCreated | Select-Object -Last 1

$Updates = Get-CMSoftwareUpdate -UpdateGroupName $LatestSUG -Fast

$Count = 0

Foreach ($Update in $Updates) {

$Count++

Try {

Add-CMSoftwareUpdateToGroup -SoftwareUpdateId $($Update.CI_ID) -SoftwareUpdateGroupId $($DestinationSUG.CI_ID)

Write-Progress -Activity "Adding $($Update.LocalizedDisplayName) to $($DestinationSUG.LocalizedDisplayName)" -Status "Adding $Count of $($Updates.Count) updates to $($SourceSUG.LocalizedDisplayName)" -PercentComplete ($Count / $Updates.count * 100)

Write-Log -Message "Adding $($Update.LocalizedDisplayName) to $($DestinationSUG.LocalizedDisplayName)" -Severity 1 -Component "Add SU to SUG"

}

Catch {

Write Warning "$_.Exception.Message"

Write-Log -Message "$_.Exception.Message" -Severity 3 -Component "Add SU to SUG"

}

Set-Location $PSScriptRoot

Configure Collection Updates in MEMCM (SCCM) using a Powershell Script

Written by Sreekanth Reddy on August 07, 2021 in collection, PowerShell with No comments

Configure Collection Updates in MEMCM (SCCM) using a Powershell Script

If you want to configure device collections in collections.txt to only update on a schedule, you run this command:

.\Set-CollectionUpdates.ps1 -CollectionType Device -RefreshType 2

Source code:

<#
.PARAMETER COLLECTIONTYPE
Define the type of collections in the collections.txt file. Valid inputs are Device and User

.PARAMETER REFRESHTYPE
Define the Refresh type for the collection. Valid inputs are:

# The following refresh types exist for ConfigMgr collections 
# 6 = Incremental and Scheduled Updates 
# 4 = Incremental Updates Only 
# 2 = Scheduled Updates only 
# 1 = Manual Update only 
    
.DESCRIPTION
Sets the collection refresh type for all collections defined in a text file.

.NOTES
Author: Daniel Classon
Version: 1.0
Date: 2015/05/18
    
.EXAMPLE
.\Set-Collection_Updates.ps1 -CollectionType Device -RefreshType 2
Will set the collections in collections.txt to "Scheduled Updates only".

.DISCLAIMER
All scripts and other powershell references are offered AS IS with no warranty.
These script and functions are tested in my environment and it is recommended that you test these scripts in a test environment before using in your production environment.
#>

[CmdletBinding()]

Param(
    [Parameter(Mandatory=$True, Helpmessage="Enter the Collection Type")]
    [string]$CollectionType,
    [Parameter(Mandatory=$True, Helpmessage="Enter the Refresh Type")]
    [string]$RefreshType
)

Begin {
    #Checks if the user is in the administrator group. Warns and stops if the user is not.
    if (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) {
        Write-Warning "You are not running this as local administrator. Run it again in an elevated prompt." ; break
    }
    try {
        Import-Module (Join-Path $(Split-Path $env:SMS_ADMIN_UI_PATH) ConfigurationManager.psd1)
    }
    catch [System.UnauthorizedAccessException] {
        Write-Warning -Message "Access denied" ; break
    }
    catch [System.Exception] {
        Write-Warning "Unable to load the Configuration Manager Powershell module from $env:SMS_ADMIN_UI_PATH" ; break
    }
}
Process {
    try {
        $CollectionIDs = Get-Content "collections.txt" -ErrorAction Stop
    }
    catch [System.Exception] {
        Write-Warning "Unable to find collections.txt. Make sure to place it in the script directory."
    }
    $SiteCode = Get-PSDrive -PSProvider CMSITE
    Set-Location -Path "$($SiteCode.Name):\"
    
    $Count = 0

    Foreach ($CollectionID in $CollectionIDs) {
        $Count++
        if ($CollectionType -eq "Device") {
            $Collection = Get-CMDeviceCollection -CollectionId $CollectionID
            $Collection.RefreshType = $RefreshType
            $Collection.Put()
            if ($RefreshType -eq 1) {
                Write-Progress -Activity "Enabling Manual Update only  on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Manual Update only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
            }
            elseif ($RefreshType -eq 2) {
                Write-Progress -Activity "Enabling Scheduled Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Scheduled Updates only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
            }
            elseif ($RefreshType -eq 4) {
                Write-Progress -Activity "Enabling Incremental Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Incremental Updates Only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
            }
            elseif ($RefreshType -eq 6) {
                Write-Progress -Activity "Enabling Incremental and Scheduled Updates on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Incremental and Scheduled Updates on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
        }
        } 
        if ($CollectionType -eq "User") {
            $Collection = Get-CMUserCollection -CollectionId $CollectionID
            $Collection.RefreshType = $RefreshType
            $Collection.Put()
            if ($RefreshType -eq 1) {
                Write-Progress -Activity "Enabling Manual Update only  on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Manual Update only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
            }
            elseif ($RefreshType -eq 2) {
                Write-Progress -Activity "Enabling Scheduled Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Scheduled Updates only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
            }
            elseif ($RefreshType -eq 4) {
                Write-Progress -Activity "Enabling Incremental Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Incremental Updates Only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
            }
            elseif ($RefreshType -eq 6) {
                Write-Progress -Activity "Enabling Incremental and Scheduled Updates on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
                Write-Host "Enabling Incremental and Scheduled Updates on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
        }
        } 
} 
}  
End {
    Write-Host "$Count $CollectionType collections were updated"  
    Set-Location -Path $env:SystemDrive
}

Powershell script to find incremental Collection

Written by Sreekanth Reddy on August 07, 2021 in collection, PowerShell with No comments

This is how you can get an idea how many collections you have that are using incremental updates:

Get-CMDeviceCollection | Select -Property Name, CollectionID,RefreshType | Where-Object {$_.Refreshtype -eq 6} | FT -AutoSize

Topics

Tuesday, 10 August 2021

PowerShell-Scripts/Delete devices collections with no members and no deployments

Powershell-Create SCCM Collections based on Active Directory OU

Hardware Inventory SQL Report

Hardware Inventory SQL Report

Sunday, 8 August 2021

WQL Collection Querreys

Saturday, 7 August 2021

Move Software Updates between Software Update Groups in SCCM using Powershell

Configure Collection Updates in MEMCM (SCCM) using a Powershell Script

Powershell script to find incremental Collection

Salad

Pasta