Solved

AD PowerShell Query for Users - Exclude Specific OU's

Posted on 2013-01-25
8
4,620 Views
Last Modified: 2013-01-31
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

However I need to also EXCLUDE two specific Organization Units.  The two OU's are:

ServiceAccounts
SharedMailboxes


Here is the command we currently have:

Get-ADUser -Filter * -Properties Title,Department,LastLogonDate,whenCreated,Enabled -SearchBase "DC=ad,DC=local" |
? {$_.Name -notlike "*ConfRm*" -and $_.SamAccountName -notlike "*RSG*" -and $_.SamAccountName -notlike "*HSG*" -and $_.SamAccountName -notlike "*IG*" -and $_.SamAccountName -notlike "*SystemMailbox*" -and $_.SamAccountName -notlike "*Agilysys*" -and $_.SamAccountName -notlike "*AGYS*"}  |
Select Name,SamAccountName,Title,Department,LastLogonDate,whenCreated,Enabled |
Export-Csv "C:\myscripts\ADusers.csv" –NoTypeInformation
0
Comment
Question by:Twhite0909
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 38820224
You can add condition like following where CN=SharedMailboxes,DC=yourdomain,DC=com and CN=ServiceAccounts,DC=yourdomain,DC=com should be the DistinguishedName of OU's..
-and $_.DistinguishedName -notmatch 'CN=SharedMailboxes,DC=yourdomain,DC=com'
-and $_.DistinguishedName -notmatch 'CN=ServiceAccounts,DC=yourdomain,DC=com'

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

Open in new window

1
 

Author Comment

by:Twhite0909
ID: 38826772
I ran your command but my CSV still shows the User names for the OU's Service accounts and ShareMailboxes.  Your command has the correct name for the OU's which are

SharedMailboxes
ServiceAccounts

But for some reason the command is not doing what it should and grabs these user names and places them in the spreadsheet.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38826809
Just to confirm did you replace 'CN=SharedMailboxes,DC=yourdomain,DC=com' with OU's DistinguishedName?
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:Twhite0909
ID: 38826954
I thought that SharedMailboxes and ServiceAccounts was the OU's distinguished name?  Under AD it shows OU - SharedMailboxes and under here are all my shared mailboxes Same for AccountServices.  Now I did change the CN to OU and it pulled out about 200 accounts that are sharedmailboxes and service accounts but not all of them.

OU=SharedMailboxes,DC=yourdomain,DC=com
OU=ServiceAccounts,DC=yourdomain,DC=com

So this command pulls out most of the user accounts under these 2 OU's but some still remain.  I checked a handful od the ones that remain and they are under these two OU's.  Any ideas why it wouldn't pull them all out?
0
 

Author Comment

by:Twhite0909
ID: 38827531
Im not sure if this helps but attached is a snapshot of our AD infrastructural with SharedMailboxes highlighted
0
 

Author Comment

by:Twhite0909
ID: 38827545
AD pic

ad picADPic.PNG
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 38829764
Are you using following DN's to filter the OU?

OU=SharedMailboxes,DC=AD,DC=Local
OU=ServiceAccounts,DC=AD,DC=Local

I can also see another OU called resources, which you may have to filter it it contains resource mailboxes..

OU=Resources,DC=AD,DC=Local
0
 

Author Closing Comment

by:Twhite0909
ID: 38839871
THANK YOU SUBSUN for all your help
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A safe way to clean winsxs folder from your windows server 2008 R2 editions
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
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…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

696 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