asked on

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

SamCaldwell

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.

SOLUTION

Shift-3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

exx1976

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.

exx1976

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

Enjoy!

ASKER CERTIFIED SOLUTION

exx1976

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

exx1976

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

exx1976

D'oh! LOL

You'll need to change line 6 in the above code to eb for your domain.

exx1976

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

SamCaldwell

My apologies if my comment seemed arrogant. I believe in helping people learn to DO rather than beg for free labor.

exx1976

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

ashwynr

ASKER

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

ashwynr

ASKER

Thanks! That was spot on & quick

exx1976

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.

ashwynr

ASKER

Ya, that could have been the possibility but cant be sure.
Anyways...Thanks for all your efforts...really appreciate it.

#wyn

Shift-3

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

exx1976

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

exx1976

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

??