Export all user information in Active Directory to Excel

I need to obtain a detailed list of all user objects within all OU's and export it to excel, including whether or not the the "PASSWORD NEVER EXPIRES" checkbox is selected on the "ACCOUNT" tab.
LenCepedaAsked:
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.

Hendrik WieseInformation Security ManagerCommented:
When you say all information, what exactly do you mean?
1. Groups their part of
2. Username...
0
Neil RussellTechnical Development LeadCommented:
With ALL information you might not have enough columns in Excell!!
Define "ALL"
 
0
LenCepedaAuthor Commented:
Just name, email, phone, webpage, office etc
not username

also, i don't have excel installed on the DC - if i can't create an excel file, how about a CSV instead?
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

LenCepedaAuthor Commented:
sorry, should have clarified, i am looking for personal information, name, email address, office, web page, office - i don't need to know which groups etc, the most important thing is finding out whether or not the PASSWORD NEVER EXPIRES" is checked off
0
athomsfereCommented:
we would really need exactly whats needed...

dsquery /  dsget from dstools is likely the easiest solution:

dsquery computer "ou=CCI,dc=corp,dc=company,dc=com" -limit 10 | dsget computer -Samid

And add the needed switches for additional information
0
UK_AndyCommented:
0
LenCepedaAuthor Commented:
This is all i need - taken from the user properties screen.

General TAB
First Name
Last Name
Description
Office
Telephone Number
Email
Web Page

Address TAB
Street
City
State
Zip

Account TAB
User cannot change password:
Password never Expires

Telephones TAB
Notes
0
RobSampsonCommented:
Hi there,
Try this script.  It will output the required information for all users in your domain.
Regards,
Rob.
0
RobSampsonCommented:
Ooops, forgot the code...
Const ADS_UF_ACCOUNTDISABLE = 2
Const CHANGE_PASSWORD_GUID = "{AB721A53-1E2F-11D0-9819-00AA0040529B}"
Const ADS_RIGHT_DS_CONTROL_ACCESS = &H100
Const ADS_ACETYPE_ACCESS_ALLOWED_OBJECT = &H5
Const ADS_ACETYPE_ACCESS_DENIED_OBJECT = &H6
Const ADS_ACEFLAG_OBJECT_TYPE_PRESENT = &H1

strOutputFile = "User_Details.csv"

strOUPath = ""

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
If Trim(strOUPath) <> "" Then
	If Right(strOUPath, 1) <> "," Then strOUPath = strOUPath & ","
Else
	strOUPath = ""
End If
objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
    "<GC://" & strOUPath & objRootDSE.Get("defaultNamingContext") & ">;(objectCategory=User)" & _
        ";userAccountControl,distinguishedName;subtree"  
Set objRecordSet = objCommand.Execute
 
