<#
.SYNOPSIS
Shows the information about the deployment packages and Software update groups of which asoftware update is a member.
.DESCRIPTION
This script creates a form that requires a article id, a software update group, or a deployment package as input. Based on the input it will show information about
the specific software update, or all the software updates in a software update group, or a deployment package. When used for a software update it will show in which
software update group and deployment package it exists. When used for a software update group it will show in which deployment packages those updates also exists.
When used for a deployment package it will show in which software update groups those updates exist.
.PARAMETER SiteCode
The site code of the primary site.
.PARAMETER SiteServer
The site server of the primary site.
.NOTES
Author: Peter van der Woude - pvanderwoude@hotmail.com
Date published: 19-06-2014
.LINK
http://www.petervanderwoude.nl
.EXAMPLE
Get-SoftwareUpdateInformation.ps1 -SiteCode PCP -SiteServer CLDSRV02
#>
[CmdletBinding()]
param (
[string]$SiteCode,
[string]$SiteServer
)
#Function to load the form
function Load-Form {
$Form1.Controls.Add($Button1)
$Form1.Controls.Add($Button2)
$Form1.Controls.Add($ComboBox1)
$Form1.Controls.Add($DataGridView1)
$Form1.Controls.Add($DataGridView2)
$Form1.Controls.Add($Label1)
$Form1.Controls.Add($Label2)
$Form1.Controls.Add($LinkLabel1)
$Form1.Controls.Add($LinkLabel2)
$Form1.Controls.Add($TextBox1)
$Form1.Controls.Add($GroupBox1)
$Form1.Controls.Add($GroupBox2)
$Form1.Controls.Add($GroupBox3)
$ComboBox1.Items.add("Software Update")
$ComboBox1.Items.add("Deployment Package")
$ComboBox1.Items.add("Software Update Group")
$Form1.ShowDialog()
}
#Function to get the software update group membership of updates
function Get-SoftwareUpdateGroupMembership {
param (
[array]$Updates
)
foreach ($Update in $Updates) {
$UpdateCIID = $Update.CI_ID
$UpdateGroupNames = (Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT DISTINCT ALI.* from SMS_AuthorizationList ALI `
JOIN SMS_CIRelation CIR on ALI.CI_ID = CIR.fromCIID WHERE CIR.ToCIID='$UpdateCIID'").LocalizedDisplayName
if ($UpdateGroupNames -ne $null) {
foreach ($Name in $UpdateGroupNames) {
$UpdateGroupName = $Name
$DataGridView1.Rows.Add($UpdateGroupName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
$UpdateGroupName = "<NoSoftwareUpdateGroup>"
$DataGridView1.Rows.Add($UpdateGroupName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
}
#Function to get the deployment package membership of updates
function Get-DeploymentPackageMembership {
param (
[array]$Updates
)
foreach ($Update in $Updates) {
$UpdateCIID = $Update.CI_ID
$UpdatePackageNames = (Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT DISTINCT sup.* FROM SMS_SoftwareUpdatesPackage AS sup `
JOIN SMS_PackageToContent AS pc ON sup.PackageID=pc.PackageID JOIN SMS_CIToContent AS cc ON pc.ContentID = cc.ContentID WHERE CC.CI_ID='$UpdateCIID'").Name
if ($UpdatePackageNames -ne $null) {
foreach ($UpdatePackageName in $UpdatePackageNames) {
$DataGridView2.Rows.Add($UpdatePackageName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
$UpdatePackageName = "<NoDeploymentPackage>"
$DataGridView2.Rows.Add($UpdatePackageName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
}
#Function to get the members of a software update group
function Get-SoftwareUpdateGroupMembers {
param (
[string]$Group
)
$UpdateGroupCIID = (Get-WmiObject -ComputerName $SiteServer -Namespace root/SMS/site_$($SiteCode) -Class SMS_AuthorizationList -Filter "LocalizedDisplayName='$Group'").CI_ID
$Updates = Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT upd.* FROM SMS_SoftwareUpdate upd, SMS_CIRelation cr WHERE cr.FromCIID='$UpdateGroupCIID' AND cr.RelationType=1 AND upd.CI_ID=cr.ToCIID"
if ($Updates -ne $null) {
foreach ($Update in $Updates) {
$DataGridView1.Rows.Add($Group,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
}
return $Updates
}
#Function to get the members of a deployment package
function Get-DeploymentPackageMembers {
param (
[string]$Package
)
$UpdatePackageID = (Get-WmiObject -ComputerName $SiteServer -Namespace root/SMS/site_$($SiteCode) -Class SMS_SoftwareUpdatesPackage -Filter "Name='$Package'").PackageID
$Updates = Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT DISTINCT su.* FROM SMS_SoftwareUpdate AS su JOIN SMS_CIToContent AS cc `
ON SU.CI_ID = CC.CI_ID JOIN SMS_PackageToContent AS pc ON pc.ContentID=cc.ContentID WHERE pc.PackageID='$UpdatePackageID' AND su.IsContentProvisioned=1"
if ($Updates -ne $null) {
foreach ($Update in $Updates) {
$DataGridView2.Rows.Add($Package,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
}
return $Updates
}
#Button1 OnClick event
$Button1_OnClick= {
$Form1.Close()
}
#Button2 OnClick event
$Button2_OnClick= {
Try {
if ($DataGridView1 -ne $null) {
$DataGridView1.Rows.Clear()
$DataGridView2.Rows.Clear()
$Form1.Refresh()
}
$ErrorProvider1.SetError($TextBox1,"")
$ErrorProvider1.SetError($ComboBox1,"")
if($TextBox1.Text.Length -eq 0 -or $ComboBox1.SelectedItem -eq $null) {
if($TextBox1.Text.Length -eq 0) {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid name")
}
if ($ComboBox1.SelectedItem -eq $null) {
$ErrorProvider1.SetError($ComboBox1, "Please select a valid type")
}
}
else {
$Name = $TextBox1.Text
$Type = $ComboBox1.SelectedItem
if ($Type -eq "Software Update") {
$GroupBox1.Text = "Software Update Group"
$GroupBox2.Text = "Deployment Package"
$TextBox1.Enabled = $false
$ComboBox1.Enabled = $false
$Updates = Get-WmiObject -ComputerName $SiteServer -Namespace root/SMS/site_$($SiteCode) -Class SMS_SoftwareUpdate -Filter "ArticleID='$Name'"
if ($Updates -ne $null) {
Get-SoftwareUpdateGroupMembership $Updates
Get-DeploymentPackageMembership $Updates
}
else {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid article id of a software update")
}
$ComboBox1.Enabled = $true
$TextBox1.Enabled = $true
}
elseif ($Type -eq "Software Update Group") {
$GroupBox1.Text = "Software Update Group: $Name"
$GroupBox2.Text = "Deployment Package"
$TextBox1.Enabled = $false
$ComboBox1.Enabled = $false
$Updates = Get-SoftwareUpdateGroupMembers $Name
if ($Updates -ne $null) {
Get-DeploymentPackageMembership $Updates
}
else {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid name of a software update group")
}
$ComboBox1.Enabled = $true
$TextBox1.Enabled = $true
}
elseif ($Type -eq "Deployment Package") {
$GroupBox1.Text = "Software Update Group"
$GroupBox2.Text = "Deployment Package: $Name"
$TextBox1.Enabled = $false
$ComboBox1.Enabled = $false
$Updates = Get-DeploymentPackageMembers $Name
if ($Updates -ne $null) {
Get-SoftwareUpdateGroupMembership $Updates
}
else {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid name of a deployment package")
}
$ComboBox1.Enabled = $true
$TextBox1.Enabled = $true
}
}
}
Catch {
[Windows.Forms.MessageBox]::Show(“Please provide a valid combination of a name and type”, “Software Update Information”, [Windows.Forms.MessageBoxButtons]::OK, [Windows.Forms.MessageBoxIcon]::Error)
}
}
#LinkLabel1 event
$LinkLabel1_OpenLink= {
[System.Diagnostics.Process]::start($LinkLabel1.text)
}
#LinkLabel2 event
$LinkLabel2_OpenLink= {
[System.Diagnostics.Process]::start("http://twitter.com/pvanderwoude")
}
#Load Assemblies
[Reflection.Assembly]::LoadWithPartialName("System.Drawing") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
#Create ErrorProvider
$ErrorProvider1 = New-Object System.Windows.Forms.ErrorProvider
$ErrorProvider1.BlinkStyle = "NeverBlink"
#Create Form1
$Form1 = New-Object System.Windows.Forms.Form
$Form1.Size = New-Object System.Drawing.Size(700,590)
$Form1.MinimumSize = New-Object System.Drawing.Size(700,590)
$Form1.MaximumSize = New-Object System.Drawing.Size(700,590)
$Form1.SizeGripStyle = "Hide"
$Form1.Text = "Software Update Information"
$Form1.ControlBox = $true
$Form1.TopMost = $true
#Create Button1
$Button1 = New-Object System.Windows.Forms.Button
$Button1.Location = New-Object System.Drawing.Size(510,490)
$Button1.Size = New-Object System.Drawing.Size(150,25)
$Button1.Text = "Close"
$Button1.add_Click($Button1_OnClick)
#Create Button2
$Button2 = New-Object System.Windows.Forms.Button
$Button2.Location = New-Object System.Drawing.Size(510,30)
$Button2.Size = New-Object System.Drawing.Size(150,25)
$Button2.Text = "Execute"
$Button2.add_Click($Button2_OnClick)
#Create ComboBox1
$ComboBox1 = New-Object System.Windows.Forms.ComboBox
$ComboBox1.Location = New-Object System.Drawing.Size(255,30)
$ComboBox1.Size = New-Object System.Drawing.Size(150,25)
$comboBox1.Text = "<Select Type>"
#Create DataGriView1
$DataGridView1 = New-Object System.Windows.Forms.DataGridView
$DataGridView1.Location = New-Object System.Drawing.Size(20,95)
$DataGridView1.Size = New-Object System.Drawing.Size(640,170)
$DataGridView1.ColumnCount = 3
$DataGridView1.ColumnHeadersVisible = $true
$DataGridView1.Columns[0].Name = "Software Update Group"
$DataGridView1.Columns[0].AutoSizeMode = "Fill"
$DataGridView1.Columns[1].Name = "Article ID"
$DataGridView1.Columns[1].AutoSizeMode = "Fill"
$DataGridView1.Columns[2].Name = "Software Update"
$DataGridView1.Columns[2].AutoSizeMode = "Fill"
$DataGridView1.AllowUserToAddRows = $false
$DataGridView1.AllowUserToDeleteRows = $false
$DataGridView1.ReadOnly = $True
$DataGridView1.ColumnHeadersHeightSizeMode = "DisableResizing"
$DataGridView1.RowHeadersWidthSizeMode = "DisableResizing"
$DataGridView1.SelectionMode = "FullRowSelect"
#Create DataGridView2
$DataGridView2 = New-Object System.Windows.Forms.DataGridView
$DataGridView2.Location = New-Object System.Drawing.Size(20,305)
$DataGridView2.Size = New-Object System.Drawing.Size(640,170)
$DataGridView2.ColumnCount = 3
$DataGridView2.ColumnHeadersVisible = $true
$DataGridView2.Columns[0].Name = "Deployment Package"
$DataGridView2.Columns[0].AutoSizeMode = "Fill"
$DataGridView2.Columns[1].Name = "Article ID"
$DataGridView2.Columns[1].AutoSizeMode = "Fill"
$DataGridView2.Columns[2].Name = "Software Update"
$DataGridView2.Columns[2].AutoSizeMode = "Fill"
$DataGridView2.AllowUserToAddRows = $false
$DataGridView2.AllowUserToDeleteRows = $false
$DataGridView2.ReadOnly = $True
$DataGridView2.ColumnHeadersHeightSizeMode = "DisableResizing"
$DataGridView2.RowHeadersWidthSizeMode = "DisableResizing"
$DataGridView2.SelectionMode = "FullRowSelect"
#Create Groupbox1
$GroupBox1 = New-Object System.Windows.Forms.GroupBox
$GroupBox1.Location = New-Object System.Drawing.Size(10,75)
$GroupBox1.Size = New-Object System.Drawing.Size(660,200)
$GroupBox1.Text = "Software Update Group"
#Create GroupBox2
$GroupBox2 = New-Object System.Windows.Forms.GroupBox
$GroupBox2.Location = New-Object System.Drawing.Size(10,285)
$GroupBox2.Size = New-Object System.Drawing.Size(660,200)
$GroupBox2.Text = "Deployment Package"
#Create GroupBox3
$GroupBox3 = New-Object System.Windows.Forms.GroupBox
$GroupBox3.Location = New-Object System.Drawing.Size(10,10)
$GroupBox3.Size = New-Object System.Drawing.Size(660,55)
$GroupBox3.Text = "Required Information"
#Create Label1
$Label1 = New-Object System.Windows.Forms.Label
$Label1.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$Label1.Location = New-Object System.Drawing.Size(20,530)
$Label1.Size = New-Object System.Drawing.Size(48,23)
$Label1.Text = "My blog:"
#Create Label2
$Label2 = New-Object System.Windows.Forms.Label
$Label2.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$Label2.Location = New-Object System.Drawing.Size(460,530)
$Label2.Size = New-Object System.Drawing.Size(111,23)
$Label2.Text = "Follow me on twitter:"
#Create LinkLabel1
$LinkLabel1 = New-Object System.Windows.Forms.LinkLabel
$LinkLabel1.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$LinkLabel1.Location = New-Object System.Drawing.Size(68,530)
$LinkLabel1.Size = New-Object System.Drawing.Size(142,23)
$LinkLabel1.Text = "www.petervanderwoude.nl"
$LinkLabel1.add_click($LinkLabel1_OpenLink)
#Create LinkLabel2
$LinkLabel2 = New-Object System.Windows.Forms.LinkLabel
$LinkLabel2.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$LinkLabel2.Location = New-Object System.Drawing.Size(569,530)
$LinkLabel2.Size = New-Object System.Drawing.Size(90,23)
$linkLabel2.Text = "@pvanderwoude"
$LinkLabel2.add_click($LinkLabel2_OpenLink)
#Create TextBox1
$TextBox1 = New-Object System.Windows.Forms.TextBox
$TextBox1.Location = New-Object System.Drawing.Size(20,30)
$TextBox1.Size = New-Object System.Drawing.Size(150,25)
$TextBox1.Text = "<Provide Name>"
#Load form
Load-Form#############################################################################
# Author : Benoit Lecours
# Website : www.SystemCenterDudes.com
# Twitter : @scdudes
#
# Version : 1.0
# Created : 2018/06/05
# Modified :
#
# Purpose : This script delete deploymentsolder than the specified date
#
#############################################################################
#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 "--------------- SYSTEM CENTER DUDES - DELETE OLD DEPLOYMENTS ----------------`n"
Write-host "This script deletes SCCM deployments older than the number of days specified.`n"
Write-Host "The script is based on Creation Date. The user will be prompted before each deletion.`n" -ForegroundColor Yellow
$SCCMServer = Read-Host "Enter your SCCM server Name"
$UserDate = Read-Host "Delete deployments older than how many days ? (ex: 365 = 1 year)"
$DeleteDate = (Get-Date).AddDays(-$UserDate)
Write-Host ""
Write-host "-------------------------------------------------------------------------------------"
Write-Host "Building Deployment List older than " -NoNewline; Write-Host $DeleteDate.ToShortDateString() -ForegroundColor Red -NoNewline; Write-Host " This may take a couple of minutes...";
Write-host "-------------------------------------------------------------------------------------`n"
$DeploymentList = Get-CMDeployment | Where-Object {$_.CreationTime -le $Deletedate}
$DeploymentNumber=0
Write-Host ("Found " + $DeploymentList.Count + " deployments older than specified date`n") -ForegroundColor Yellow
foreach ($Deployment in $DeploymentList)
{
$DeploymentID = $Deployment.DeploymentID
$DeploymentName = $Deployment.ApplicationName
$DeploymentNumber++
# User Prompt
Write-Host ""
Write-host "--------------- " -NoNewline; Write-host "DEPLOYMENT #"$DeploymentNumber -ForegroundColor Red -NoNewline; Write-host " ---------------------------------------------------------"
Write-Host ("Deployment Name: " + $DeploymentName)
Write-Host ("Targetted Collection: " + $Deployment.CollectionName)
Write-Host ("Deployment ID: " + $Deployment.DeploymentID)
Write-Host ("Creation Date: " + $Deployment.CreationTime)
Write-Host ("Deployment Time: " + $Deployment.DeploymentTime)
Write-Host ("Enforcement Deadline: " + $Deployment.EnforcementDeadline)
Write-Host ("Feature Type: " + $Deployment.FeatureType)
Write-host "----------------------------------------------------------------------------------------`n"
# User Confirmation
If ((Read-Host -Prompt "Type `"Y`" to delete the deployment, any other key to skip") -ieq "Y")
{
Try
{
#Delete the deployment based on the Deployment Feature Type
Switch ($Deployment.FeatureType)
{
#Application
1{Remove-CMDeployment -DeploymentId $DeploymentID -ApplicationName $DeploymentName -ErrorAction Stop -Force
Write-Host "Sucessfully Deleted Application Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
# Package/Program
2{
$AdvertFilter = "AdvertisementID='$DeploymentID'"
#gwmi sms_advertisement -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_Advertisement -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Program Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
#Software Update
5{
$AdvertFilter = "AssignmentUniqueID='$DeploymentID'"
#gwmi SMS_UpdatesAssignment -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_UpdatesAssignment -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Software Update Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
#Baseline
6{
$AdvertFilter = "AssignmentUniqueID='$DeploymentID'"
#gwmi sms_baselineassignment -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_baselineassignment -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Baseline Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
#Task Sequence
7{
$AdvertFilter = "AdvertisementID='$DeploymentID'"
#gwmi sms_advertisement -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_Advertisement -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Task Sequence Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
Default {
Write-Host ("Feature Type not supported" + $Deployment.FeatureType)}
}
}
Catch{Write-Host "Can't delete deployment #"$DeploymentNumber -ForegroundColor Red -BackgroundColor Black }
}
}
#############################################################################
# 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.")}
}
}
}<#############################################################################
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 ***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