?
Solved

How Do I create a VB script to search Active Directory and modify the account expiration based on criteria?

Posted on 2007-08-02
10
Medium Priority
?
1,731 Views
Last Modified: 2013-11-05
I need assistance with creating a script that can take input from an excel spreadsheet and based on what is in there, automatically populate the account expiration field in Active Directory for user accounts.  Basically, accounts have been created for the last couple of years in our organization but no account expiration date was ever put in for these 600+ users.  So basically, take the username and date that the account should expire on from excel and populate into Active Directory via LDAP?  Example:
take joe.doe, 08/31/2007 from excel and populate the Account tab for the username in Active Directory Users and Computers.  Any help and guidance with this is appreciated!
0
Comment
Question by:jluzanilla
  • 5
  • 4
10 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 19622293
Hi jluzanilla,

Try this script....it opens an excel file with a name defined by this line:
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Users.xls"

and goes through it, setting each account's expiration date.

'===============
'Sources:
'http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept05/hey0902.mspx
'http://www.microsoft.com/technet/scriptcenter/resources/qanda/apr07/hey0405.mspx

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Users.xls"
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
objExcel.Visible = False
objExcel.Workbooks.Open strExcelFile
For intRow = 2 To objExcel.ActiveSheet.Cells(65536, "A").End(xlUp).Row
      strUserName = objExcel.ActiveSheet.Cells(intRow, "A").Value
      strExpiration = objExcel.ActiveSheet.Cells(intRow, "B").Value
      objCommand.CommandText = "SELECT AdsPath FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND (name='" & strUserName & "' OR samAccountName='" & strUserName & "')"
      
      Set objRecordSet = objCommand.Execute
      
      objRecordSet.MoveFirst
      Do Until objRecordSet.EOF
          Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
        
          ' This checks to see if the account DOES NOT expire
            On Error Resume Next
          If objUser.AccountExpirationDate = "1/1/1970" Or objUser.AccountExpirationDate = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
          On Error GoTo 0
              'WScript.Echo objUser.Name & " will not expire. Setting to " & strExpiration
              objUser.AccountExpirationDate = Eval("#" & strExpiration & "#")
                  objUser.SetInfo
            Else
                  'WScript.Echo objUser.Name & " already has an expiration date: " & objUser.AccountExpirationDate
          End If
      
          objRecordSet.MoveNext
      Loop
Next
objExcel.ActiveWorkbook.Close False
objExcel.Quit
Set objExcel = Nothing

MsgBox "Done"
'==================

Regards,

Rob.
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 19627640
Just use dsquery with dsmod.

Example of dsquery/dsmod commandline...

dsquery user -upn jmorgan* | dsmod user -acctexpires 340

I just set Jmorgan's account to expire 340 days from now.

Using Concatenate command in excel...you can parse this into a batchfile.
dsquery user -upn *{name}* | dsmod user -acctexpires {numdays}



0
 

Author Comment

by:jluzanilla
ID: 19628077
Thanks Rob Samson,

I had a question about this.... in the <objCommand.CommandText = "SELECT AdsPath FROM 'LDAP://" > line, i assume i am supposed to put in the LDAP address for our domain?  I keep getting a (24,7) Provider: One or more errors occured during the processing of command.  The line looked similar to this:
objCommand.CommandText = "SELECT AdsPath FROM 'LDAP://dc=somesite,dc=other,dc=com' & strDNSDomain & " ' WHERE object.....

Was there anything else I had to set in the script to get it to work?  other than pointing it to the correct excel spreadsheet?  

_____

hello xuserx2000,

I am utilizing a W2K domain w/ W2k DCs....dsquery is only part of 03, correct?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 65

Expert Comment

by:RobSampson
ID: 19629359
Hi,
No, you don't need to put in your LDAP address, the strDNSDomain string holds that, which gets assigned the value of your rootDNS address.  So if you just leave it the way I posted it, it should work.  Right above the objCommand.CommandText = .... line, if you put MsgBox strDNSDomain you will see dc=somesite,dc=other.

Regards,

Rob.
0
 

Author Comment

by:jluzanilla
ID: 19629703
I did try it without modifying any part of the script and it did give me the "done" message upon completion.  I didnt, however, modify any of the users in the excel spreadsheet.  The spreadsheet just has the username in the first column and the expiration date in the second column like so: first.last and 10/10/2007.  Thanks for your help.  
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19636229
Try to uncomment the WScript.Echo lines so that you get some output as to whether the expiration date currently doesn't expire (and will change), or if it is already set to expire.

