Counting users in active directory excluding on various criteria

Hello,

I need to count the users in my active directory environment but I need to exclude accounts that match any of the following criteria from the count;

Account disabled
Account expired
Has not logged in for 90 days
Contains "admin" anywhere in the username
Contains "template" anywhere in the username
Contains "test" anywhere in the username
Is in the ServiceAccounts OU
Contains "built-in" anywhere in the description
Contains "email verification" anywhere in the description

It would be nice if the script gave;
a total count of users in AD
a count for each exclusion criteria
a count of total users in AD - the excluded users

I expect there will be some overlap with the criteria

I am open to doing this with powershell, VBScript, LDAP query, batch file, or any other method.
LVL 23
Erik BjersPrincipal Systems AdministratorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
footechConnect With a Mentor Commented:
coraxal had a great start, so I just built off of that.  Two things I noticed, it didn't take into account any overlap of the exclusion criteria, and the final line wouldn't work as written.  I modified his code to correct these, and also only using built-in PS cmdlets (no Quest).
$ADUser_admin = @()
$ADUser_template = @()
$ADUser_test = @()
$ADUser_builtin = @()
$ADUser_email = @()
$ADUser_OU = @()
$ADUser_expired = @()
$ADUser_disabled = @()
$ADUser_notloggedonfor = @()
$ADUser_excluded = @()

$refDate = (Get-Date).AddDays(-90)

$ADQuery = Get-ADUser -filter * `
	-properties UserPrincipalName,SamAccountName,Description,DistinguishedName,AccountExpirationDate,Enabled,LastLogonDate
			
$ADQuery | % {
			
	if($_.SamAccountName -like "*admin*"){ $ADUser_admin += @($_.DistinguishedName) } 
	
	if($_.SamAccountName -like "*template*"){ $ADUser_template += @($_.DistinguishedName) } 
	
	if($_.SamAccountName -like "*test*"){ $ADUser_test += @($_.DistinguishedName) } 
	
	if($_.Description -like "*built-in*"){ $ADUser_builtin += @($_.DistinguishedName) } 
					
	if($_.Description -like "*email verification*"){ $ADUser_email += @($_.DistinguishedName) } 			
	
	if($_.DistinguishedName -like "*OU=ServiceAccounts*"){ $ADUser_OU += @($_.DistinguishedName) }
	
	if(($_.AccountExpirationDate) -and ($_.AccountExpirationDate -lt $(Get-Date))){ $ADUser_expired += @($_.DistinguishedName) }
	
	if($_.Enabled -eq $False){ $ADUser_disabled += @($_.DistinguishedName) }
	
	if($_.LastLogonDate -le $refDate){ $ADUser_notloggedonfor += @($_.DistinguishedName) }
				
				
			
}

$ADUser_excluded = Compare-Object $ADUser_admin $ADQuery -property DistinguishedName -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_template -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_test -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_builtin -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_email -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_OU -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_expired -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_disabled -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_notloggedonfor -passthru | where { ($_.SideIndicator -eq "=>")} 

<#
It would be nice if the script gave;
a total count of users in AD
a count for each exclusion criteria
a count of total users in AD - the excluded users
#>


Write-Output "Total AD users:  $($ADQuery.Count)"

Write-Output "Account disabled count:  $($ADUser_disabled.Count)"
Write-Output "Account expired count:  $($ADUser_expired.Count)"
Write-Output "Account not logged in over 90 days count:  $($ADUser_notloggedonfor.Count)"
Write-Output "Account containing 'admin' anywhere in the username count:  $($ADUser_admin.Count)"
Write-Output "Account containing 'template' anywhere in the username count:  $($ADUser_template.Count)"
Write-Output "Account containing 'test' anywhere in the username count:  $($ADUser_test.Count)"
Write-Output "Account in the ServiceAccounts OU count:  $($ADUser_OU.Count)"
Write-Output "Account containing 'built-in' anywhere in the description count:  $($ADUser_builtin.Count)"
Write-Output "Account containing 'email verification' anywhere in the description count:  $($ADUser_email.Count)"

Write-Output "Total AD users after exclusion:  $($ADQuery.Count - $ADUser_excluded.count)"

Open in new window

0
 
JaihuntCommented:
Hi

Please use the power shell to find how many users in AD

http://powergui.org/thread.jspa?threadID=2701&tstart=0

Thanks
  Jai
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Jaihunt,

Not very helpful.  I already know how to count users in PS, that is the easy part.  My question is how do I exclude the users that meet the criteria I listed in the question from the count?  I don't know enough about PS to filter my results.

eb
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
coraxalConnect With a Mentor Commented:
Here's an attempt....script is not tested. Make sure to replace the search domain.

$ADUser_admin = @()
$ADUser_template = @()
$ADUser_test = @()
$ADUser_builtin = @()
$ADUser_email = @()
$ADUser_OU = @()
$ADUser_expired = @()
$ADUser_disabled = @()
$ADUser_notloggedonfor = @()
$ADUser_excluded = @()

$refDate = (Get-Date).AddDays(-90)

$ADQuery = Get-QADUser -SearchRoot "dc=testdomain,dc=com" `
			-DontUseDefaultIncludedProperties `
			-IncludedProperties UserPrincipalName,SamAccountName,Description,DN,AccountIsExpired,AccountIsDisabled `
			-ResultSize 0 
			
