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

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




'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"


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

End If



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


        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_" + _


                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


        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)






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



            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)







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


                    End If






End Sub


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



Comments (4)

  1. Jeff Doty says:

    Thanks for writing the script, but can you give me an example of how the command line execution should look?  It looks like I need to specify parameters for the script to execute properly.  Before setting it to run in Task Manager I wanted to run it manually to make sure it was functioning correctly.  Thanks.

  2. Chris says:

    If you do not tell people how to use this script and how to set/use arguments (username password, smsserver) then is usless for most of your Readers…

    Hope ypu will add some comment rows to clarify how tos 🙂 It will be surely appreciated


  3. Omer Yaseen says:

    Awesome post. There is no need to explain arguments and variables, as this is not a classroom, rather a solution for the issue, which an experienced person needs to know before playing with scripts!

    It is like some gifts you a plane and then you crib saying I dont know how to ride a bicycle !!! 🙂

  4. Louis says:

    please, as asked before indeed I also have the need of knowing the cmd line command to start the script??


Skip to main content