Solved

Export all user information in Active Directory to Excel

Posted on 2010-08-18
13
631 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 20

Expert Comment

by:Hendrik Wiese
Comment Utility
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
Comment Utility
With ALL information you might not have enough columns in Excell!!
Define "ALL"
 
0
 

Author Comment

by:LenCepeda
Comment Utility
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
 

Author Comment

by:LenCepeda
Comment Utility
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
Comment Utility
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
Comment Utility
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:LenCepeda
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
To get all users that have "password never expires" do:

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

Author Comment

by:LenCepeda
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now