Link to home
Start Free TrialLog in
Avatar of smyers051972
smyers051972Flag for United States of America

asked on

VBSCRIPT to DELETE AD user based on sql query

All,

Based on the code below,  I am maintaining a SQL DB to track users and automate certain aspects of it as a project.  I have some code below need help modifying.  The criteria basically says if the user is DISABLED in the DB and has been disabled for more than 90 days and is NOT on legal hold status, delete from AD all together, see the bottom portion of the text code below.

Any help is greatly appreciated! I would like to add to this code in order to facilitate that requirement and I am aware of the implications behind this :)

At no point will the code read from AD to determine the disabled days field, I am using SQL to determine all this, reports also follow to report who is about to be deleted as well, lots of checks and balances.

What I am putting through into my head is, vbscript should perform a select statement similar to this:
select * from logons (nolock) where DISABLED = 'Y' and DisabledDays > '90' and Legalhold = 'N'

Open in new window


Taking into consideration of the select statement above those users should then be deleted in active directory via VBScript.

Here is the full code below:

Const DB_CONNECT_STRING = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=vdpsql08r2\logon;Initial Catalog=Users"

Dim WSHShell, WSHNetwork, objDomain, UserString
 
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
Set WSHShell = CreateObject("WScript.Shell")
Set WSHNetwork = CreateObject("WScript.Network")
Set objDomain = getObject("LDAP://rootDse")

DomainString = objDomain.Get("dnsHostName")
UserString = WSHNetwork.UserName 
strComputer = WSHNetwork.ComputerName
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn

myCommand.CommandText = "" &_
	+ "UPDATE logons set DisabledDays=DisabledDays+1 where Disabled='Y' and LegalHold <> 'Y' "
myCommand.Execute
myCommand.CommandText = "" &_
	+ "UPDATE logons set DaysLastLogon=DaysLastLogon+1 where LegalHold <> 'Y' "
myCommand.Execute

' Insert code here

myCommand.CommandText = "" &_
	+ "INSERT INTO [DELETED] (Computername,Station,Username,Disabled,DisabledBy,LegalHold,Timestamp,DaysLastLogon,DisabledDate,DisabledDays) " &_
	+ "	SELECT Computername,Station,Username,Disabled,DisabledBy,LegalHold,Timestamp,DaysLastLogon,DisabledDate,DisabledDays FROM LOGONS (NOLOCK) " &_
	+ "	WHERE LOGONS.DISABLED='Y' AND LOGONS.DisabledDays > '90' AND LOGONS.LEGALHOLD <> 'Y' "
myCommand.Execute
myCommand.CommandText = "" &_
	+ "DELETE from LOGONS where DISABLED = 'Y' and DisabledDays > '90' and Legalhold = 'N'"
myCommand.Execute
myConn.Close

Open in new window

Avatar of smyers051972
smyers051972
Flag of United States of America image

ASKER

Put together some updated code... From my research it looks like a record set will probably be best just cant figure out how to put it together:

Const DB_CONNECT_STRING = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=vdpsql08r2\logon;Initial Catalog=Users"

Dim WSHShell, WSHNetwork, objDomain, UserString
 
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
Set WSHShell = CreateObject("WScript.Shell")
Set WSHNetwork = CreateObject("WScript.Network")
Set objDomain = getObject("LDAP://rootDse")

DomainString = objDomain.Get("dnsHostName")
UserString = WSHNetwork.UserName 
strComputer = WSHNetwork.ComputerName
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn

myCommand.CommandText = "" &_
	+ "UPDATE logons set DisabledDays=DisabledDays+1 where Disabled='Y' and LegalHold <> 'Y' "
myCommand.Execute
myCommand.CommandText = "" &_
	+ "UPDATE logons set DaysLastLogon=DaysLastLogon+1 where LegalHold <> 'Y' "
myCommand.Execute

' More code here to delete OLD users
'myCommand.CommandText = "" &_
'	+	"SELECT username FROM LOGONS (NOLOCK) where DisabledDays > '90' and Legalhold = 'N'" &_
'	+	""

myCommand.CommandText = "" &_
	+ "INSERT INTO [DELETED] (Computername,Station,Username,Disabled,DisabledBy,LegalHold,Timestamp,DaysLastLogon,DisabledDate,DisabledDays) " &_
	+ "	SELECT Computername,Station,Username,Disabled,DisabledBy,LegalHold,Timestamp,DaysLastLogon,DisabledDate,DisabledDays FROM LOGONS (NOLOCK) " &_
	+ "	WHERE LOGONS.DISABLED='Y' AND LOGONS.DisabledDays > '90' AND LOGONS.LEGALHOLD <> 'Y' "
myCommand.Execute
myCommand.CommandText = "" &_
	+ "DELETE from LOGONS where DISABLED = 'Y' and DisabledDays > '90' and Legalhold = 'N'"
myCommand.Execute
myCommand.CommandText = "" &_
	+ "UPDATE logons SET LegalHold = 'Y' WHERE username LIKE 'svc_%'"
myCommand.Execute
myConn.Close

Open in new window

Could you simply invoke a powershell command to have the user removed?
The command is: Remove-ADUser
http://technet.microsoft.com/en-us/library/ee617206.aspx

You can also use "Remove-Mailbox" if the user has an exchange mailbox, that will delete the mailbox and the user account from AD.
http://technet.microsoft.com/en-us/library/jj614539.aspx
Avatar of RobSampson
So you have the username identified in the SQL statement when you move them to the deleted table.  You should be able to use this username to search AD, and then, given that users ADsPath, bind to the parent OU, and delete the user by CN.
Set objOU = GetObject("LDAP://ou=hr,dc=fabrikam,dc=com")
objOU.Delete "user", "cn=MyerKen"

