We help IT Professionals succeed at work.

VBSCRIPT to DELETE AD user based on sql query

844 Views
Last Modified: 2014-05-22
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

Comment
Watch Question

Author

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

jrhelgesonSolutions Architect

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
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.

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
Sorry but I couldn't test this today still putting out another fire but will try tomorrow.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No problem. I know what it's like ;-)

Author

Commented:
Fires finally out... ill try testing this tomorrow :)

Author

Commented:
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!

Author

Commented:
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 :)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
so if I have 10 samaccount's it will just delete 1 at a time until it reaches the end of the record set?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yes, that's right.

Author

Commented:
Thanks Rob! Appreciate all the help!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.