strDetails = """User Name"",""First Name"",""Last Name"",""Description"",""Office"",""Telephone Number"",""Email"",""Web Page"",""Street"",""City"",""State"",""Zip"",""Notes"",""Cannot Change Password"",""Will Never Expire"",""Disabled"""
Do Until objRecordset.EOF
    intUAC=objRecordset.Fields("userAccountControl")
    Set objUser = GetObject("LDAP://" & objRecordset.Fields("distinguishedName"))
    If TypeName(objUser.Description) = "Variant" Then
    	strDescription = Join(objUser.Description)
    Else
    	strDescription = objUser.Description
    End If
    On Error Resume Next
    strEmail = objUser.Mail
    Err.Clear
    On Error GoTo 0
    strDetails = strDetails & VbCrLf & """" & objUser.samAccountName & """," & _
    	"""" & objUser.givenName & """," & _
       	"""" & objUser.sn & """," & _
		"""" & strDescription & """," & _
		"""" & objUser.physicalDeliveryOfficeName & """," & _
		"""" & objUser.telephoneNumber & """," & _
		"""" & strEmail & """," & _
		"""" & objUser.wwwHomePage & """," & _
		"""" & objUser.StreetAddress & """," & _
		"""" & objUser.C & """," & _
		"""" & objUser.St & """," & _
		"""" & objUser.postalCode & """," & _
		"""" & objUser.Notes & ""","

	' Search the ACE to see if SELF has Cannnot Change Password
	' Bind to the user security objects.
	Set objSecDescriptor = objUser.Get("ntSecurityDescriptor")
	Set objDACL = objSecDescriptor.discretionaryAcl
	
	For Each objACE In objDACL
	    If (UCase(objACE.Trustee) = "NT AUTHORITY\SELF") _
		And (UCase(objACE.objectType) = CHANGE_PASSWORD_GUID) _
		And (objACE.AceFlags = 0) _
		And (objACE.AccessMask = ADS_RIGHT_DS_CONTROL_ACCESS) _
		And (objACE.Flags =  ADS_ACEFLAG_OBJECT_TYPE_PRESENT) Then
	        If (objACE.AceType = ADS_ACETYPE_ACCESS_DENIED_OBJECT) Then
				strDetails = strDetails & """Yes"","
			Else
				strDetails = strDetails & """No"","
	        End If
	    End If
	Next

    accountExpires = objUser.AccountExpirationDate
	If accountExpires = "1/1/1970" Or accountExpires = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
		strDetails = strDetails & """No"","
	ElseIf CDate(accountExpires) < Now Then
		strDetails = strDetails & """Yes"","
	Else
		strDetails = strDetails & """Unknown"","	
	End If
    If intUAC And ADS_UF_ACCOUNTDISABLE Then
        strDetails = strDetails & """Yes"""
	Else
		strDetails = strDetails & """No"""
    End If
    objRecordset.MoveNext
Loop

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing

MsgBox "Done. Please see " & strOutputFile

Open in new window

0
sniperuCommented:
Install powershell and PowerShell Commands (CMDLETs) for Active Directory by Quest Software. Then do:

Get-QADUser -IncludeAllProperties -SizeLimit 0 | export-csv filename.csv
0
sniperuCommented:
To get all users that have "password never expires" do:

Get-QADUser -PasswordNeverExpires -SizeLimit 0 | Export-Csv filename.csv
0
LenCepedaAuthor Commented:
RobSampson:  i saved it to the desktop as a .vbs, ran the script and received the following error, any ideas?
LINE: 73
Char: 5
Error: Unspecified Error
Code: 80004005
Source: (null)

sniperu, I will give powersheel a shot as well. thanks
0
RobSampsonCommented:
Oh yeah, the account expiration can cause an error sometimes...try this.
Regards,
Rob.

Const ADS_UF_ACCOUNTDISABLE = 2
Const CHANGE_PASSWORD_GUID = "{AB721A53-1E2F-11D0-9819-00AA0040529B}"
Const ADS_RIGHT_DS_CONTROL_ACCESS = &H100
Const ADS_ACETYPE_ACCESS_ALLOWED_OBJECT = &H5
Const ADS_ACETYPE_ACCESS_DENIED_OBJECT = &H6
Const ADS_ACEFLAG_OBJECT_TYPE_PRESENT = &H1

strOutputFile = "User_Details.csv"

strOUPath = ""

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
If Trim(strOUPath) <> "" Then
	If Right(strOUPath, 1) <> "," Then strOUPath = strOUPath & ","
Else
	strOUPath = ""
End If
objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
    "<GC://" & strOUPath & objRootDSE.Get("defaultNamingContext") & ">;(objectCategory=User)" & _
        ";userAccountControl,distinguishedName;subtree"  
Set objRecordSet = objCommand.Execute
 
strDetails = """User Name"",""First Name"",""Last Name"",""Description"",""Office"",""Telephone Number"",""Email"",""Web Page"",""Street"",""City"",""State"",""Zip"",""Notes"",""Cannot Change Password"",""Will Never Expire"",""Disabled"""
Do Until objRecordset.EOF
    intUAC=objRecordset.Fields("userAccountControl")
    Set objUser = GetObject("LDAP://" & objRecordset.Fields("distinguishedName"))
    If TypeName(objUser.Description) = "Variant" Then
    	strDescription = Join(objUser.Description)
    Else
    	strDescription = objUser.Description
    End If
    On Error Resume Next
    strEmail = objUser.Mail
    Err.Clear
    On Error GoTo 0
    strDetails = strDetails & VbCrLf & """" & objUser.samAccountName & """," & _
    	"""" & objUser.givenName & """," & _
       	"""" & objUser.sn & """," & _
		"""" & strDescription & """," & _
		"""" & objUser.physicalDeliveryOfficeName & """," & _
		"""" & objUser.telephoneNumber & """," & _
		"""" & strEmail & """," & _
		"""" & objUser.wwwHomePage & """," & _
		"""" & objUser.StreetAddress & """," & _
		"""" & objUser.C & """," & _
		"""" & objUser.St & """," & _
		"""" & objUser.postalCode & """," & _
		"""" & objUser.Notes & ""","

	' Search the ACE to see if SELF has Cannnot Change Password
	' Bind to the user security objects.
	Set objSecDescriptor = objUser.Get("ntSecurityDescriptor")
	Set objDACL = objSecDescriptor.discretionaryAcl
	
	For Each objACE In objDACL
	    If (UCase(objACE.Trustee) = "NT AUTHORITY\SELF") _
		And (UCase(objACE.objectType) = CHANGE_PASSWORD_GUID) _
		And (objACE.AceFlags = 0) _
		And (objACE.AccessMask = ADS_RIGHT_DS_CONTROL_ACCESS) _
		And (objACE.Flags =  ADS_ACEFLAG_OBJECT_TYPE_PRESENT) Then
	        If (objACE.AceType = ADS_ACETYPE_ACCESS_DENIED_OBJECT) Then
				strDetails = strDetails & """Yes"","
			Else
				strDetails = strDetails & """No"","
	        End If
	    End If
	Next

	On Error Resume Next
    accountExpires = objUser.AccountExpirationDate
	If accountExpires = "1/1/1970" Or accountExpires = "1/01/1601 10:00:00 AM" Or Err.Number = -2147467259 Then
		strDetails = strDetails & """No"","
	ElseIf CDate(accountExpires) < Now Then
		strDetails = strDetails & """Yes"","
	Else
		strDetails = strDetails & """Unknown"","	
	End If
	Err.Clear
	On Error GoTo 0
    If intUAC And ADS_UF_ACCOUNTDISABLE Then
        strDetails = strDetails & """Yes"""
	Else
		strDetails = strDetails & """No"""
    End If
    objRecordset.MoveNext
Loop

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strDetails
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing

MsgBox "Done. Please see " & strOutputFile

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
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
VB Script

From novice to tech pro — start learning today.