VBScript to extract details of domain users whose account is going to EXPIRE

Hello Experts!

I have a Windows 2000 domain and it's loaded with lots of users placed in different OUs and with different expiry date.
I am looking for a VB Script which will extract following details of all the users whose AD account is set with an expiry date:

Display Name
SAM Account Name / Login ID
Employee ID
When Created
Description
Office
Account Status
Account Expiry Date

Please note that I would want the output to be in the order specified above and if possible then would want it to be extracted into Excel sheet.

Could some expert help me with a script to extract the above details from Active Directory.

I would appreciate if i am provided with the script instead of links to it as Im not so good at programming.

Thanks!

#wyn
LVL 14
ashwynrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SamCaldwellCommented:
Uh...   I develop custom scripts to pay my bills.  I don't mind helping you LEARN to write a script or to provide guidance, which is what this site is for...but if you want free programmers...you get what you pay for.  No offense, but do be careful running any scripts given for free.
0
Shift-3Commented:
Paste the script below into a text file with a .vbs extension.  Customize the value of the strContainer variable with the distinguished name of the OU or domain to search under.  Running the script will create a comma-delimited text file which can be opened in Excel.

Please clarify what you mean by "Employee ID" and "Account Status".  Right now those values will be left blank.


Const ForWriting = 2
Const ADS_SCOPE_SUBTREE = 2
 
On Error Resume Next
 
strContainer = "ou=test,dc=yourdomain,dc=local"
strOutput = "report.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.OpenTextFile(strOutput, ForWriting, True)
 
