Solved

AD PowerShell Query for Users - Exclude Specific OU's

Posted on 2013-01-25
8
4,556 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
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…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…

860 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