?
Solved

query ad for the following info to CSV

Posted on 2011-05-09
12
Medium Priority
?
746 Views
Last Modified: 2012-06-21
I have an auditor in town wanting a query outputted to Excel.  I cannot seem to get *ALL* the fields he wants below.  Can you help me?  

username, Full name, home drive,  pswd can be changed, pswd last set time, Acct locked out, Last logon time, account status (maybe me numeric 512, 514, 528 ), account created date, account created by (userid), groups

I have tried dsquery and DumpSec  but cannot seem to get all the info he needs.  Please help me.

0
Comment
Question by:stowyo
  • 7
  • 5
12 Comments
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35722735
PowerShell and the Quest tools? Makes real short work of this kind of thing:

PowerShell if you're not on Win 7 / 2008 R2: http://support.microsoft.com/kb/968930
Quest tools (free): http://www.quest.com/powershell/activeroles-server.aspx

Then open the Quest folder and PowerShell in the Start Menu:
Get-QADUser SamAccountName, Name, HomeDrive, HomeDirectory, PasswordNeverExpires, `
    AccountIsLockedOut, LastLogon, WhenCreated |
  Export-Csv "out.csv" -NoTypeInformation

Open in new window

Okay, part of the way, and I may have some of those fields wrong, not at work to test.

Account Status - Includes things like Password Last Set, does he really know what he's asking for?
Account Created By - Is not held unless you explicitly logged it. No chance unless you have Security logs going back and already set up auditing.
Groups - No real problem. What format would you like?

Chris
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35722776
Errors abound, the trouble with doing things in a hurry.
Get-QADUser -IncludedProperties userAccountControl | 
  Select-Object SamAccountName, Name, HomeDrive, HomeDirectory, PasswordNeverExpires, `
    AccountIsLockedOut, LastLogon, WhenCreated,
    @{n='UserAccountStatus';e={ $_.UserAccountControl }},
    @{n='Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} |
  Export-Csv "out.csv" -NoTypeInformation

Open in new window

Added a list of groups and the "user account status" field I don't really think he understands (but I'm cynical) :)

If anyone can check my fields for me I'd appreciate it.

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35722892
I will try running this now.  I am on a Windows 7 box querying the directory...
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
LVL 71

Expert Comment

by:Chris Dent
ID: 35723052
I might not have all the field names right, so please yell if anything is missing.

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35723117
Chris-Dent, I think you do have it correct, but it seems it does not like me just cut and pasting..  Should I take this and put it in a batch file or something?  PowerShell is new to me.....  Once somebody shows me once, I will be golden I guess....
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35723131
Copy and paste into the PowerShell console (the second version, the first will die). Then hit return a couple of times (or you'll end up stuck with this prompt: >>).

It doesn't display output, just makes the CSV file, if you want to see what it does, drop the last line and the | at the end of the line immediately above.

Chirs
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35723160
If you get big red errors, please tell me what they say :)

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35723165
So I think this is all to be on one line but I cannot get thh SizeLimit correct.  here is what I am cut/pasting into Quest on my Windows 7 box....Please confirm this is all to be on one line too..  Thanks!

Get-QADUser -IncludedProperties userAccountControl | Select-Object SamAccountName, Name, HomeDrive, HomeDirectory, PasswordNeverExpires, AccountIsLockedOut, LastLogon, WhenCreated, @{n='UserAccountStatus';e={ $_.UserAccountControl }}, @{n='Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} |   Export-Csv "out.csv" -NoTypeInformation -SizeLimit=0
0
 
LVL 71

Accepted Solution

by:
Chris Dent earned 2000 total points
ID: 35723189
Close, SizeLimit needs to go near the beginning. Each | splits a command, we take the output from the first command and pipe it into the next (until we get to the very end and want to export it to a file). It's Quest that needs the SizeLimit bit, so lets have that in:
Get-QADUser -IncludedProperties userAccountControl -SizeLimit 0 | Select-Object SamAccountName, Name, HomeDrive, HomeDirectory, PasswordNeverExpires, AccountIsLockedOut, LastLogon, WhenCreated, @{n='UserAccountStatus';e={ $_.UserAccountControl }}, @{n='Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} |  Export-Csv "out.csv" -NoTypeInformation

Open in new window

It will be happy if you have lint breaks, still if this works...

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35723251
Thanks for the quick reply.  I am running this now - will let you know how it turns out.
0
 
LVL 1

Author Comment

by:stowyo
ID: 35723314
Chris, Thank you.  That was just awesome.  It worked perfectly. - Stowy
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35723320
Excellent, not bad for from-memory-untested snippets :)

Chris
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
A bad practice commonly found during an account life cycle is to set its password to an initial, insecure password. The Password Reset Tool was developed to make the password reset process easier and more secure.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

809 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