$ADQuery | % {
			
	if($_.SamAccountName -like "*admin*"){ $ADUser_admin += @($_.DN) } 
	
	if($_.SamAccountName -like "*template*"){ $ADUser_template += @($_.DN) } 
	
	if($_.SamAccountName -like "*test*"){ $ADUser_test += @($_.DN) } 
	
	if($_.SamAccountName -like "*built-in*"){ $ADUser_builtin += @($_.DN) } 
					
	if($_.Description -like "*email verification*"){ $ADUser_email += @($_.DN) } 			
	
	if($_.DN -like "*OU=ServiceAccounts*"){ $ADUser_OU += @($_.DN) }
	
	if($_.AccountIsExpired){ $ADUser_expired += @($_.DN) }
	
	if($_.AccountIsDisabled){ $ADUser_disabled += @($_.DN) }
	
	if($_.LastLogonTimestamp -le $refDate){ $ADUser_notloggedonfor += @($_.DN) }
				
				
			
}
			
$ADUser_excluded = $ADUser_admin.Count + $ADUser_template.Count + $ADUser_test.Count + `
					$ADUser_builtin.Count + $ADUser_email.Count + $ADUser_OU.Count + `
					$ADUser_expired.Count + $ADUser_disabled.Count + $ADUser_notloggedonfor.Count


<#
It would be nice if the script gave;
a total count of users in AD
a count for each exclusion criteria
a count of total users in AD - the excluded users
#>


Write-Output "Total AD users:  $($ADQuery.Count)"

Write-Output "Account disabled count:  $($ADUser_disabled.Count)"
Write-Output "Account expired count:  $($ADUser_expired.Count)"
Write-Output "Account not logged in over 90 days count:  $($ADUser_notloggedonfor.Count)"
Write-Output "Account containing 'admin' anywhere in the username count:  $($ADUser_admin.Count)"
Write-Output "Account containing 'template' anywhere in the username count:  $($ADUser_template.Count)"
Write-Output "Account containing 'test' anywhere in the username count:  $($ADUser_test.Count)"
Write-Output "Account in the ServiceAccounts OU count:  $($ADUser_OU.Count)"
Write-Output "Account containing 'built-in' anywhere in the description count:  $($ADUser_builtin.Count)"
Write-Output "Account containing 'email verification' anywhere in the description count:  $($ADUser_email.Count)"

Write-Output "Total AD users after exclusion:  $($ADQuery.Count) - $($ADUser_excluded.Count)"

Open in new window

0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Coraxal

I received a couple of errors.

