'======================================================================================= ' Name: DeleteExpiredUpdates.vbs ' Version: 0.1 ' Author: Raphael Perez - raphael.perez@dotnetwork.com.br ' Date: 15 August 2011 ' Comment: This script will remove all expired updates from Deployment Package ' ' Updates: ' 0.1 - Raphael Perez - Initial Script ' ' Usage: ' cscript.exe DeleteExpiredUpdates.vbs /SMSProvider:SCCM01 [/PkgID:"ID01;ID02"] [/AssignmentID:"1;2;3"] ' ' /SMSProvider is REQUIRED ' it is the name of your SMS Provider ' ' /PkgID is Optional ' it contains the ID of your package. Use ";" when specifiying more than 1 package ' ie: /PkgID:"00100001" or /PkgIDID:"00100001;00100002;00100003" ' ' /AssignmentID is Optional ' it contains the ID of your Assigments. Use ";" when specifiying more than 1 assignment ' ie: /AssignmentID:"1" or /AssignmentID:"1;2;3" '======================================================================================= 'Option Explicit On Error Resume next const PROGRAM_VERSION = "1.00" Const DISPLAYMESSAGES = false Const WriteEventLog = True WriteLog("Script started! - Ver. " & PROGRAM_VERSION) if StartedByCSCRIPT()=false then WriteLog("ERROR: Please run this script using CSCRIPT.EXE!") WriteLog("Script terminated because of an ERROR!") WScript.Quit(-1) end if Dim objArguments, sSMSProvider, objSWbemServices, sSiteCode, sQuery, sSubQuery Dim objSUPPkg, arrArray, objPkg, arrContent, arrContent2, sPkgID Dim objArray, bError, gValidPkgID, gValidAssignmentID, bPackage, bAssignment bError = False Set objArguments = Wscript.Arguments If objArguments.Count > 0 Then If objArguments.Named.Exists("SmsProvider") Then sSMSProvider = objArguments.Named.Item("SmsProvider") Else bError = True End if If objArguments.Named.Exists("PkgID") Then sPkgID = objArguments.Named.Item("PkgID") bPackage = true Else bPackage = false End If If objArguments.Named.Exists("AssignmentID") Then sAssignmentID = objArguments.Named.Item("AssignmentID") bAssignment = true Else bAssignment = false End If Else bError = true End If If bError then WriteLog(" - Arguments are invalid") Wscript.Echo "Usage: " Wscript.Quit(0) End If WriteLog(" - Arguments are valid") ConnetToSMSProvider If bPackage then If Not HasValidPackageID(sPkgID) Then WriteLog(" - Package(s) " & sPkgID & " is/are invalid") WScript.Quit(0) else WriteLog(" - Package(s) " & sPkgID & " is/are valid") End If End If If bAssignment then If Not HasValidAssignmentID(sAssignmentID) Then WriteLog(" - Package(s) " & sAssignmentID & " is/are invalid") WScript.Quit(0) else WriteLog(" - Package(s) " & sAssignmentID & " is/are valid") End If End If If bPackage then sQuery = "Select * from SMS_SoftwareUpdatesPackage where PackageID='" & gValidPkgID & "'" Set objSUPPkg = objSWbemServices.ExecQuery(sQuery) For Each objPkg In objSUPPkg sSubQuery = "SELECT DISTINCT cc.ContentID, CC.CI_ID 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='" & objPkg.PackageID & "' and IsExpired = 1" Set arrArray = objSWbemServices.ExecQuery(sSubQuery) Set arrContent = CreateObject("System.Collections.ArrayList") bContinue = false For Each objArray In arrArray bContinue = true If Not ValueExistInArray(arrContent, objArray.ContentID) then arrContent.Add objArray.ContentID End If Next For Each objArray In arrContent.ToArray ltext = ltext & ", " & objarray next If bContinue then WriteLog(" - start deleting ID's " & lText) objPkg.RemoveContent arrContent.ToArray, false objPkg.RefreshPkgSource WriteLog(" - finished removing updates to Pkg") End if Next End If If bAssignment then sQuery = "SELECT * FROM SMS_UpdatesAssignment where AssignmentID='" & gValidAssignmentID & "'" Set objSUPPkg = objSWbemServices.ExecQuery(sQuery) For Each objPkg In objSUPPkg sSubQuery = "SELECT CI_ID FROM SMS_SoftwareUpdate WHERE CI_ID IN ( SELECT CI_ID FROM SMS_CIAssignmentToCI WHERE AssignmentID = '" & objPkg.AssignmentID & "') and isExpired = 0" Set arrArray = objSWbemServices.ExecQuery(sSubQuery) Set arrContent2 = CreateObject("System.Collections.ArrayList") bContinue = false For Each objArray In arrArray bContinue = true If Not ValueExistInArray(arrContent2, objArray.CI_ID) then arrContent2.Add objArray.CI_ID End If Next For Each objArray In arrContent2.ToArray ltext = ltext & ", " & objarray Next If bContinue then WriteLog(" - start deleting ID's " & lText) objPkg.AssignedCIs = arrContent2.ToArray objPkg.Put_ WriteLog(" - finished removing updates to Pkg") End if Next End If Set arrContent = Nothing Set arrContent2 = Nothing Set objSWbemServices = Nothing WriteLog("Script finished! - Ver. " & PROGRAM_VERSION) Wscript.Quit(0) '====================================================END=========================================== Sub ConnetToSMSProvider Dim lobjSWbemLocator, lobjProviderLocation, lobjLocation, lobjSWbemServices Set lobjSWbemLocator = CreateObject("WbemScripting.SWbemLocator") lobjSWbemLocator.Security_.AuthenticationLevel = 6 'Packet Privacy. Set objSWbemServices = lobjSWbemLocator.ConnectServer(sSMSProvider, "root\sms") Set lobjProviderLocation = objSWbemServices.InstancesOf("SMS_ProviderLocation") For Each lobjLocation In lobjProviderLocation If lobjLocation.ProviderForLocalSite = True Then WriteLog(" - try to establish WBEM connection: '" & lobjLocation.Machine & "' - 'root/sms/site_" & lobjLocation.SiteCode & "'") Set objSWbemServices = lobjSWbemLocator.ConnectServer(lobjLocation.Machine, "root\sms\site_" + lobjLocation.SiteCode) If err.number <> 0 Then WriteLog("ERROR - failed to establish WBEM connection!") WriteLog("Script terminated because of an ERROR!") WScript.Quit(-1) else WriteLog(" - Connected!") end if sSiteCode = lobjLocation.SiteCode End If Next End Sub Function HasValidAssignmentID(pAssignmentID) Dim lAssignmentTemp, larrAssignment, lAssignment, lValidAssignmentID, lReturn, lobjAssignment, lQuery lAssignmentTemp = "" larrAssignment = Split(pAssignmentID,";") for each lAssignment in larrAssignment If Len(lAssignmentTemp) > 0 Then lAssignmentTemp = lAssignmentTemp & ", " End If lAssignmentTemp = lAssignmentTemp & "'" & lAssignment & "'" Next WriteLog(" - Validating Assignment") lQuery = "SELECT * FROM SMS_UpdatesAssignment where AssignmentID in (" + lAssignmentTemp + ")" lAssignmentID = "" Set larrAssignment = objSWbemServices.ExecQuery(lQuery) For Each lobjAssignment In larrAssignment If lValidAssignmentID <> "" then lValidAssignmentID = lValidAssignmentID & ";" End If lValidAssignmentID = lValidAssignmentID & lobjAssignment.AssignmentID Next gValidAssignmentID = lValidAssignmentID If lValidAssignmentID = "" Then lReturn = false Else lReturn = true End If sAssignmentID = lValidPkgID HasValidAssignmentID = lReturn End Function Function HasValidPackageID(pPkgID) Dim lPkgTemp, larrPkg, lPkg, lValidPkgID, lReturn, lobjPkg, lQuery, lPkgID lPkgTemp = "" larrPkg = Split(pPkgID,";") for each lPkg in larrPkg If Len(lPkgTemp) > 0 Then lPkgTemp = lPkgTemp & ", " End If lPkgTemp = lPkgTemp & "'" & lPkg & "'" Next WriteLog(" - Validating Packages") lQuery = "SELECT pkg.* FROM SMS_SoftwareUpdatesPackage pkg WHERE pkg.PackageID NOT IN (SELECT ctner.InstanceKey FROM SMS_ObjectContainerItem ctner WHERE ObjectType=2) AND pkg.ActionInProgress!=3 And pkg.PackageID in (" + lPkgTemp + ")" lPkgID = "" Set larrPkg = objSWbemServices.ExecQuery(lQuery) For Each lobjPkg In larrPkg If lValidPkgID <> "" then lValidPkgID = lValidPkgID & ";" End If lValidPkgID = lValidPkgID & lobjPkg.PackageID Next gValidPkgID = lValidPkgID If lValidPkgID = "" Then lReturn = false Else lReturn = true End If sPkgID = lValidPkgID HasValidPackageID = lReturn End Function Sub WriteLog(Message) LogTextToFile Message End Sub Function ValueExistInArray(pArray, pValue) Dim lCount, lFound, lstrItem lFound = False For Each lstrItem in pArray If pValue = lstrItem Then lFound = True Exit For End if Next ValueExistInArray = lFound End Function function StartedByCSCRIPT() if right(ucase(WScript.FullName),11) <> "CSCRIPT.EXE" then StartedByCSCRIPT=false else StartedByCSCRIPT=true end if end Function function LogTextToFile(strTextToLog) if WriteEventLog = false then exit function if DISPLAYMESSAGES = true then wscript.echo strTextToLog Dim objFileSystemObjectForLogfileAccess, objFile, objLogFile, objLogFile2 Dim strFilenameWithoutExtension const intMaxFileSize = 2097152 'strip ".vbs" from the full path scriptname strFilenameWithoutExtension= left(WScript.ScriptFullName,len(WScript.ScriptFullName)-4) On Error Resume Next 'create a reference to the filesystemobject to access the logfiles Set objFileSystemObjectForLogfileAccess = CreateObject("Scripting.FileSystemObject") n = -1 'we need to check the current logfile size 1st - open the current logfile Set objFile = objFileSystemObjectForLogfileAccess.getfile(strFilenameWithoutExtension & ".log") 'check if the the current logfile is bigger than the max file size If objFile.Size > intMaxFileSize Then 'it's bigger - so open the backup log file Set objLogFile2 = objFileSystemObjectForLogfileAccess.getfile(strFilenameWithoutExtension & ".lo_") 'delete the backup file objLogFile2.Delete 'rename the original file to "*.lo_" objFile.name = left(objFile.name, len(objFile.name)-1) & "_" 'close the file objFile.close End If Set objLogFile = objFileSystemObjectForLogfileAccess.opentextfile(strFilenameWithoutExtension & ".log", 8, True) objLogFile.writeline Date & " - " & Time & " - """ & strTextToLog & """" objLogFile.Close If Err Then Err.Clear End function