Solved

AD PowerShell Query for Users - Exclude Specific OU's

Posted on 2013-01-25
8
4,519 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

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.

Question has a verified solution.

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

Set OWA language and time zone in Exchange for individuals, all users or per database.
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 walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

832 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