Shaun Cassells at MyITForum.com

Delete a successful Direct Member from an SMS 2003 / ConfigMgr 2007 collection based on a date in VB

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' The following tool has several inputs and loops
' Inputs:
'       Server Name
'       Site Code
'       Date to query on
'       Blank or a capital Y
'
' This tool will query for direct memberships in all collections before date from input
'
' Created by Shaun Cassells
' http://myitforum.com/cs2/blogs/scassells/default.aspx
'
'
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim loc
Dim oArgs
Dim strComputer, ResID
Dim strServer, strSiteCode
'Dim Rules() As Variant    'Array of rules for DeleteMembershipRules
Dim total, TotalMembers

Set oArgs = WScript.Arguments

If oArgs.Count = 3 Then
 WSCRIPT.ECHO "Please use 3/4 inputs"
 WSCRIPT.ECHO " SMS Server"
 WSCRIPT.ECHO " SMS Site Code"
 WSCRIPT.ECHO " Date to display rules before -ex. '1/1/2008'"
 WSCRIPT.ECHO " Blank displays results / Y = delete"
 WSCRIPT.ECHO ""
End If
If (oArgs.Count < 3) or (oArgs.Count > 4) Then
 WSCRIPT.ECHO "Please use 3/4 inputs"
 WSCRIPT.ECHO " SMS Server"
 WSCRIPT.ECHO " SMS Site Code"
 WSCRIPT.ECHO " Date to display rules before -ex. '1/1/2008'"
 WSCRIPT.ECHO " Blank displays results / Y = delete"
 WSCRIPT.ECHO ""
 WSCRIPT.QUIT
End If
   strServer = oArgs(0)
   strSiteCode = oArgs(1)
   strDate = oArgs(2)
   If oArgs.Count = 4 Then
    bGO = oArgs(3)
   End If

