Solved

Create AD Query for Users Creation Date, Department, Disabled/Enabled

Posted on 2013-01-23
40
1,957 Views
Last Modified: 2013-01-25
I need to create either an AD query or Powershell script that can export to CSV showing all users in following format:

 Full Name
 Department
 Job title
 Created
 Last Login
 Enabled/Disabled

I know with the following command I can see User name and Creation date but if someone could add the required entries for the other items that would be awesome!


Get-ADUser -Filter * -Properties whenCreated -SearchBase "DC=ad,DC=local" |
        Select-Object SamAccountName,whenCreated |            
        Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
Comment
Question by:Twhite0909
  • 20
  • 17
  • 3
40 Comments
 
LVL 57

Expert Comment

by:Mike Kline
ID: 38810443
You can use title, department, lastlogontimestamp

I'll check for enabled disabled, there are ways to get only enabled or only disabled users but  putting that in a column (yes/no) I have to test.

Note lastlogontimestamp is accurate between 9-14 days.  If you want exact lastlogon you would need to query the lastlogon attribute on every DC.

Thanks

Mike
0
 

Author Comment

by:Twhite0909
ID: 38810584
How would the command look?


Get-ADUser -Filter * -Properties title department lastlogintimestamp whenCreated -SearchBase "DC=ad,DC=local" |
        Select-Object SamAccountName,whenCreated |            
        Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation


or is there a divider between each needed?  I'm just learning Powershell so excuse my stupidity.  lol
0
 

Author Comment

by:Twhite0909
ID: 38811475
How do I add multiple attributes to this command bc Im getting errors'



Get-ADUser -Filter * -Properties title department lastlogintimestamp whenCreated -SearchBase "DC=ad,DC=local" |
        Select-Object SamAccountName,whenCreated |            
        Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 38811724
Try

Get-ADuser -filter * -Properties *

Thanks


Mike
0
 

Author Comment

by:Twhite0909
ID: 38811760
Thanks Mike but I already know the attributes I want and I cant seem to enter the syntax correctly.  Can someone look at my command below and tell me whats wrong with it.

I want to gather   User name, Creation Date Department, Last Login, job title, enabled/disbaled


Get-ADUser -Filter * -Properties title department lastlogintimestamp whenCreated -SearchBase "DC=ad,DC=local" |
        Select-Object SamAccountName,whenCreated |            
        Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
 
LVL 57

Expert Comment

by:Mike Kline
ID: 38811794
put commas between the properties, do you still receive the error?

Thanks

Mike
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38811829
Try this..
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled |
Select SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38811892
OK so I added comas and it seemed to work out for the MOST PART lol.  The only problem now is Lastlogintimestamp and Enabled/disabled  show up as

"Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"

in my CSV file.  Any suggestions?  Im almost there I really appreciate your help so far




Get-ADUser -Filter * -Properties title,department,whenCreated -SearchBase "DC=ad,DC=local" |
        Select-Object SamAccountName,whenCreated,title,department,lastlogintimestamp,enabled/disabled |            
        Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38811911
Try with LastLogonDate,Enabled
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
Select SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38811932
SubSun That worked GREAT!

One last thing I swear lol we noticed that the User tab in CSV shows the resources like conference rooms and such.  is there a way to exclude resources from being listed and have just grab User Accounts?


Thank you all for all your help this has been awesome!
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38811966
Is there a naming pattern which you use to identify the resources?
0
 

Author Comment

by:Twhite0909
ID: 38814043
Yes we use the Initials of City and then ConfRm then name of the conference room example

ATL-ConfRm
BV-ConfRm
NV-ConfRm

Also the user names are showing up as the actual ID example WhiteT Instead of Tim White.  Is there a way I can have the user name displayed as Last name, First name?


Thanks
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814082
Following code will exclude the accounts which have "-ConfRm" in SamAccountName, Does that give you the required output?
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.SamAccountName -notlike "*-ConfRm"} |
Select SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation

Open in new window

