Remove Unreferenced packages from Distribution Points

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.

SELECT DISTINCT PKG.PackageID
FROM  v_TaskSequenceReferencesInfo AS TS INNER JOIN
         v_Package AS PKG ON ts.ReferencePackageID = PKG.PackageID
WHERE (PKG.PackageType = 0)

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.

SELECT DISTINCT PKG.PackageID
FROM  v_DeploymentSummary AS DS INNER JOIN
         v_Package AS PKG ON ds.PackageID = PKG.PackageID
WHERE (PKG.PackageType = 0)

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):

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)))

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:

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))))

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.

<#
.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

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

The Solution

The Script

Conclusion

Share this:

Like this: