Script to Clean expired updates from update lists, packages and deployments

Article
10/15/2012

<---My college Kurt has pointed out to me that the code snip to trigger the update synchronization has some problem. So I remove that. As a workaround, you can trigger a full update synchronization from the UI-->

If you use Software Update Point (SUP) in a System Center Configuration Manager 2007 hierarchy, you may face the problem of expired updates.

Consider the following scenario:

Updates are saved in the SMS database as CIs (Configuration Items). All CIs will flow downward to the child sites in the hierarchy.

If the updates are expired, the CI will also be marked as expired. Each site (central or child primary) has the Delete Aged Configuration Manager Data job that removes CIs that have been expired for 90 days (by default) which are not locally referenced. -- “Locally referenced” means it’s not used in any Update Lists created by this site.

So if you create Update Lists on the child site which contains a CI (Update) that has been expired, the CI is not removed from the child site since it is referenced by an local Update List; But the CI is removed from the parent site by the “Delete Aged Configuration Manager Data” job because Update Lists don’t flow up to the parent site.

Now if there is a new third tier primary site (a grandchild site), its SUP WILL NOT get this CI from the central site’s SUP because it has already been removed from the top-most SUP. Its SUP WILL get the Update List which contains the expired CI from its parent site. So the new grandchild site will fail to process this Update List that reference the now-missing CI.

To solve this problem, we have written a script that can clear expired updates from the Update Lists, Update Deployment and Update Packages. As a best practice, we’d suggest to clear up the expired updates and in a one day schedule:

Set the update cleanup age to 1 day. Site Settings->Site Maintenance->Tasks-> Delete Aged Configuration Manager Data Properties->Set Delete data older than (days): to 1
Run the attached script on the site where you create Update Lists every day. You can use Windows Task Schedule to do it.

Here comes the sample script:

Dim Debugging 'If debug logging is enabled

Dim connection 'the Service Object

Dim context 'SMS WMI context

Dim SMSSiteCode 'The site code of the site that we peform clear up

Dim SMSServer 'The machine name of the site server

Dim SMSProvider 'The machine name of the provider

Dim UserName 'username used to connect to the remote server

Dim Password 'Password used to connect to the remote server

DebugLogging = false

Set objArgs = WScript.Arguments

call ParseParameters(objArgs)

Log("SMSServer: "&SMSServer)

Log("UserName: "&UserName)

Log("Password: "&Password)

Set context = CreateObject("WbemScripting.SWbemNamedValueSet")

' Add the standard SMS context qualifiers to the context object.

context.Add "LocaleID", "MS\1033"

context.Add "MachineName", SMSServer

context.Add "ApplicationName", "ClearExpiredUpdates"

Set connection = Connect(SMSServer,UserName,Password)

If Err.Number<>0 Then

Wscript.Echo "Call to connect failed"

End If

'collect all expired CIs

'save to Variable - ExpiredCIs

Query = "SELECT * FROM SMS_SoftwareUpdate " & _

"WHERE IsExpired='TRUE'"

Set ExpiredCIs = connection.ExecQuery(Query, ,wbemFlagForwardOnly Or wbemFlagReturnImmediately, Context)

Log("Expired CI IDs: ")

For each ExpiredCI in ExpiredCIs

Log(ExpiredCI.CI_ID)

'Remove from UpdateList

call RemoveExpiredUpdatesFromUpdateList(connection,context,SMSSiteCode,ExpiredCIs)

'Remove from Deployment

call RemoveExpiredUpdatesFromDeploymens(connection,context,SMSSiteCode,ExpiredCIs)

'Remove Expired updates' content from the packages

call RemoveExpiredUpdatesContent(connection,context,SMSSiteCode,ExpiredCIs)

If Err.Number<>0 Then

WScript.Echo "Clear Failed"

Else

WScript.Echo "Clear Successfully, please trigger a Full Update Synchronization"

End If

WScript.Quit

Function Connect(server, userName, userPassword)

Dim net

Dim localConnection

Dim swbemLocator

Dim swbemServices

Dim providerLoc

Dim location

Set swbemLocator = CreateObject("WbemScripting.SWbemLocator")

swbemLocator.Security_.AuthenticationLevel = 6 'Packet Privacy

' If the server is local, don't supply credentials.

Set net = CreateObject("WScript.NetWork")

If server = "." Then

localConnection = true

userName = ""

userPassword = ""

server = "."

Log("Connecting to local machine...")

Else

Log("Connect to remote machine: "&server&"...")

End If

' Connect to the server.

Set swbemServices= swbemLocator.ConnectServer _

(server, "root\sms",userName,userPassword)

If Err.Number<>0 Then

Wscript.Echo "Couldn't connect: " + Err.Description

Connect = null

Exit Function

End If

Log("Connected successfully")

' Determine where the provider is and connect.

Set providerLoc = swbemServices.InstancesOf("SMS_ProviderLocation")

For Each location In providerLoc

SMSProvider = location.Machine

SMSSiteCode = location.SiteCode

If UCase(net.ComputerName) = UCase(SMSProvider) Then

SMSProvider = "."

userName = ""

userPassword = ""

End If

Log("SMSProvider: "&SMSProvider)

Log("SMSSiteCode: "&SMSSiteCode)

Set swbemServices = swbemLocator.ConnectServer _

(SMSProvider, "root\sms\site_" + _

SMSSiteCode,userName,userPassword)

If Err.Number<>0 Then

Wscript.Echo "Couldn't connect:" + Err.Description

Connect = Null

Exit Function

End If

Log("Connected to provider")

Set Connect = swbemServices

Exit Function

Set Connect = null ' Failed to connect.

End Function

Sub ParseParameters(Parameters)

For each objArg in Parameters

CorrectParametersSofar = false

objArgShort = left(objArg,2)

If objArgShort = "-d" Then

DebugLogging = true

CorrectParametersSofar = true

End If

If objArgShort = "-l" Then

SMSServer = "."

UserName = ""

Password = ""

CorrectParametersSofar = true

End If

If objArgShort = "-r" Then

SMSServer = Right(objArg,Len(objArg)-3)

CorrectParametersSofar = true

End If

If objArgShort = "-u" Then

UserName = Right(objArg,Len(objArg)-3)

CorrectParametersSofar = true

End If

If objArgShort = "-p" Then

Password = Right(objArg,Len(objArg)-3)

CorrectParametersSofar = true

End If

If CorrectParametersSofar = false Then

WScript.Echo "Wrong parameters"

call PrintUsage

WScript.Quit

End If

End Sub

Sub Log(message)

If DebugLogging = true Then

WScript.Echo message

End If

End Sub

Sub PrintUsage

WScript.Echo "Usage of this script: "

WScript.Echo "cscript ExpireClear.vbs [-d] -l|-r:<remoteserver> -u:<Username> -p:<password>"

WScript.Echo "-d enable debug logging"

WScript.Echo "-l connect to local machine"

WScript.Echo "-r connect to remote machine"

WScript.Echo "If you use -r, you need to specify the username and password"

End Sub

Sub RemoveExpiredUpdatesFromUpdateList(swbemServices, swbemContext, siteCode, ExpiredCIs)

Log("====Removing Expired Updates From Update List====")

Query = "SELECT * FROM SMS_AuthorizationList " & _

"WHERE SourceSite='" & siteCode & "'"

Set UpdateLists = swbemServices.ExecQuery(Query, ,wbemFlagForwardOnly Or wbemFlagReturnImmediately, swbemContext)

For each UpdateList in UpdateLists

Log("Checking UpdateList CI_ID = "&UpdateList.CI_ID)

Set UpdateListLazy = swbemServices.Get("SMS_AuthorizationList.CI_ID=" & UpdateList.CI_ID)

UpdatesNumOrg = UBound(UpdateListLazy.Updates) + 1

Log("Updates Number Before: " & UpdatesNumOrg)

ResultUpdates = UpdateListLazy.Updates

For each ExpiredCI in ExpiredCIs

Resultupdates = Filter(Resultupdates,ExpiredCI.CI_ID,FALSE)

UpdatesNumAfter = UBound(Resultupdates) + 1

Log("Updates Number After: " & UpdatesNumAfter)

UpdateListLazy.Updates=Resultupdates

UpdateListLazy.Put_

End Sub

Sub RemoveExpiredUpdatesFromDeploymens(swbemServices, swbemContext, siteCode, ExpiredCIs)

Log("====Removing Expired Updates From Update Deployments====")

Query = "SELECT * FROM SMS_UpdatesAssignment " & _

"WHERE SourceSite='" & siteCode & "'"

Set UpdateAssignments = swbemServices.ExecQuery(Query, ,wbemFlagForwardOnly Or wbemFlagReturnImmediately, swbemContext)

For each UpdateAssignment in UpdateAssignments

Log("Checking UpdateAssignment AssignmentID = "&UpdateAssignment.AssignmentID)

Set UpdateAssignmentLazy = swbemServices.Get("SMS_UpdatesAssignment.AssignmentID=" & UpdateAssignment.AssignmentID)

ContainsExpired = UpdateAssignmentLazy.ContainsExpiredUpdates

If ContainsExpired = true Then

Log("ContainsExpired=true")

CINumOrg = UBound(UpdateAssignmentLazy.AssignedCIs) + 1

ResultCIs = UpdateAssignmentLazy.AssignedCIs

Log("Updates Number Before: " & CINumOrg)

For each ExpiredCI in ExpiredCIs

ResultCIs = Filter(ResultCIs,ExpiredCI.CI_ID,FALSE)

CINumAfter = UBound(ResultCIs) + 1

Log("Updates Number After: " & CINumAfter)

UpdateAssignmentLazy.AssignedCIs=ResultCIs

UpdateAssignmentLazy.Put_

Else

Log("ContainsExpired=false")

End If

End Sub

Sub RemoveExpiredUpdatesContent(swbemServices, swbemContext, siteCode, ExpiredCIs)

Log("====Removing Expired Updates Content From Deployment Packates====")

For each ExpiredCI in ExpiredCIs

Log("Checking ExpiredCI: "&ExpiredCI.CI_ID)

Query = "SELECT * FROM SMS_CIToContent " & _

"WHERE CI_ID=" & ExpiredCI.CI_ID

set ExpiredContents = swbemServices.ExecQuery(Query, ,wbemFlagForwardOnly Or wbemFlagReturnImmediately, swbemContext)

For each ExpiredContent in ExpiredContents

Log("Corresponding ContentID = "&ExpiredContent.ContentID)

Query = "SELECT * FROM SMS_SoftwareUpdatesPackage " & _

"WHERE SourceSite='" & siteCode & "'"

set Packages = swbemServices.ExecQuery(Query, ,wbemFlagForwardOnly Or wbemFlagReturnImmediately, swbemContext)

For each Package in Packages

Log("Checking Deployment Package PackageID= " & Package.PackageID)

Query = "SELECT * FROM SMS_PackageToContent " & _

"WHERE PackageID = '" & Package.PackageID & "'"

set Contents = swbemServices.ExecQuery(Query, ,wbemFlagForwardOnly Or wbemFlagReturnImmediately, swbemContext)

For each Content in Contents

Log("ContentID: "&Content.ContentID)

If Content.ContentID = ExpiredContent.ContentID Then

Log("ExpiredContent: " & Content.ContentID & " - will be delete")

Content.Delete_

End If

End Sub

--- The script is provided as is. Please test before implement in the product environment

Script to Clean expired updates from update lists, packages and deployments

Additional resources