troubleshooting Question

VBSCRIPT to DELETE AD user based on sql query

Avatar of smyers051972
smyers051972Flag for United States of America asked on
VB ScriptWindows Server 2008Active Directory
17 Comments1 Solution850 ViewsLast Modified:
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'

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros