?
Solved

AD PowerShell Query for Users - Exclude Specific OU's

Posted on 2013-01-25
8
Medium Priority
?
4,893 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
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…
Suggested Courses

777 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