?
Solved

Export information from Active Directory.

Posted on 2011-10-26
31
Medium Priority
?
339 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:ZUNO
  • 14
  • 12
  • 5
31 Comments
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036249
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
 
LVL 57

Expert Comment

by:Mike Kline
ID: 37036251
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
 

Author Comment

by:ZUNO
ID: 37036252
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Expert Comment

by:Mike Kline
ID: 37036272
ok then if you use adfind just add description  after pwdlastset  
0
 
LVL 39

Accepted Solution

by:
Krzysztof Pytko earned 2000 total points
ID: 37036273
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
 
LVL 57

Expert Comment

by:Mike Kline
ID: 37036276
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
 

Author Closing Comment

by:ZUNO
ID: 37036281
Great and easy to understand.
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036287
Hey :) Thank you but you could split poitns between us :]

Krzysztof
0
 

Author Comment

by:ZUNO
ID: 37036303
Yeah, noticed afterwards. Sorry about that guys. It's just that you were first. :)
0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 37036319
no problem...just glad you were helped
0
 

Author Comment

by:ZUNO
ID: 37036400
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
 
LVL 57

Expert Comment

by:Mike Kline
ID: 37036407
Did you download the Quest cmdlets that Krzystof had a link to?
0
 

Author Comment

by:ZUNO
ID: 37036411
Yes. And I am running it directly from PowerShell.
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036417
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
 

Author Comment

by:ZUNO
ID: 37036421
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
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036426
You're welcome :)

Krzysztof
0
 

Author Comment

by:ZUNO
ID: 37036439
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
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036584
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
 

Author Comment

by:ZUNO
ID: 37036797
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
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036806
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
 

Author Comment

by:ZUNO
ID: 37036818
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
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036833
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
 

Author Comment

by:ZUNO
ID: 37036867
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
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036886
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
 

Author Comment

by:ZUNO
ID: 37036941
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
 

Author Comment

by:ZUNO
ID: 37036947
Oh yes and here is the screenshot.
01.jpg
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036964
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
 

Author Comment

by:ZUNO
ID: 37036975
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
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37036986
You're welcome :)
So, do you need still another query for users not logged from 3 months?

Krzysztof
0
 

Author Comment

by:ZUNO
ID: 37037086
If you don't mind, please, post it. I might need it and at least will learn some more. :) Thanks again.
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 37037116
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
Here's a look at newsworthy articles and community happenings during the last month.
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 …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

862 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