?
Solved

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

Posted on 2008-11-13
17
Medium Priority
?
2,047 Views
Last Modified: 2013-12-05
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
0
Comment
Question by:ashwynr
  • 10
  • 3
  • 2
  • +1
17 Comments
 
LVL 1

Expert Comment

by:SamCaldwell
ID: 22952873
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
 
LVL 38

Assisted Solution

by:Shift-3
Shift-3 earned 800 total points
ID: 22953231
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
 
LVL 18

Expert Comment

by:exx1976
ID: 22953500
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 18

Expert Comment

by:exx1976
ID: 22953527
D'oh..  Someone beat me to it.  LOL

Enjoy!
0
 
LVL 18

Accepted Solution

by:
exx1976 earned 1200 total points
ID: 22953579
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
 
LVL 18

Expert Comment

by:exx1976
ID: 22953871
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
 
LVL 18

Expert Comment

by:exx1976
ID: 22953884
D'oh!  LOL

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

Expert Comment

by:exx1976
ID: 22953971
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
 
LVL 1

Expert Comment

by:SamCaldwell
ID: 22954246
My apologies if my comment seemed arrogant.  I believe in helping people learn to DO rather than beg for free labor.  
0
 
LVL 18

Expert Comment

by:exx1976
ID: 22954285
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
 
LVL 14

Author Comment

by:ashwynr
ID: 22954558
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
 
LVL 14

Author Closing Comment

by:ashwynr
ID: 31516490
Thanks! That was spot on & quick
0
 
LVL 18

Expert Comment

by:exx1976
ID: 22954603
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
 
LVL 14

Author Comment

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

#wyn
0
 
LVL 38

Expert Comment

by:Shift-3
ID: 22959795
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
 
LVL 18

Expert Comment

by:exx1976
ID: 22960301
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
 
LVL 18

Expert Comment

by:exx1976
ID: 22960661
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

Featured Post

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.

Question has a verified solution.

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

Uncontrolled local administrators groups within any organization pose a huge security risk. Because these groups are locally managed it becomes difficult to audit and maintain them.
Microsoft Office 365 is a subscriptions based service which includes services like Exchange Online and Skype for business Online. These services integrate with Microsoft's online version of Active Directory called Azure Active Directory.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

839 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