Solved

Export all user information in Active Directory to Excel

Posted on 2010-08-18
13
632 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
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.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Resolve DNS query failed errors for Exchange
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

832 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