'convert extra chars in date to be correct format
strDate = replace(strDate, "\", "/")
strDate = replace(strDate, "-", "/")

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Set loc = CreateObject( "WbemScripting.SWbemLocator" )
Set WbemServices = loc.ConnectServer( strServer,"root\SMS\site_" & strSiteCode)
'WScript.Echo strComputer & " ResourceID in " & strServer & " is " & ResID

'Remove single or double quotes from date string
strDate = replace(strDate,"'","")
strDate = replace(strDate,"""","")
'wscript.echo strDate

'Query that finds count of successful direct members in all collections
'Excludes SUSFP and ITMU
'Excludes nonstandard resources
strQry =  "SELECT     dbo.v_AdvertisementInfo.CollectionID, dbo.v_AdvertisementInfo.CollectionName, COUNT(dbo.v_CollectionRuleDirect.ResourceID) AS Total " & _
" FROM dbo.v_CollectionRuleDirect INNER JOIN dbo.v_AdvertisementInfo ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_AdvertisementInfo.CollectionID INNER JOIN dbo.v_ClientAdvertisementStatus ON dbo.v_CollectionRuleDirect.ResourceID = dbo.v_ClientAdvertisementStatus.ResourceID AND  dbo.v_AdvertisementInfo.AdvertisementID = dbo.v_ClientAdvertisementStatus.AdvertisementID " & _
" WHERE (dbo.v_ClientAdvertisementStatus.LastStateName = 'succeeded') AND (dbo.v_ClientAdvertisementStatus.LastStatusTime < CAST('" & strDate & "' AS DateTime)) AND (dbo.v_AdvertisementInfo.CollectionName NOT LIKE '%SUSFP%') AND (dbo.v_AdvertisementInfo.CollectionName NOT LIKE '%ITMU%') AND (dbo.v_CollectionRuleDirect.RuleName NOT LIKE 'ResourceID=%') " & _
" and (dbo.v_AdvertisementInfo.CollectionID like '" & strSiteCode & "%') " & _
" GROUP BY dbo.v_AdvertisementInfo.CollectionName, dbo.v_AdvertisementInfo.CollectionID " & _
" ORDER BY Total desc" 'dbo.v_AdvertisementInfo.CollectionID"
'wscript.echo strQry

'Connect SQL
Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
constring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SMS_" & strSiteCode & ";Data Source="& strServer
cn.ConnectionString = constring
cn.Open
Set cmd.ActiveConnection = cn
cmd.Prepared = True
cmd.CommandText = strQry
'wscript.echo strQry
Set rs = cmd.Execute
If rs.EOF Then
 WScript.Echo "Query did not return any succesful direct members !  Please try again." & VBCrLF & "You entered date:  " & strDate
Else
 Wscript.ECHO "------------------------------------------------------------------------------"
 Wscript.ECHO "Collection ID, Number of Identified Members, Collection Name"
 Wscript.ECHO "------------------------------------------------------------------------------"
 'Used to count loops - total number of collections (summary)
 total = 0
 'Used to show how many members returned (summary)
 TotalMembers = 0
 Do Until rs.EOF
  total = total + 1
  TotalMembers = TotalMembers + rs(2)
  If bGO = "Y" Then
   Wscript.ECHO "------------------------------------------------------------------------------"
  End If
  Wscript.Echo rs(0) & ", " & rs(2) & ", " & rs(1)
  
  If bGO = "Y" Then
   'Wscript.ECHO "SMS_Collection.CollectionID=""" & rs(0)& """"
    Set instCollection = WbemServices.Get("SMS_Collection.CollectionID=""" & rs(0)& """")
   
   'Query finds exact number of members to delete
   strQry2 = "SELECT dbo.v_CollectionRuleDirect.RuleName, dbo.v_CollectionRuleDirect.ResourceID " & _
   " FROM dbo.v_CollectionRuleDirect INNER JOIN dbo.v_AdvertisementInfo ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_AdvertisementInfo.CollectionID INNER JOIN dbo.v_ClientAdvertisementStatus ON dbo.v_CollectionRuleDirect.ResourceID = dbo.v_ClientAdvertisementStatus.ResourceID AND dbo.v_AdvertisementInfo.AdvertisementID = dbo.v_ClientAdvertisementStatus.AdvertisementID " & _
    " WHERE     (dbo.v_ClientAdvertisementStatus.LastStateName = 'succeeded') AND (dbo.v_ClientAdvertisementStatus.LastStatusTime < CAST('" & strDate & "' AS DateTime)) AND (dbo.v_CollectionRuleDirect.RuleName NOT LIKE 'ResourceID=%') AND (dbo.v_AdvertisementInfo.CollectionID = '" & rs(0) & "')"
 
   'note array is ZERO to one minus total size
   ReDim Rules((rs(2)-1)) '(0 to rs(2)) array must contain exact number of objects
   i = 0
    
   cmd.CommandText = strQry2
   Set rs2 = cmd.Execute
   If rs2.EOF Then
    WScript.Echo "No succesfully completed direct members found!  Please try again." & VBCrLF & "You entered:  " & strDate
   Else
    Do Until rs2.EOF
     Wscript.Echo rs2(0) & ", " & rs2(1)
     ' Identify and delete the direct rule.
      Set instDirectRule = WbemServices.Get("SMS_CollectionRuleDirect").SpawnInstance_
       instDirectRule.ResourceID = rs2(1)
       Set Rules(i) = instDirectRule
       i = i+1
       'wscript.echo i
     rs2.MoveNext
    loop
   End If
  
  Wscript.ECHO "------------------------------------------------------------------------------"
  WSCRIPT.ECHO "Deleting total of " & rs(2) & " members from " & rs(1)
 
  ' commit rules delete  
  
     'On Error Resume Next
     instCollection.DeleteMembershipRules  Rules
     'On Error GoTo 0
     'Wscript.ECHO "------------------------------------------------------------------------------"
     'Wscript.Echo "*************************Pausing********************************"
     'Wscript.ECHO "------------------------------------------------------------------------------"
     'Wscript.Sleep(5000)
   End If
  Set rs2 = Nothing
 rs.MoveNext
 
 'The following can be used to limit the number of loops.
 ' useful to remove the process lock when doing lots of deletes
 If (bGO = "Y") And (total > 100) Then
  'Call Cleanup
 End If

 Loop
 Wscript.ECHO "------------------------------------------------------------------------------"
 Wscript.ECHO "Total collections = " & Total & VBCrLF & "    Total Members = " & TotalMembers
 Wscript.Echo " for successful direct rule collections before " & strDate
End If
Call Cleanup
Wscript.quit

Sub Cleanup
 cn.Close
 
 Set rs = Nothing
 Set cmd = Nothing
 Set cn = Nothing
 Set instances = NOTHING
 Set WbemServices = NOTHING
 Set strQry = NOTHING
 Set loc = Nothing
 Wscript.quit
End Sub