Open in new window


As an example, code like this should work:
strSamAccountName = objRS("username")
strADsPath = Get_LDAP_User_Properties("user", "samaccountname", strSamAccountName, "adsPath")
If strADsPath <> "" Then
	Set objUser = GetObject(strADsPath)
	Set objOU = GetObject(objUser.Parent)
	objOU.Delete "user", "cn=" & objUser.CN
End If

Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
      
      ' This is a custom function that connects to the Active Directory, and returns the specific
      ' Active Directory attribute value, of a specific Object.
      ' strObjectType: usually "User" or "Computer"
      ' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
      '             It filters the results by the value of strObjectToGet
      ' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
      '             For example, if you are searching based on the user account name, strSearchField
      '             would be "samAccountName", and strObjectToGet would be that speicific account name,
      '             such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
      ' strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
      '             the home folder path, as defined by the AD, for a specific user, this would be
      '             "homeDirectory".  If you want to return the ADsPath so that you can bind to that
      '             user and get your own parameters from them, then use "ADsPath" as a return string,
      '             then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
      
      ' Now we're checking if the user account passed may have a domain already specified,
      ' in which case we connect to that domain in AD, instead of the default one.
      If InStr(strObjectToGet, "\") > 0 Then
            arrGroupBits = Split(strObjectToGet, "\")
            strDC = arrGroupBits(0)
            strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
            strObjectToGet = arrGroupBits(1)
      Else
      ' Otherwise we just connect to the default domain
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDNSDomain = objRootDSE.Get("defaultNamingContext")
      End If
 
      strBase = "<LDAP://" & strDNSDomain & ">"
      ' Setup ADO objects.
      Set adoCommand = CreateObject("ADODB.Command")
      Set ADOConnection = CreateObject("ADODB.Connection")
      ADOConnection.Provider = "ADsDSOObject"
      ADOConnection.Open "Active Directory Provider"
      adoCommand.ActiveConnection = ADOConnection
 
 
      ' Filter on user objects.
      'strFilter = "(&(objectCategory=person)(objectClass=user))"
      strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"
 
      ' Comma delimited list of attribute values to retrieve.
      strAttributes = strCommaDelimProps
      arrProperties = Split(strCommaDelimProps, ",")
 
      ' Construct the LDAP syntax query.
      strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
      adoCommand.CommandText = strQuery
      ' Define the maximum records to return
      adoCommand.Properties("Page Size") = 100
      adoCommand.Properties("Timeout") = 30
      adoCommand.Properties("Cache Results") = False
 
      ' Run the query.
      Set adoRecordset = adoCommand.Execute
      ' Enumerate the resulting recordset.
      strReturnVal = ""
      Do Until adoRecordset.EOF
          ' Retrieve values and display.
          For intCount = LBound(arrProperties) To UBound(arrProperties)
                If strReturnVal = "" Then
                      strReturnVal = adoRecordset.Fields(intCount).Value
                Else
                      strReturnVal = strReturnVal & vbCrLf & adoRecordset.Fields(intCount).Value
                End If
          Next
          ' Move to the next record in the recordset.
          adoRecordset.MoveNext
      Loop
 
      ' Clean up.
      adoRecordset.Close
      ADOConnection.Close
      Get_LDAP_User_Properties = strReturnVal
 
End Function 

Open in new window


Regards,

Rob.
I dont think thats it because first before any users are moved to the deleted table we need to identify the user based on the select statement to delete from AD, so before we move the user we are doing this:

select username from logons (nolock) where DISABLED = 'Y' and DisabledDays > '90' and Legalhold = 'N'

Open in new window


The criteria is:

1. Must be a disabled account
2. Must be disabled for 90 full days (Deletes on 91st day)
3. Can not be flagged in the DB as a legal hold

If we take each user name whether its 1 or 100 users that should pass to the vb script and cause an account delete in AD on each result. I just dont know how to put that part together.
Sorry guess I am lost on the results above I dont see a connection of the sql results to the deletion of the account in AD?
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
AHHHH! Ok insert the code into my existing, makes sense now, I can try by creating an account and artificially inflating the numbers to match the criteria then.

Ill try it tomorrow.
Cool, let me know how it goes.

I noticed that in that one you use LegalHold = 'N' but in the next one you use LegalHold <> 'Y'.  That will still produce the same result provided that LegalHold is only ever 'N' or 'Y', but you might want to double check.

Rob.
Sorry but I couldn't test this today still putting out another fire but will try tomorrow.
No problem. I know what it's like ;-)
Fires finally out... ill try testing this tomorrow :)
Worked like a charm! I tested everything out and once I knew it wasnt going to delete all my AD users :) I removed the comment on the delete object line and commented the echo for later testing purposes if they should arise.

Thanks a ton for your help and again sorry for the long delay in closing this had a lot of fires the last few days!
One thing I forgot to ask on this, it appears to me the code will handle multiple accounts i.e. 3-5 or more accounts meet the criteria, it should do this for all of them in the same execution? Just wanted to check :)
Great. Glad it worked for you. Yes, it would handle multiple records returned from the SELECT query, but since samAccountName is unique in a domain, it would only ever affect one account in AD per record.

Rob.
so if I have 10 samaccount's it will just delete 1 at a time until it reaches the end of the record set?
Yes, that's right.
Thanks Rob! Appreciate all the help!