Export information from Active Directory.

Can anyone suggest me the most efficient and the easiest way to export from Active Directory the following information:

- Name & Surname
- Role
- date of last sign in
- date of last password change

Administrator accounts should be included as well. Is there a script for this? Or is there somekind of tool which can be installed on Active Directory for this?

Thanks.
ZUNOAsked:
Who is Participating?
 
Krzysztof PytkoConnect With a Mentor Senior Active Directory EngineerCommented:
OK, if it's a description field then add it to the query

Get-QADUser -name * -SizeLimit 0 | Select sAMAccountName,givenName,sn,lastLogonTimestamp,PasswordLastSet,Description | Export-CSV c:\file.csv

Krzysztof
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
I would suggest for that Quest PowerShell module for AD. This is free and can be downloaded from
http://www.quest.com/powershell/activeroles-server.aspx

after installation, you can run this query

Get-QADUser -name * -SizeLimit 0 | Select sAMAccountName,givenName,sn,lastLogonTimestamp,PasswordLastSet

if you wish, you can export results to file by adding at the end | Export-CSV c:\files.csv

but, please tell me what do you mean saying "Role" ?

Regards,
Krzysztof
0
 
Mike KlineCommented:
adfind is great for this   http://www.joeware.net/freetools/tools/adfind/index.htm


adfind -default -f "&(objectcategory=person)(objectclass=user)"  samaccountname givenname sn lastlogontimestamp pwdlastset -tdca  -nodn -csv > c:\users.csv

One thing about lastlogontimestamp it is accurate between 9-14 days   http://blogs.technet.com/b/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx

What attribute are you using for role?

If you prefer a GUI use adinfo http://www.cjwdev.co.uk/Software/ADReportingTool/Info.html

Thanks

Mike
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.

 
ZUNOAuthor Commented:
Hello guys,

First let me thank you for assistance and tip. I will try that. Role was not clear to me as well, that is the reason I was asking requestor. In my opinion, it should be the field Description.
0
 
Mike KlineCommented:
ok then if you use adfind just add description  after pwdlastset  
0
 
Mike KlineCommented:
By the way nice entries that show the various attribute names

http://www.selfadsi.org/user-attributes-w2k8.htm
http://www.selfadsi.org/user-attributes-w2k3.htm

Thanks
Mike
0
 
ZUNOAuthor Commented:
Great and easy to understand.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
Hey :) Thank you but you could split poitns between us :]

Krzysztof
0
 
ZUNOAuthor Commented:
Yeah, noticed afterwards. Sorry about that guys. It's just that you were first. :)
0
 
Mike KlineCommented:
no problem...just glad you were helped
0
 
ZUNOAuthor Commented:
Getting this error while executing, not quite sure which character is wrong:

The term 'Get-QADUser' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the
 spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:12
+ Get-QADUser <<<<  -name * -SizeLimit 0 | Select sAMAccountName,givenName,sn,lastLogonTimestamp,PasswordLastSet,Descri
ption | Export-CSV c:\file.csv
    + CategoryInfo          : ObjectNotFound: (Get-QADUser:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
0
 
Mike KlineCommented:
Did you download the Quest cmdlets that Krzystof had a link to?
0
 
ZUNOAuthor Commented:
Yes. And I am running it directly from PowerShell.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
OK, but you need to run Quest PowerShell module for Active Directory from Start -> Programs -> Quest Software :)
It looks like you are trying to run this cmdlet from Windows PowerShell (v1 or v2)

I checked this syntax in my lab before posting and it works fine :)

Check once again but in Quest PS

Krzysztof
0
 
ZUNOAuthor Commented:
OMG, I am stupid. Sorry, I did tried that just in the meantime you were probably writing this. And yes, it is working. Oh snap. Thanks again.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
You're welcome :)

Krzysztof
0
 
ZUNOAuthor Commented:
And one other thing, I forgot to enter there. I need to list only Enable accounts. Can you help on that as well. Sorry for bothering again. Thanks.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
OK, for that you need to use more advanced search :)

Get-QADUser -name * -SizeLimit 0 | Where-Object { $_.AccountIsDisabled -eq $False} | Select sAMAccountName,givenName,sn,lastLogonTimestamp,PasswordLastChanged,Description | Export-CSV c:\files.csv

Krzysztof
0
 
ZUNOAuthor Commented:
Krzysztof, for some reason I can see there only one date and time. I don't see the last time they logged in AND date of last password change. There is only one date. Any ideas?

And thanks for all the replies.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
But you can see both attributes in usr's output? You mean, both are the same?
Can you post print screen of that here to see it better?

Krzysztof
0
 
ZUNOAuthor Commented:
I see only one attribute. Sure, I have attached the print screen. I have just wiped out the RIDB number and Name and Surname for obvious reasons. Thanks.
01.jpg
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
OK, this date and time you can see is lastLogonTimestamp or PasswordLastChanged?

Have you checked if you didn't make typo in Select section with attributes to show? What happens if you past code directly from this post, the same blank field is displayed?

Thanks for more details in advance.

Krzysztof
0
 
ZUNOAuthor Commented:
Yes, I have copied the last line exactly as you have posted and here is the output from Excel, one line with myself. I will just change numbers after RIDB to XXXX and will change my name to John Doe.

RIDBXXXXX,"John","Doe","20. 10. 2011 8:05:08",

This is the everything I am getting from it.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
OK, please remove from this syntax "| Export-CSV" and let's see what you can see on the screen. Then tell me, please if that blank field is lastLogonTimestamp attribute or PasswordLastChanged.

For me it looks like lastLogonTimeStamp, which wasn't replicated from other DCs. As Mike wrote in his post above it's [...]accurate between 9-14 days [...]

How many DCs do you have?

Krzysztof
0
 
ZUNOAuthor Commented:
I have removed it. Looks like PasswordLastChanged still does not show up.

We have 2 DC's.

I have another thing I need to add to the script. To exclude accounts that have not logged on in last 3 months.

Thanks.
0
 
ZUNOAuthor Commented:
Oh yes and here is the screenshot.
01.jpg
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
OK, I think we found problem. RIDB accounts were created in tha past with initial password and someone set up "Password Never Expires". So, those passwords were never changed before that's why field is blank. You can verify that using this command in Quest PS for one user to check

Get-QADUser -name RIDB<and-the-rest-information> | Select PasswordNeverExpires,PasswordStatus

and if PasswordNeverExpires is true, that's it!

Krzysztof
0
 
ZUNOAuthor Commented:
Yeah. That's it. In the meantime, I have managed to do it with AD Info. :) Thanks anyways. I have learned something at least out of this. I appreciate your help, very much.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
You're welcome :)
So, do you need still another query for users not logged from 3 months?

Krzysztof
0
 
ZUNOAuthor Commented:
If you don't mind, please, post it. I might need it and at least will learn some more. :) Thanks again.
0
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
OK, then, try this syntax

Get-QADUser -name * | Where-Object { $_.AccountIsDisabled -eq $False -and $_.lastLogonTimestamp -gt "7/27/2011" } | Select sAMAccountName,givenName,sn,lastLogonTimestamp,PasswordLastChanged,Description | Export-CSV c:\file.csv

Krzysztof
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.