PS C:\Users\admin_ebjers> $ADUser_admin = @()
PS C:\Users\admin_ebjers> $ADUser_template = @()
PS C:\Users\admin_ebjers> $ADUser_test = @()
PS C:\Users\admin_ebjers> $ADUser_builtin = @()
PS C:\Users\admin_ebjers> $ADUser_email = @()
PS C:\Users\admin_ebjers> $ADUser_OU = @()
PS C:\Users\admin_ebjers> $ADUser_expired = @()
PS C:\Users\admin_ebjers> $ADUser_disabled = @()
PS C:\Users\admin_ebjers> $ADUser_notloggedonfor = @()
PS C:\Users\admin_ebjers> $ADUser_excluded = @()
PS C:\Users\admin_ebjers>
PS C:\Users\admin_ebjers> $refDate = (Get-Date).AddDays(-90)
PS C:\Users\admin_ebjers>
PS C:\Users\admin_ebjers> $ADQuery = Get-QADUser -SearchRoot "dc=daiglobal,dc=net" `
>>             -DontUseDefaultIncludedProperties `
>>             -IncludedProperties UserPrincipalName,SamAccountName,Description,DN,AccountIsExpired,AccountIsDisabled `
>>             -ResultSize 0
>>
Get-QADUser : A parameter cannot be found that matches parameter name 'ResultSize'.
At line:4 char:24
+             -ResultSize <<<<  0
    + CategoryInfo          : InvalidArgument: (:) [Get-QADUser], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.GetUserC
   mdlet

PS C:\Users\admin_ebjers> $ADQuery | % {
>>
>>     if($_.SamAccountName -like "*admin*"){ $ADUser_admin += @($_.DN) }
>>
>>     if($_.SamAccountName -like "*template*"){ $ADUser_template += @($_.DN) }
>>
>>     if($_.SamAccountName -like "*test*"){ $ADUser_test += @($_.DN) }
>>
>>     if($_.SamAccountName -like "*built-in*"){ $ADUser_builtin += @($_.DN) }
>>
>>     if($_.Description -like "*email verification*"){ $ADUser_email += @($_.DN) } .\Documents
>>
>>     if($_.DN -like "*OU=ServiceAccounts*"){ $ADUser_OU += @($_.DN) }
>>
>>     if($_.AccountIsExpired){ $ADUser_expired += @($_.DN) }
>>
>>     if($_.AccountIsDisabled){ $ADUser_disabled += @($_.DN) }
>>
>>     if($_.LastLogonTimestamp -le $refDate){ $ADUser_notloggedonfor += @($_.DN) }
>>
>>
>>
>> }
>>
The term '.\Documents' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the
 spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:6 char:93
+     if($_.Description -like "*email verification*"){ $ADUser_email += @($_.DN) } .\Documents <<<<
    + CategoryInfo          : ObjectNotFound: (.\Documents:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Open in new window


I am going to do a little debugging, see if I can figure out what the cause of the errors is but my PS knowledge is limited.

eb
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Footech, thank you for the modified script.  It took a long time to run and there were a lot of errors around the part checking for "email verification" in the description but it seems to have produced reliable results.  

The "email verification" accounts should mostly overlap with the disabled accounts so this error is not a big deal and the count is close enough for my needs.

I will split the points between you and coraxal
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Thanks again for the help
0
 
footechCommented:
You're welcome.  I'm curious about the errors you mentioned - doesn't seem like there should be any.
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Sorry I have already disconnected from the server, but I can try running the script again later and post the errors.

Based on the number of errors displayed I am fairly sure they resulted from the check for email verification as it looked like there was an error for each user account (basically 4500 errors or so)

eb
0
 
footechCommented:
Only 4500? Well, that's nothing to be concerned about.  Call me when it reaches 5000.  :)

No need to put yourself out.  Just for my own curiousity I'll see if I can duplicate.
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
will only take a few minutes to start the script and get to the error, I just need to get back to a server which I will at somepoint tomorrow so no big deal to post the error.
0
 
footechCommented:
I was just taking another look at the script and it looks like I made a big mistake in the way I was counting and also forgot to do "+=" instead of "=" to make sure $ADUser_excluded was an array.  Here's a corrected script.
$ADUser_admin = @()
$ADUser_template = @()
$ADUser_test = @()
$ADUser_builtin = @()
$ADUser_email = @()
$ADUser_OU = @()
$ADUser_expired = @()
$ADUser_disabled = @()
$ADUser_notloggedonfor = @()
$ADUser_remaining = @()

$refDate = (Get-Date).AddDays(-90)

$ADQuery = Get-ADUser -filter * `
	-properties UserPrincipalName,SamAccountName,Description,DistinguishedName,AccountExpirationDate,Enabled,LastLogonDate
			
$ADQuery | % {
			
	if($_.SamAccountName -like "*admin*"){ $ADUser_admin += @($_.DistinguishedName) } 
	
	if($_.SamAccountName -like "*template*"){ $ADUser_template += @($_.DistinguishedName) } 
	
	if($_.SamAccountName -like "*test*"){ $ADUser_test += @($_.DistinguishedName) } 
	
	if($_.Description -like "*built-in*"){ $ADUser_builtin += @($_.DistinguishedName) } 
					
	if($_.Description -like "*email verification*"){ $ADUser_email += @($_.DistinguishedName) } 			
	
	if($_.DistinguishedName -like "*OU=ServiceAccounts*"){ $ADUser_OU += @($_.DistinguishedName) }
	
	if(($_.AccountExpirationDate) -and ($_.AccountExpirationDate -lt $(Get-Date))){ $ADUser_expired += @($_.DistinguishedName) }
	
	if($_.Enabled -eq $False){ $ADUser_disabled += @($_.DistinguishedName) }
	
	if($_.LastLogonDate -le $refDate){ $ADUser_notloggedonfor += @($_.DistinguishedName) }
				
				
			
}

