Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4105
  • Last Modified:

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

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
Twhite0909
Asked:
Twhite0909
  • 20
  • 17
  • 3
1 Solution
 
Mike KlineCommented:
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
 
Twhite0909Author Commented:
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
 
Twhite0909Author Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Mike KlineCommented:
Try

Get-ADuser -filter * -Properties *

Thanks


Mike
0
 
Twhite0909Author Commented:
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
 
Mike KlineCommented:
put commas between the properties, do you still receive the error?

Thanks

Mike
0
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
Is there a naming pattern which you use to identify the resources?
0
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
ATL-ConfRm-Augusta-1st-Floor, is it a SamAccountName? or Object name? or displayname?
0
 
Twhite0909Author Commented:
That is listed in my csv as a SamAccountName
0
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
If this SamAccountName's then $_.SamAccountName -notlike "*ConfRm*" should take care of it..

Are you running Exchange 2010?
0
 
Twhite0909Author Commented:
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
 
SubsunCommented:
Change line no 2
? {$_.SamAccountName -notlike "*ConfRm*" -and $_.SamAccountName -notlike "*RSG*" -and $_.SamAccountName -notlike "*HSG*"} |

Open in new window

0
 
Twhite0909Author Commented:
that worked thank you
0
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
No there is no output from that ne either.  I noticed there is not an Export command in this last one you posted..?
0
 
SubsunCommented:
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
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
Am I becoming annoying yet?  LOL I really do appreciate all your help you have been giving me on this SUB!!
0
 
SubsunCommented:
Not yet.. I have a one year old who teach me patience.. ;-)
0
 
Twhite0909Author Commented:
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
 
SubsunCommented:
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
 
Twhite0909Author Commented:
Ok Thank you SUB for all your help
0
 
SubsunCommented:
You are welcome!
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 20
  • 17
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now