Also, if that doesn't give you any prompt at all, then it must be having trouble obtaining a user with that exact name in the Excel spreadsheet.

Put this in just below the objCommand.CommandText = ...... line to output the query string, and make sure it looks correct:
WScript.Echo "Query String:" & VbCrLf & "SELECT AdsPath FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND (name='" & strUserName & "' OR samAccountName='" & strUserName & "')"

Regards,

Rob.
0
 

Author Comment

by:jluzanilla
ID: 19636516
Hello RobSampson,

Ok, finally got it to work...one problem though.  If a user does have a date set, it doesnt change it to what I specified in the spreadsheet.  I want to be able to set a date even if one is already set.  It just says "Already has an expiration date".  Thanks for the help!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 19636552
Sure that's no problem. Just under that WScript.Echo line that says "Date is already set, just copy these two lines from the criteria before it, and it set the date no matter which is the case.

objUser.AccountExpirationDate = Eval("#" & strExpiration & "#")
objUser.SetInfo

So, that full If block looks like this:
          If objUser.AccountExpirationDate = "1/1/1970" Or objUser.AccountExpirationDate = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
          On Error GoTo 0
              'WScript.Echo objUser.Name & " will not expire. Setting to " & strExpiration
              objUser.AccountExpirationDate = Eval("#" & strExpiration & "#")
                  objUser.SetInfo
            Else
                  'WScript.Echo objUser.Name & " already has an expiration date: " & objUser.AccountExpirationDate & vbCrLf & "Setting to " & strExpiration
              objUser.AccountExpirationDate = Eval("#" & strExpiration & "#")
                  objUser.SetInfo
          End If

Regards,

Rob.
0
 

Author Comment

by:jluzanilla
ID: 19636786
Hello RobSampson,

Well its working as you said, thanks.  One more question, how can i set it to resume to the next username in the excel spreadsheet if it encounters a username that does not exist....so, for instance, if I specify john.doe and its not found, how can i set it to report that the username was not found, and resume to the next user in the list?  Thanks for your help!
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 19642566
Hi, sorry about that one, I forgot to check if the user exists.
Here I have added just the
If Not objRecordSet.EOF
line, so that it does not try to check the user account if no records were found.  It will then tell you that the user was not found, and will go to the next user in the spreadsheet.
'==============
'Sources:
'http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept05/hey0902.mspx
'http://www.microsoft.com/technet/scriptcenter/resources/qanda/apr07/hey0405.mspx

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Users.xls"
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
objExcel.Visible = False
objExcel.Workbooks.Open strExcelFile
For intRow = 2 To objExcel.ActiveSheet.Cells(65536, "A").End(xlUp).Row
      strUserName = objExcel.ActiveSheet.Cells(intRow, "A").Value
      strExpiration = objExcel.ActiveSheet.Cells(intRow, "B").Value
      objCommand.CommandText = "SELECT AdsPath FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND (name='" & strUserName & "' OR samAccountName='" & strUserName & "')"
      'WScript.Echo "Query String:" & VbCrLf & "SELECT AdsPath FROM 'LDAP://" & strDNSDomain & "' WHERE objectCategory='user' AND (name='" & strUserName & "' OR samAccountName='" & strUserName & "')"
      
      Set objRecordSet = objCommand.Execute
      
      If Not objRecordSet.EOF Then
            objRecordSet.MoveFirst
            Do Until objRecordSet.EOF
                Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
             
                ' This checks to see if the account DOES NOT expire
                  On Error Resume Next
                If objUser.AccountExpirationDate = "1/1/1970" Or objUser.AccountExpirationDate = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
                On Error GoTo 0
                    'WScript.Echo objUser.Name & " will not expire. Setting to " & strExpiration
                    objUser.AccountExpirationDate = Eval("#" & strExpiration & "#")
                        objUser.SetInfo
                  Else
                        'WScript.Echo objUser.Name & " already has an expiration date: " & objUser.AccountExpirationDate & vbCrLf & "Setting to " & strExpiration
                        objUser.AccountExpirationDate = Eval("#" & strExpiration & "#")
                        objUser.SetInfo
                End If
            
                objRecordSet.MoveNext
            Loop
      Else
            WScript.Echo "The user " & strUserName & " was not found."
      End If
Next
objExcel.ActiveWorkbook.Close False
objExcel.Quit
Set objExcel = Nothing

MsgBox "Done"
'==============

Regards,

Rob.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the basics of formula auditing in Excel 2013.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question