Solved

AD PowerShell Query for Users - Exclude Specific OU's

Posted on 2013-01-25
8
4,387 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
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…

708 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

15 Experts available now in Live!

Get 1:1 Help Now