Is there a way I can have the user name displayed as Last name, First name?
If your displayname is in this format then you can add it to the select, else we need to have additional code for that

Select DisplayName,SamAccountName,Title........
0
 

Author Comment

by:Twhite0909
ID: 38814375
This got the full name of user but the conforence rooms are still there so now I have this as my command but still need Confr Rooms removed:

Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.SamAccountName -notlike "*-ConfRm"} |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation


here are some examples of our room names

ATL-ConfRm-Augusta-1st-Floor
BV-ConfRm-DeathCab
GVConfRm-TaylorsBriefing
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814412
ATL-ConfRm-Augusta-1st-Floor, is it a SamAccountName? or Object name? or displayname?
0
 

Author Comment

by:Twhite0909
ID: 38814541
That is listed in my csv as a SamAccountName
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814588
Try..
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.SamAccountName -notlike "*ConfRm*"} |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38814697
That took care of most of them however I found these

IG_ConfRm-(LG) LV - Mission
IG_ConfRm-(Sales) LV Executive
IG_ConfRm-(Sales)-Web Demo
IG_ConfRm-(SM) LV - San Marcos
IG_ConfRm-Arlington
IG_ConfRm-Guest Office
IG_ConfRm-Mojave
IG_ConfRm-Presidio
IG_ConfRm-Tahoe
IG_ConfRm-Tina's Office


It took all other resource conference rooms but those remained,  Any ideas?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38814710
If this SamAccountName's then $_.SamAccountName -notlike "*ConfRm*" should take care of it..

Are you running Exchange 2010?
0
 

Author Comment

by:Twhite0909
ID: 38814870
LOL That actually did get it it is bc I have another TAB called NAME and it was in there. Samaccount got it. Thanks
\

If I wanted to exclude additional resources can I copy the _not like command for example I also have additional resources named

HSG
RSG
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 38814921
Change line no 2
? {$_.SamAccountName -notlike "*ConfRm*" -and $_.SamAccountName -notlike "*RSG*" -and $_.SamAccountName -notlike "*HSG*"} |

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38815151
that worked thank you
0
 

Author Comment

by:Twhite0909
ID: 38815822
One last thing....after going through the list I pulled with this command I am noticing alot of Shared Mailboxes in my users tab.  Is there anyway to make this command not list shared mailboxes or do I have to do the -notlike command for each one?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38815898
I am noticing alot of Shared Mailboxes in my users tab.
Do you mean SamAccountName tab?? else which attribute it is?
If you have specific name pattern then we can exclude it as I mentioned above or if you have this mailboxes created in specific OU then we can exclude it..

If you have exchange 2007 or 2010 and the resource mailboxes is created as room mailbox then you can exclude them..

In simple words, you need to have a specific attribute/particular naming pattern to identify and exclude the resource mailboxes...
0
 

Author Comment

by:Twhite0909
ID: 38816027
Please forgive me I am new to this company and inherited a nightmare of a AD structure.   There is a VERY specific OU we can exclude called SHAREDMAILBOXES  as well as One I wanna exclude called Service Accounts LOL.  Do I exclude in the same fashion as above:

