Avatar of smyers051972
smyers051972
Flag 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

VB ScriptWindows Server 2008Active Directory

Avatar of undefined
Last Comment
smyers051972

8/22/2022 - Mon
smyers051972

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

jrhelgeson

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
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
smyers051972

ASKER
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.
smyers051972

ASKER
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
RobSampson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
smyers051972

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RobSampson

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.
smyers051972

ASKER
Sorry but I couldn't test this today still putting out another fire but will try tomorrow.
RobSampson

No problem. I know what it's like ;-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
smyers051972

ASKER
Fires finally out... ill try testing this tomorrow :)
smyers051972

ASKER
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!
smyers051972

ASKER
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 :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RobSampson

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.
smyers051972

ASKER
so if I have 10 samaccount's it will just delete 1 at a time until it reaches the end of the record set?
RobSampson

Yes, that's right.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
smyers051972

ASKER
Thanks Rob! Appreciate all the help!