Solved

Export all user information in Active Directory to Excel

Posted on 2010-08-18
13
633 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:LenCepeda
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 21

Expert Comment

by:Hendrik Wiese
ID: 33469473
When you say all information, what exactly do you mean?
1. Groups their part of
2. Username...
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 33469524
With ALL information you might not have enough columns in Excell!!
Define "ALL"
 
0
 

Author Comment

by:LenCepeda
ID: 33469532
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:LenCepeda
ID: 33469549
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
 
LVL 14

Expert Comment

by:athomsfere
ID: 33469584
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
 
LVL 4

Expert Comment

by:UK_Andy
ID: 33469599
0
 

Author Comment

by:LenCepeda
ID: 33469690
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 33471895
Hi there,
Try this script.  It will output the required information for all users in your domain.
Regards,
Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 33471907
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
 
LVL 1

Expert Comment

by:sniperu
ID: 33472662
Install powershell and PowerShell Commands (CMDLETs) for Active Directory by Quest Software. Then do:

Get-QADUser -IncludeAllProperties -SizeLimit 0 | export-csv filename.csv
0
 
LVL 1

Expert Comment

by:sniperu
ID: 33472672
To get all users that have "password never expires" do:

Get-QADUser -PasswordNeverExpires -SizeLimit 0 | Export-Csv filename.csv
0
 

Author Comment

by:LenCepeda
ID: 33474310
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 33480222
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Last week, our Skyport webinar on “How to secure your Active Directory” (https://www.experts-exchange.com/videos/5810/Webinar-Is-Your-Active-Directory-as-Secure-as-You-Think.html?cid=Gene_Skyport) provided 218 attendees with a step-by-step guide for…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

789 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