? {$_.SamAccountName -notlike "*SharedMailboxes*" -and $_.SamAccountName -notlike "*ServiceAccounts*"
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38816063
Yes you can. Donot forget to close the curly bracket..
? {$_.SamAccountName -notlike "*SharedMailboxes*" -and $_.SamAccountName -notlike "*ServiceAccounts*"}

Open in new window


If you want to exclude users from specific OU then try with $_.dn -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com'. Replace 'CN=SharedMailboxes,DC=yourdomain,DC=com' with the OU which you want to exclude..
? {$_.dn -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com' -and $_.SamAccountName -notlike "*SharedMailboxes*" -and $_.SamAccountName -notlike "*ServiceAccounts*"}

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38816150
Is this right?


Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.Name -notlike "*-ConfRm" -and $_.SamAccountName -notlike "*RSG*"} |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled | ? {$_.dn -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com' -and $_.SamAccountName -notlike "*SharedMailboxes*" -and $_.SamAccountName -notlike "*ServiceAccounts*"} |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38816176
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.Name -notlike "*ConfRm*" `
-and $_.SamAccountName -notlike "*RSG*" `
	-and $_.dn -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com' `
		-and $_.SamAccountName -notlike "*SharedMailboxes*" `
			-and $_.SamAccountName -notlike "*ServiceAccounts*"} |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38818803
That command brought back a file that is 0KB...??

Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.Name -notlike "*ConfRm*" `
-and $_.SamAccountName -notlike "*RSG*" `
      -and $_.dn -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com' `
            -and $_.SamAccountName -notlike "*SharedMailboxes*" `
                  -and $_.SamAccountName -notlike "*ServiceAccounts*"} |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38818819
Are you getting any output for..
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.Name -notlike "*ConfRm*" `
-and $_.SamAccountName -notlike "*RSG*" `
      -and $_.dn -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com' `
            -and $_.SamAccountName -notlike "*SharedMailboxes*" `
                  -and $_.SamAccountName -notlike "*ServiceAccounts*"}

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38818887
No there is no output from that ne either.  I noticed there is not an Export command in this last one you posted..?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38819056
Try..
Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.Name -notlike "*ConfRm*" `
-and $_.SamAccountName -notlike "*RSG*" `
	-and $_.DistinguishedName -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com' `
		-and $_.SamAccountName -notlike "*SharedMailboxes*" `
			-and $_.SamAccountName -notlike "*ServiceAccounts*"} |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation

Open in new window

0
 

Author Comment

by:Twhite0909
ID: 38819279
That got data but the resources and service accounts are still there.  Although it seems that command would work as The Resources and Service accounts are under OU's named as such.

ServiceAccounts and SharedMailboxes
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38819305
If you are talking about the samaccount name then following filter should take care of it..
-and $_.SamAccountName -notlike "*ServiceAccounts*"
-and $_.SamAccountName -notlike "*SharedMailboxes*"


Else if it is name, you neeed to change it to..
-and $_.name -notlike "*ServiceAccounts*"
-and $_.name -notlike "*SharedMailboxes*"
0
 

Author Comment

by:Twhite0909
ID: 38819306
Am I becoming annoying yet?  LOL I really do appreciate all your help you have been giving me on this SUB!!
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38819517
Not yet.. I have a one year old who teach me patience.. ;-)
0
 

Author Comment

by:Twhite0909
ID: 38819559
Just so I am sure but for NAME and SAMACCOUNT - notlike will only find account names associated with SharedMailbxes and ServiceAccounts in the actual NAME correct?  Would there be a different command to tell this syntax to not pull anything from the OU containers SharedMailboxes and ServiceAccounts?    I know previous attempts to say Samaccount - notlike for something like ConfRm removed any and all user names that had ConfRm in it but if I have an OU named ConfRm I would need another switch fr the command right?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38819980
I think I have already answered this question..
#a38815898

ServiceAccounts and just user accounts, not special attributes added. As a best practice you need to follow a standard to identify them like all service accounts in a specific OU, or All service accounts should follow some naming standard like Test-SA-SQL-Account.. Else it's difficult to identify them..

But If you have created all service accounts with password never expire and user cannot change password, then you can query the accounts which are matching this criteria..

SharedMailbxes, if you have not created them as room mailbox, then again it's difficult to identify them without a naming standard or specific OU.

If you have more queries then I would suggest you to open another question with details about your requirement (You may include  PowerShell zone, so you can get inputs from other PowerShell experts too) :-)
0
 

Author Comment

by:Twhite0909
ID: 38820050
Ok Thank you SUB for all your help
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38820067
You are welcome!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you migrate a Terminal Server licenses server inside the 2008 server family, you can takte advantage of the build-in migration tool. If you like to migrate an older 2003 Server (and the installed client CALs) to a 2008 R2 server for example, you …
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 locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

706 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

18 Experts available now in Live!

Get 1:1 Help Now