$ADUser_remaining += $ADQuery | Select -expandProperty DistinguishedName | Compare-Object $ADUser_admin -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_template -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_test -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_builtin -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_email -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_OU -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_expired -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_disabled -passthru | where { ($_.SideIndicator -eq "=>")} |`
	Compare-Object $ADUser_notloggedonfor -passthru | where { ($_.SideIndicator -eq "=>")} 

<#
It would be nice if the script gave;
a total count of users in AD
a count for each exclusion criteria
a count of total users in AD - the excluded users
#>


Write-Output "Total AD users:  $($ADQuery.Count)"

Write-Output "Account disabled count:  $($ADUser_disabled.Count)"
Write-Output "Account expired count:  $($ADUser_expired.Count)"
Write-Output "Account not logged in over 90 days count:  $($ADUser_notloggedonfor.Count)"
Write-Output "Account containing 'admin' anywhere in the username count:  $($ADUser_admin.Count)"
Write-Output "Account containing 'template' anywhere in the username count:  $($ADUser_template.Count)"
Write-Output "Account containing 'test' anywhere in the username count:  $($ADUser_test.Count)"
Write-Output "Account in the ServiceAccounts OU count:  $($ADUser_OU.Count)"
Write-Output "Account containing 'built-in' anywhere in the description count:  $($ADUser_builtin.Count)"
Write-Output "Account containing 'email verification' anywhere in the description count:  $($ADUser_email.Count)"

Write-Output "Total AD users after exclusion:  $($ADUser_remaining.Count)"

Open in new window

You'll notice I changed the "excluded" to "remaining".  If we wanted to actually build a list of the excluded users, we could use something like this:
[array]$ADUser_excluded = Compare-Object $ADUser_admin $ADUser_template -passthru -IncludeEqual |`
	Compare-Object $ADUser_test -passthru -IncludeEqual |`
	Compare-Object $ADUser_builtin -passthru -IncludeEqual |`
	Compare-Object $ADUser_email -passthru -IncludeEqual |`
	Compare-Object $ADUser_OU -passthru -IncludeEqual |`
	Compare-Object $ADUser_expired -passthru -IncludeEqual |`
	Compare-Object $ADUser_disabled -passthru -IncludeEqual |`
	Compare-Object $ADUser_notloggedonfor -passthru -IncludeEqual

Open in new window

Haven't been able to duplicate any error with "emai verification" though.
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
OK just ran your new version and still got the error which is

The term '.\Documents' is not recognized as the name of a cmdlet, function, scr
ipt file, or operable program. Check the spelling of the name, or if a path was
 included, verify that the path is correct and try again.
At line:6 char:108
+     if($_.Description -like "*email verification*"){ $ADUser_email += @($_.Di
stinguishedName) } .\Documents <<<<
    + CategoryInfo          : ObjectNotFound: (.\Documents:String) [], Command
   NotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Open in new window


It comes up fast so I did not catch what command caused the error but the next command run  was

PS C:\Users\admin_ebjers> $ADUser_remaining += $ADQuery | Select -expandProperty
 DistinguishedName | Compare-Object $ADUser_admin -passthru | where { ($_.SideIn
dicator -eq "=>")} |`

Open in new window


The end count with the new version is about 30 less than the count from the old version which is well with in my margin of error as I am rounding up to the next 500 for license purchases that come in blocks of 500.

Thanks again for the help.
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Actually exactly 55 less, but it also looks like a few accounts expired over night
0
 
footechCommented:
OK.  I think I know what happened.  You must have just copied the all the code and run it at the prompt, am I correct?

I was able to duplicate the issue by just pasting the code into the prompt.  What seems to be the cause is at the end of a few of the lines there is a <space><tab>.  In a script file this isn't a problem, but when the behavior at the prompt is different.  When in the middle of a command (in this case the ForEach scriptblock), after pressing <space> each press of <tab> auto-completes to the name of the next folder or file in the current working directory.

So, either save the script to a .ps1 file and run it, or be sure that there is no whitespace at the end of any line - is the best to way to avoid issues like this.  Kind of interesting... I'd never run into this before because I almost exclusively run script files.
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
I was lazy and just cut and past, next time I will run as .ps.  Anyway it seems to have given good results and as I said earlier any account that has "Email verification" in the description should also be disabled so that count is not really needed as much.

eb
0
 
Erik BjersPrincipal Systems AdministratorAuthor Commented:
Just so you know I ran this again as a .ps1 file and had no issues.  Thanks again for the help.

eb
0
All Courses

From novice to tech pro — start learning today.