[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

powershell script to query AD for the following items

Posted on 2011-05-10
17
Medium Priority
?
1,095 Views
Last Modified: 2012-05-11
I cannot figure out how to get the following two items added in  -
(1) date password last changed and
(2) who created the AD account.  

Here is the PowerShell line I am currently using - please help me....

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

I think PasswordLastChanged is not correct, or I do not audit/log this information
0
Comment
Question by:stowyo
  • 9
  • 5
  • 2
  • +1
17 Comments
 
LVL 43

Accepted Solution

by:
Adam Brown earned 1000 total points
ID: 35730366
Unfortunately, the user that created an AD account is not information that is stored in Active Directory. The only way to determine who created an account is to go back through audit logs on the time and date that the account was created. The time the object was created is recorded, so you can figure it out with that information if you have the appropriate audit logs. passwordlastset is the object that holds the time for the password reset, though.
0
 
LVL 7

Expert Comment

by:Chris Patterson
ID: 35730407
Here is the code to query when an account was created or changed:

ldifde -d ou=myorg,dc=company,dc=com -l whencreated, whenchanged -p onelevel -r "(ObjectCategory=user)"
-f con

I am not aware of a way to query who or what account was used in the creation, but i'll keep looking.

Here is the code to in powershell to determine when the password was last changed:

http://www.rlmueller.net/PowerShell/PSPwdLastChanged.txt

0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35731402
acbrown2010's suggestion in-line, any credit for this should go to acbrown2010 since he noted this first :)
Get-QADUser -IncludedProperties userAccountControl -SizeLimit 0 | 
  Select-Object SamAccountName, LastName, FirstName, HomeDrive, HomeDirectory, PasswordLastChanged, 
    PasswordNeverExpires, PasswordLastSet, PasswordExpires, AccountIsLockedOut, LastLogon, WhenCreated,
    @{n='UserAccountStatus';e={ $_.UserAccountControl }},
    @{n='Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} |
  Export-Csv "out1b.csv" -NoTypeInformation

Open in new window

I've added PasswordLastSet and PasswordExpires to the output, both are returned by Get-QADUser and I figure they're both useful.

The who-created-it thing, yeah, you're not going to get that from AD. I'm pretty sure I said that in the last thread as well. It's simply not there. Unless you anticipated this need long ago, then set up and have some kind of historic access to Security logs you simply won't be able to provide the auditor with the information.

I wonder, can you make that the responsibility of HR / Personnel? After all, someone had to say "lets employ xxx", account creation is simply a reaction to that.

Chris
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Author Comment

by:stowyo
ID: 35731411
The LDIFDE did not understand the parameter 'whenchanged', so I removed that and it ran, but it says no entries found.  Of course I put my real company in where mycompany is ...  Here is what I tried.  Please help..

ldifde -d dc=mycompany,dc=com -l whencreated, whenchanged -p onelevel -r "(ObjectCategory=user)" -f con.txt
0
 
LVL 1

Author Comment

by:stowyo
ID: 35731438
Thanks Chris.  I was hoping you would post.  Yea, I added his PasswordlastSet parameter and it worked.  Thanks for the tips!

Apparently the WhoCreatedBy attribute is required for Sarbanes Oxley compliance - at least that is what our white hat auditors are telling us.
0
 
LVL 43

Expert Comment

by:Adam Brown
ID: 35731485
I'm not too familiar with the SOX controls so I can't tell you for sure. In order to be compliant according to their requirements going forward, you'll want to implement a solution of some type to record that information when creating users and keep that up. For Exchange users you can record the user in the CustomAttribute fields. You could theoretically input that information in the Description field if you like as well.
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35731544
Hmm see that goes with anticipating the need. WhoCreatedBy isn't an AD attribute unless you extended the schema, adding the attribute and figured out a way to populate it. Is this something you've done?

We can modify the script to attempt to pull an attribute by that name like this:
Get-QADUser -IncludedProperties userAccountControl, WhoCreatedBy -SizeLimit 0 | 
  Select-Object SamAccountName, LastName, FirstName, HomeDrive, HomeDirectory, PasswordLastChanged, 
    PasswordNeverExpires, PasswordLastSet, PasswordExpires, AccountIsLockedOut, LastLogon, WhenCreated,
    WhoCreatedBy,
    @{n='UserAccountStatus';e={ $_.UserAccountControl }},
    @{n='Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} |
  Export-Csv "out1b.csv" -NoTypeInformation

Open in new window

But that'll only tell you something if it already exists and has data.

Kind of annoying, isn't it? :) I can think of plenty of suggestions for what you might use going forward, but that doesn't seem very helpful in this context. I guess nothing has been configured to pull this kind of detail on a regular basis?

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35731863
Select-Object : A positional parameter cannot be found that accepts argument 'System.Object[]'.
At line:1 char:94
+ Get-QADUser -IncludedProperties userAccountControl, WhoCreatedBy -SizeLimit 0 | Select-Object <<<<  SamAccountName, L
astName, FirstName, HomeDrive, HomeDirectory, PasswordLastChanged, PasswordNeverExpires, PasswordLastSet, PasswordExpir
es, AccountIsLockedOut, LastLogon, WhenCreated, WhoCreatedBy @{n='UserAccountStatus';e={ $_.UserAccountControl }}, @{n=
'Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} | Export-Csv "out1d.csv" -NoTypeInformation
    + CategoryInfo          : InvalidArgument: (:) [Select-Object], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35731878
You've dropped a comma out after the "WhoCreatedBy" entry, pop that back in and it should start working again.

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35731879
Chris,  I put your text all on one line like this

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

Author Comment

by:stowyo
ID: 35731891
OK - Let me try.  Thanks.
0
 
LVL 1

Author Comment

by:stowyo
ID: 35731920
same error
0
 
LVL 71

Assisted Solution

by:Chris Dent
Chris Dent earned 1000 total points
ID: 35731931
Like this?
Get-QADUser -IncludedProperties userAccountControl, WhoCreatedBy -SizeLimit 0 | Select-Object SamAccountName, LastName, FirstName, HomeDrive, HomeDirectory, PasswordLastChanged, PasswordNeverExpires, PasswordLastSet, PasswordExpires, AccountIsLockedOut, LastLogon, WhenCreated, WhoCreatedBy, @{n='UserAccountStatus';e={ $_.UserAccountControl }}, @{n='Groups';e={ $_ | Get-QADMemberOf | Select-Object -ExpandProperty Name }} | Export-Csv "out1d.csv" -NoTypeInformation

Open in new window

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35731964
OK, it is running.  It take a bit of time to pump out 1400 entries.  Will let you know when it is done.  Thank you!
0
 
LVL 1

Author Comment

by:stowyo
ID: 35732132
OK.  It ran good, except WhoCreatedBy is blank.  
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 35732185
Yeah, I thought it would be. It's not an AD attribute by default, you would have to do "stuff" to make it appear and make it populate. Could be the auditor is used to working in places that have a particular piece of software that fills it in.

I'm afraid that puts you back to square one.

Chris
0
 
LVL 1

Author Comment

by:stowyo
ID: 35732351
Chris,  No worries.  You have been a big help
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Let's recap what we learned from yesterday's Skyport Systems webinar.
It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
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 …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

864 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