Need some help with vbscript that deletes direct membership rules

[Lorrec]

Hello everyone. I need some help with a vbscript that removes direct membership rules from SMS collections based on advertisement status.

I got script off of another forum. It works correctly if I do not have a query rule on a collection. However, every collection that I need to run this script on has a query in place.

I am trying to setup the script to only look at direct membership rules. However, I get an error message and I am not quite sure how to fix it.

It was suggested to me to add AND IsDirect='True' to Set objResources = objSMS.ExecQuery("SELECT ResourceID FROM SMS_CollectionMember_a WHERE CollectionID = '" & subColl.SubCollectionID & "' AND IsDirect='True'")

However, I get an error message the error message The error message states that Object doesn't support this property or method: 'subColl.SubCollectionID'.

I have been trying to get the script to only do one collection and the advertisement that is on that collection.

Is it possible to only look at one collection and clean the direct memberships from that collection based on advertisement status? Any help would be greatly appreciated.





Full Code Below:

'Standard File System Object
Set WshFileSys = WScript.CreateObject("Scripting.FileSystemObject")

' Create array objects for email details
Set machineSuccessList = WScript.CreateObject("Scripting.Dictionary")
Set machineNonSuccessList = WScript.CreateObject("Scripting.Dictionary")

' Setup connection to sms server
Set loc = CreateObject("WbemScripting.SWbemLocator")
Set objSMS = loc.ConnectServer(SMSServer, "root\SMS\Site_" & _
SMSSiteCode)

' Creates collection object based on ProcessSubCollection switch above
If ProcessSubCollections = 1 Then
' Get a collection of all subcollections defined from the rootCollID above
Set collList = objSMS.ExecQuery("select * from SMS_CollectToSubCollect WHERE parentCollectionID = '" & rootCollID & "'")
Else
Set collList = objSMS.ExecQuery("select * from SMS_Collection where CollectionID = '" & rootCollID & "'")
End If

' Loop thru all sub collections
For each subColl in collList

If ProcessSubCollections = 1 Then
' Get both resourceID and Advertisements associated with current sub collection
Set objResources = objSMS.ExecQuery("SELECT ResourceID FROM SMS_CollectionMember_a WHERE CollectionID = '" & subColl.SubCollectionID & "' AND IsDirect='True'")
Set objAdvertisements = objSMS.ExecQuery("Associators Of {SMS_Collection.CollectionID='" & SubColl.SubCollectionID & "'} Where ResultClass = SMS_Advertisement")
Set objColl = objSMS.Get("SMS_Collection.CollectionID='" & subColl.SubCollectionID & "'")
Else
' Get both resourceID and Advertisements associated with current sub collection
Set objResources = objSMS.ExecQuery("SELECT ResourceID FROM SMS_CollectionMember_a WHERE CollectionID = '" & subColl.SubCollectionID & "' AND IsDirect='True'")
Set objAdvertisements = objSMS.ExecQuery("Associators Of {SMS_Collection.CollectionID='" & SubColl.CollectionID & "'} Where ResultClass = SMS_Advertisement")
Set objColl = subColl
End If


' Loop thru all advertisements found on current collection
For each objAdvert in objAdvertisements

' Loop thru all resources found in current sub collection
For each objResource in objResources

' Get the last status of the current advertisement and for this resource
Set objStatus = objSMS.ExecQuery("Select * from SMS_ClientAdvertisementStatus where ResourceID = '" & _
objResource.ResourceID & "' AND AdvertisementID = '" & objAdvert.AdvertisementID & "'")

' Retrieve the system name for this resource
Set objSystemResource = objSMS.ExecQuery("Select name from SMS_R_System where resourceID = '" & objResource.ResourceID & "'")

' Loop thru system name object
For each objSystemName in objSystemResource

' Loop thru status object
For each status in objStatus

'Wscript.echo "Computer " & objSystemName.Name & " has a status of (" & status.laststate & " " & status.laststatename & ") for advertisement " & objAdvert.AdvertisementName & " in collection " & objColl.Name

' Declare machine details into dictionary array
Set machineStatus = Wscript.CreateObject("Scripting.Dictionary")

machineStatus.Add "Machine", objSystemName.Name
machineStatus.Add "Status", status.laststatename
machineStatus.Add "Advertisement", objAdvert.AdvertisementName

' Check if status is successful
If status.laststate = successStateID Then
' Remove resource from collection
Set instDirectRule = objSMS.Get("SMS_CollectionRuleDirect").SpawnInstance_
instDirectRule.ResourceID = objResource.ResourceID
objColl.DeleteMembershipRule instDirectRule

' Add machine, status, advertisement to Success status array for email
machineSuccessList.add machineSuccessList.count, machineStatus

'Wscript.echo "Removed computer " & objSystemName.Name & " from " & objColl.Name
Else
'Wscript.echo "Did not remove computer " & objSystemName.Name & " from " & objColl.Name

' Add machine, status, advertisement to NonSuccess status array for email
machineNonSuccessList.add machineNonSuccesslist.count, machineStatus

End If

' Clear array memory
Set machineStatus = Nothing
Next

Next

Next

Next

Next

' Send Email if specified in Constants
If EmailResults = 1 Then
' Send status email of both successful and nonsuccessful machines
Call SendMail(FormatResults(machineSuccessList, machineNonSuccessList))
End If

' Write log if specified in Constants
If LogResults = 1 Then
Call WriteLog(FormatResults(machineSuccessList, machineNonSuccessList), logPath)
End If


Wscript.echo "Done"
Wscript.Quit

' Function to create and send email with results
Function SendMail(strBody)