The story
Our distribution points were getting pretty full, and I wanted to see if I could clean them up before requesting more disk space. I wanted to go out and make sure we remove any packages that were on our DP’s and DP Groups that were not referenced by a task sequence or had an active deployment. I mean, why keep them there?
The Solution
So, to start, I had to figure out a SQL query that would get me the PackageID’s for packages that were not referenced by a task sequence and not in an active deployment. I try to avoid not in queries when I can, so I first wanted to create a query for PackageID’s that were referenced by a task sequence. Now, since I’m just working on packages, I had to join two tables and specify a PackageType of 0.
1 2 3 4 |
SELECT DISTINCT PKG.PackageID FROM v_TaskSequenceReferencesInfo AS TS INNER JOIN v_Package AS PKG ON ts.ReferencePackageID = PKG.PackageID WHERE (PKG.PackageType = 0) |
That got me the packages referenced by a task sequence, now for the packages that have active deployments.
1 2 3 4 |
SELECT DISTINCT PKG.PackageID FROM v_DeploymentSummary AS DS INNER JOIN v_Package AS PKG ON ds.PackageID = PKG.PackageID WHERE (PKG.PackageType = 0) |
By combining the two queries above, I get a list of packages that should be on our DP’s. I find out what packages are on the DP’s that should not be there (and yes, I have to use not in):
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT DISTINCT PkgID FROM v_DPGroupPackages WHERE (PkgID NOT IN (SELECT DISTINCT PKG.PackageID FROM v_DeploymentSummary AS DS INNER JOIN v_Package AS PKG ON ds.PackageID = PKG.PackageID WHERE (PKG.PackageType = 0) UNION SELECT DISTINCT PKG.PackageID FROM v_TaskSequenceReferencesInfo AS TS INNER JOIN v_Package AS PKG ON ts.ReferencePackageID = PKG.PackageID WHERE (PKG.PackageType = 0))) |
That get’s me all the packages that are not supposed to be on the DP’s! We take all this and select the PackageID’s from V_Package where the PackageID is in the result of the query above and the PackageType = 0. We end up with:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT DISTINCT PackageID FROM v_Package WHERE PackageType = 0 AND (PackageID IN (SELECT DISTINCT PkgID FROM v_DPGroupPackages WHERE (PkgID NOT IN (SELECT DISTINCT PKG.PackageID FROM v_DeploymentSummary DS JOIN v_Package PKG on ds.PackageID = PKG.PackageID WHERE PKG.PackageType = 0 UNION select distinct PKG.PackageID from v_TaskSequenceReferencesInfo TS JOIN v_Package PKG on ts.ReferencePackageID = PKG.PackageID WHERE PKG.PackageType = 0)))) |
And there it is! All the PackageID’s that are out there on our DP’s that have no deployments and are not referenced by a task sequence! This is the list we will use to remove them!
The Script
OK, I have put the entire script below and will be referencing the line #’s in there, if you want a copy, you can either cut and paste from below or go to my Bitbucket link above and grab it from there.
This script has 1 required and three optional parameters:
- The first (and only required) is the SiteServer: This is the site server where we are going to be cleaning up.
- Next is the LogLevel, this is the level of information to be logged, 1 = Informational, 2 = Warning (default), and 3 = Error.
- LogFileDir is where you want the log written to, this defaults to C:\Temp
- Finally, we have ClearLog, when you specify this to delete the current logfile, if it exists. Remember, the logfile is going to be <scriptname>.log
OK, let’s look at what this script does.
Lines 1 to 53 are the initial lines to describe the function and define the parameters.
I’d like to jump down to line 301 and come back to the functions as we encounter them. Lines 301 to 305 are setting the logfile variable to “LogFileDir\ScriptName.log.” It does checks to make sure there is a \ at the end of LogFileDir. We also remove the existing log if the flag is set and it exists.
Line 308, we get our first use of the New-LogEntry function. This function is described in an earlier post found here.
Next (line 309), we determine the site code by grabbing it from the SiteServer and then we import the SCCM PowerShell Module. Line 317, we save our current location and then change to the Site: drive.
Since we will be querying WMI several times, and the same ComputerName and Namespace parameters will be used frequently, we save them in a hash table called WMIQueryParameters on lines 320-323.
Lines 326 and 327 get the DataBaseServer and Database names from the site server.
We’re ready to get that list of packages! Lines 330 to 345 set the SQLCommand variable to our query above.
Line 347 calls the Get-SQLQuery function (lines 55 to 100). This function creates a connection, then a command object. This is passed the SQLCommand and then opened. We pull in the data and return the table.
Now, $PackageIDs has a list of Packages that need to be removed. Starting at line 348, we cycle through each ID, logging the package we are processing (line 350) and then we call IsPKGReferenced to make sure nothing has changed. This function (lines 182 to 241) checks for a task sequence reference or an active distribution. If either of those exist, it returns $true, otherwise it returns $false.
Finally, line 355 calls Remove-CMNDPPackage (lines 112 to 179) and removes the package. This function first gets the current list of DP’s/DP Group’s the package is on (line 144-145). Line 148, we verify that we got a return value, if not, then this package isn’t on any DP’s. Now, since the value for the name is in different formats for DP’s and DP Groups, we have to clean it up. First, if we see a ‘\’ in the name, we know it’s a DP and need to extract just the name from there. That is what lines 152 to 154 is doing. If, on the other hand, it doesn’t have a ‘\’ in the name, it’s a DP group and we can just use the name (line 158). Now (lines 160 to 164), if the ContentServerType is 1, it’s on a distribution Point, we use the DistributionPointName parameter, otherwise (Lines 165 to 169), we use the Distribution PointGroupName parameter of Remove-CMContentDistribution cmdlet. You will see that it takes a couple of seconds for this command to run.
Conclusion
I ran this against our environment and it removed 286 packages from our DP’s. Ended up running for over an hour and giving me around 20GB of space.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 |
<# .SYNOPSIS .DESCRIPTION .PARAMETER SiteServer .PARAMETER LogLevel Minimum logging level: 1 - Informational 2 - Warning (default) 3 - Error .PARAMETER LogFileDir Directory where log file will be created. Default C:\Temp. .PARAMETER ClearLog Clears existing log file. .EXAMPLE PS C:\> .\Clean-CMNUnreferencedPackages -SiteServer Server01 This example will redistribute the out of sync packages and make sure they are on Intranet DP's and also keep them on All DP's if they are there. PS C:\> .\Clean-CMNUnreferencedPackages -SiteServer Server01 -ClearLog PS C:\> .\Clean-CMNUnreferencedPackages -SiteServer Server01 -ClearLog $true Both of these are the same as above, except they will clear the logfile as well. .LINK Blog http://configman-notes.com .NOTES #> [CmdletBinding(SupportsShouldProcess=$true)] param( [parameter(Mandatory=$true,HelpMessage="Site server where the SMS Provider is installed")] [ValidateScript({Test-Connection -ComputerName $_ -Count 1 -Quiet})] [string]$SiteServer, [parameter(Mandatory=$false,HelpMessage="Logging Level")] [ValidateSet(1, 2, 3)] [Int32]$LogLevel = 2, [parameter(Mandatory=$false,HelpMessage="Log File Directory")] [string]$LogFileDir = 'C:\Temp\', [parameter(Mandatory=$false,HelpMessage="Clear any existing log file")] [switch]$ClearLog ) #Begin Functions Function Get-SQLQuery { <# .Synopsis This function will query the database $Database on $DatabaseServer using the $SQLCommand .DESCRIPTION This function will query the database $Database on $DatabaseServer using the $SQLCommand. It uses windows authentication .PARAMETER DatabaseServer This is the database server that the query will be run on .PARAMETER Database This is the database on the server to be queried .PARAMETER SQLCommand This is the query to be run .EXAMPLE .LINK http://configman-notes.com .NOTES #> PARAM ( [Parameter(Mandatory=$true)] [String]$DataBaseServer, [Parameter(Mandatory=$true)] [String]$Database, [Parameter(Mandatory=$true)] [String]$SQLCommand ) $ConnectionString = "Data Source=$DataBaseServer;" + "Integrated Security=SSPI; " + "Initial Catalog=$Database" $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString) $Command = new-object system.data.sqlclient.sqlcommand($SQLCommand,$Connection) $Connection.Open() $Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $Command $DataSet = New-Object System.Data.DataSet $Adapter.Fill($DataSet) | Out-Null $Connection.Close() Return $DataSet.Tables } #End Get-SQLQuery Function Remove-CMNDPPackage { <# .Synopsis This function will remove the package from the DP's and DP Group's. .DESCRIPTION This function will remove the package from the DP's and DP Group's. .PARAMETER PackageID This is the PackageID to be removed .EXAMPLE .LINK http://configman-notes.com .NOTES #> Param ( [Parameter(Mandatory=$true)] [String]$PackageID ) #First, get a list of DP/DPGroup's the package is on New-LogEntry 'Starting Function Remove-CMNDPPackage' 1 'Remove-CMNDPPackage' New-LogEntry "Package - $PackageID" 1 'Remove-CMNDPPackage' #Get DP deployment status for PackageID $Query = "SELECT * FROM SMS_PackageContentServerInfo where ObjectID = '$PackageID'" $DPStatus = Get-WmiObject -Query $Query @WMIQueryParameters #Go through and remove each DP/DPGroup, if it exists if ($DPStatus) { foreach($DP in $DPStatus) { if ($DP.Name -match '\\') { $DPName = $DP.Name -replace '([\[])','[$1]' -replace '(\\)','$1' -replace '\\\\(.*)', '$1' } else { $DPName = $DP.Name } if ($DP.ContentServerType -eq 1) { New-LogEntry "Removing Package $PackageID from $DPName" 1 'Remove-CMNDPPackage' Remove-CMContentDistribution -PackageId $PackageID -DistributionPointName $DPName -ErrorAction SilentlyContinue -Force } else { New-LogEntry "Removing Package $PackageID from $DPName" 1 'Remove-CMNDPPackage' Remove-CMContentDistribution -PackageId $PackageID -DistributionPointGroupName $DPName -ErrorAction SilentlyContinue -Force } } } #If DPStatus is null, no package content is distributed else { New-LogEntry "Package $PackageID is not currently distributed" 1 'Remove-CMNDPPackage' } } #End Remove-CMNDPPackage Function IsPKGReferenced { <# .Synopsis This function will return true if the PackageID is referenced by a task sequence or used in a deployment .DESCRIPTION This function will return true if the PackageID is referenced by a task sequence or used in a deployment .PARAMETER PackageID This is the PackageID to be checked .EXAMPLE .LINK http://configman-notes.com .NOTES #> Param ( [parameter(Mandatory=$True)] [String]$PackageID ) New-LogEntry 'Starting Function IsPKGReferenced' 1 'IsPKGReferenced' $Package = Get-CMPackage -Id $PackageID New-LogEntry "Package - $PackageID" 1 'IsPKGReferenced' $IsPKGReferenced = $false #Check for task sequence $Query = "SELECT * FROM SMS_TaskSequenceReferencesInfo WHERE ReferencePackageID = '$PackageID'" $TaskSequence = Get-WmiObject -Query $Query @WMIQueryParameters if ($TaskSequence) { $IsPKGReferenced = $True New-LogEntry 'It is referenced by a task sequence' 1 'IsPKGReferenced' } else { New-LogEntry 'It is not referenced by a task sequence' 1 'IsPKGReferenced' } #Check for distribution $Query = "SELECT * FROM SMS_DeploymentSummary WHERE PackageID = '$PackageID'" $DistributionStatus = Get-WmiObject -Query $Query @WMIQueryParameters if ($DistributionStatus) { $IsPKGReferenced = $True New-LogEntry 'It is referenced by a distribution' 1 'IsPKGReferenced' } else { New-LogEntry 'It is not referenced by a distribution' 1 'IsPKGReferenced' } New-LogEntry "End Function - Returning $IsPKGReferenced" 1 'IsPKGReferenced' return $IsPKGReferenced } #End IsPKGReferenced #Begin New-LogEntry Function Function New-LogEntry { <# .Synopsis This function will write a log entry that can be read by CMTrace .DESCRIPTION This function will write a log entry that can be read by CMTrace .PARAMETER Entry This is the entry for the log .PARAMETER Type This is the message type 1 - Informational - This is the default 2 - Warning 3 - Error .PARAMETER Component This is the component entry for the log, it can be the function name or it will default to the script name .EXAMPLE .LINK http://configman-notes.com .NOTES #> Param ( [Parameter(Position=0,Mandatory=$true)] [String] $Entry, [Parameter(Position=1,Mandatory=$false)] [ValidateSet(1, 2, 3)] [INT32] $Type = 1, [Parameter(Position=2,Mandatory=$false)] [String] $Component = $ScriptName ) if ($type -ge $Script:LogLevel) { if ($Entry.Length -eq 0) { $Entry = 'N/A' } $TZOffset = ([TimeZoneInfo]::Local).BaseUTcOffset.TotalMinutes $TZOffset = "$(Get-Date -Format "HH:mm:ss.fff")+$(-$TZOffset)" $Entry = "<![LOG[{0}]LOG]!><time=""{2}"" date=""{1}"" component=""{5}"" context="""" type=""{4}"" thread=""{3}"">" -f $Entry, (Get-Date -Format "MM-dd-yyyy"), $TZOffset, $pid, $type, $component $Entry | Out-File $Script:LogFile -Append -Encoding ascii } } #End New-LogEntry #End Functions #Build variables for New-LogEntry Function $ScriptName = $MyInvocation.MyCommand.Name if(-not ($LogFileDir -match '\\$')){$LogFileDir = "$LogFileDir\"} $LogFile = $ScriptName -replace '(.*)\.ps1', '$1' $LogFile = $LogFileDir + $LogFile + '.log' if(($ClearLog) -and (Test-Path $LogFile)) {Remove-Item $LogFile} #Figure out what site we're connecting to. New-LogEntry 'Determining Site Code' $SiteCode = $(Get-WmiObject -ComputerName $SiteServer -Namespace 'root\SMS' -Class SMS_ProviderLocation).SiteCode #Put log here to confirm we connected. New-LogEntry "Connecting to site $SiteCode." import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1') #Save our current location before changing Push-Location Set-Location "$($SiteCode):" | Out-Null $WMIQueryParameters = @{ ComputerName = $SiteServer Namespace = "root\sms\site_$SiteCode" } #Get DB Server and Database name from SCCM $DataBaseServer = $(Get-CMSiteRole -RoleName 'SMS SQL Server' -SiteCode "$SiteCode").NetworkOSPath -replace "\\*(.*)", '$1' $Database = $(Get-CimInstance -ClassName SMS_SiteSystemSummarizer -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Filter "Role = 'SMS SQL SERVER' and SiteCode = '$SiteCode' and ObjectType = 1").SiteObject -replace ".*\\([A-Z_]*?)\\$", '$+' #This query gets the PackageIDs that are on DP's, but not referenced by a task sequence or have a deployment $SQLCommand = "SELECT DISTINCT PackageID` FROM v_Package` WHERE PackageType = 0` AND (PackageID IN` (SELECT DISTINCT PkgID` FROM v_DPGroupPackages` WHERE (PkgID NOT IN` (SELECT DISTINCT PKG.PackageID` FROM v_DeploymentSummary DS` JOIN v_Package PKG on ds.PackageID = PKG.PackageID` WHERE PKG.PackageType = 0` UNION` select distinct PKG.PackageID` from v_TaskSequenceReferencesInfo TS` JOIN v_Package PKG on ts.ReferencePackageID = PKG.PackageID` WHERE PKG.PackageType = 0))))" $PackageIDs = Get-SQLQuery $DataBaseServer $Database $SQLCommand foreach ($PackageID in $($PackageIDs).PackageID) { New-LogEntry "Processing Package $PackageID" #As a precaution, double checking to see that no one has made a change before we remove the package if(-not (IsPKGReferenced $PackageID)) { Remove-CMNDPPackage $PackageID } } New-LogEntry 'Finished!!!' #return to the original directory Pop-Location |