objOutput.WriteLine "Display Name,SAM Account Name / Login ID,Employee ID,When Created,Description," & _
    "Office,Account Status,Account Expiry Date"
    
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
objCommand.CommandText = _
    "SELECT AdsPath FROM 'LDAP://" & strContainer & "' WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
 
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
  
    dtmAccountExpiration = objUser.AccountExpirationDate 
 
    If Not (Err.Number = -2147467259 OR dtmAccountExpiration = #1/1/1970#) Then
        objOutput.WriteLine objUser.DisplayName & "," & objUser.samAccountName & ",," & _
            objUser.WhenCreated & "," & objUser.Description & "," & objUser.physicalDeliveryOfficeName & _
            ",," & dtmAccountExpiration
    End If
 
    objRecordSet.MoveNext
Loop
 
objOutput.Close

Open in new window

0
exx1976Commented:
Sam - There is no need to be arrogant.  The asker has actively contributed over 75k points, far greater than your recent joining.  Cut the guy a break, huh?


OP - I'm workin on it, give me a few.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

exx1976Commented:
D'oh..  Someone beat me to it.  LOL

Enjoy!
0
exx1976Commented:
Oh, I missed that a couple fields were left blank.  Here's shift-3's script with those two fields added



Const ForWriting = 2
Const ADS_SCOPE_SUBTREE = 2
 
On Error Resume Next
 
strContainer = "ou=test,dc=yourdomain,dc=local"
strOutput = "report.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.OpenTextFile(strOutput, ForWriting, True)
 
objOutput.WriteLine "Display Name,SAM Account Name / Login ID,Employee ID,When Created,Description," & _
    "Office,Account Status,Account Expiry Date"
    
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
objCommand.CommandText = _
    "SELECT AdsPath FROM 'LDAP://" & strContainer & "' WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
 
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
  
    dtmAccountExpiration = objUser.AccountExpirationDate 
	
    If Not (Err.Number = -2147467259 OR dtmAccountExpiration = #1/1/1970#) Then
    	If objUser.AccountDisabled Then
			Status = "Disabled"
		Else
			Status = "Enabled"
		End If
        objOutput.WriteLine objUser.DisplayName & "," & objUser.samAccountName & "," & objUser.EmployeeID & "," & _
            objUser.WhenCreated & "," & objUser.Description & "," & objUser.physicalDeliveryOfficeName & _
            "," & Status & "," & dtmAccountExpiration
    End If
 
    objRecordSet.MoveNext
Loop
 
objOutput.Close

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exx1976Commented:
One mroe change - Shift-3's script checks EVERY object.  Use the one below to search only user objects.


Const ForWriting = 2
Const ADS_SCOPE_SUBTREE = 2
 
On Error Resume Next
 
strContainer = "dc=williamsville,dc=riskmanagement,dc=local"
strOutput = "c:\report.csv"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.OpenTextFile(strOutput, ForWriting, True)
 
objOutput.WriteLine "Display Name,SAM Account Name / Login ID,Employee ID,When Created,Description," & _
    "Office,Account Status,Account Expiry Date"
    
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
objCommand.CommandText = _
    "SELECT AdsPath FROM 'LDAP://" & strContainer & "' WHERE objectCategory='Person' ANDobjectClass='User'"
Set objRecordSet = objCommand.Execute
 
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
  
    dtmAccountExpiration = objUser.AccountExpirationDate 
	WScript.Echo(objUser.DisplayName)
    If Not (Err.Number = -2147467259 OR dtmAccountExpiration = #1/1/1970#) Then
    	If objUser.AccountDisabled Then
			Status = "Disabled"
		Else
			Status = "Enabled"
		End If
		WScript.Echo("found " & objUser.DisplayName)
        objOutput.WriteLine objUser.DisplayName & "," & objUser.samAccountName & "," & objUser.EmployeeID & "," & _
            objUser.WhenCreated & "," & objUser.Description & "," & objUser.physicalDeliveryOfficeName & _
            "," & Status & "," & dtmAccountExpiration
    End If
 
    objRecordSet.MoveNext
Loop
 
objOutput.Close

Open in new window

0
exx1976Commented:
D'oh!  LOL

You'll need to change line 6 in the above code to eb for your domain.
0
exx1976Commented:
Ok, ok, so that one wasn't quite perfect either.

Use this one.

This one doesn't require any editing.  It will automatically look through the entire domain that the logged on user account is in.  I tested it here, and it works.

Const ForWriting = 2
Const ADS_SCOPE_SUBTREE = 2
 
On Error Resume Next
Set objRootDSE = GetObject("LDAP://rootDSE")
strADsPath = "LDAP://" & objRootDSE.Get("defaultNamingContext") 
strOutput = "c:\report.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.OpenTextFile(strOutput, ForWriting, True)
objOutput.WriteLine "Display Name,SAM Account Name / Login ID,Employee ID,When Created,Description," & _
    "Office,Account Status,Account Expiry Date"
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    "SELECT AdsPath FROM '" & strADsPath & "' WHERE objectCategory='Person' AND objectClass='User'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
    dtmAccountExpiration = objUser.AccountExpirationDate 
	If Err.Number <> 0 Then
		Err.Clear
	Elseif CStr(dtmAccountExpiration) <> "1/1/1970" Then
       	If objUser.AccountDisabled Then
			Status = "Disabled"
		Else
			Status = "Enabled"
		End If
        objOutput.WriteLine objUser.DisplayName & "," & objUser.samAccountName & "," & objUser.EmployeeID & "," & _
            objUser.WhenCreated & "," & objUser.Description & "," & objUser.physicalDeliveryOfficeName & _
            "," & Status & "," & dtmAccountExpiration
    End If
    objRecordSet.MoveNext
Loop
objOutput.Close

Open in new window

0
SamCaldwellCommented:
My apologies if my comment seemed arrogant.  I believe in helping people learn to DO rather than beg for free labor.  
0
exx1976Commented:
I completely agree, but not everyone wants that.  Besides if he DOES want to learn, he'll need functional examples.  That's how I learned most of what I know, googling for functional code and then hacking it up and mixing/matching pieces to make it do what I wanted..
0
ashwynrAuthor Commented:
Shift-3: That worked like a charm and what clarification you asked for, that gap has been filled by Exx1976

Exx1976: You've been been spot on what I was looking for mate. Your 1st script worked quite well, however the 2nd & 3rd one didn't, don't know why, I even modified the 2nd one to fit in the correct address in the 'dc=' area but ... It would freeze for a while and then I had to terminate the 'wscript' from Task Mgr.

You guys have been of great help to me so I would distribute points between the 2 of you.

I believe nobody begs at EE, at least I don't think so while I help someone at EE in my area of expertise.

Thanks!

#wyn
0
ashwynrAuthor Commented:
Thanks! That was spot on & quick
0
exx1976Commented:
Hrmm..   The first one, on my systems, scanned ALL the objects in AD (from what I could find objectCategory='User' isn't valid, 'User' is a class type, not an object type)..     The third one I filtered by category person (users and contacts) then further by class=user..  Should've worked, it worked here.   But, if you have a LOT of users, I can see it taking a while..

Either way, glad it worked out for ya.
0
ashwynrAuthor Commented:
Ya, that could have been the possibility but cant be sure.
Anyways...Thanks for all your efforts...really appreciate it.

#wyn
0
Shift-3Commented:
WHERE objectCategory='user' comes straight from Microsoft Scripting Guys boilerplate, so if you think it doesn't work you might want to take it up with them.  See here:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept05/hey0902.mspx
0
exx1976Commented:
Interesting..   I'll look into this further.  All I know is when I ran it in my environment, it returned EVERY object in AD..  User, Contact, Machine, everything.  Hence the change I made to it..

Thanks for the link!!
0
exx1976Commented:
Yeah, that's GOT to be a typo.  I just looked at my account with ADSIEDIT and here are the values:

objectCategory:         CN=Person,CN=Schema,CN=Configuration,DC=DOMAIN,DC=LOCAL
objectClass:               organizationalPerson;person;top;User


??
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.