Solved

Counting users in active directory excluding on various criteria

Posted on 2013-01-17
17
1,330 Views
Last Modified: 2013-01-28
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.
0
Comment
Question by:Erik Bjers
17 Comments
 
LVL 13

Expert Comment

by:Jaihunt
ID: 38786720
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38786835
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
 
LVL 5

Assisted Solution

by:coraxal
coraxal earned 250 total points
ID: 38787147
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38787354
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
 
LVL 39

Accepted Solution

by:
footech earned 250 total points
ID: 38789082
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38789453
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
 
LVL 23

Author Closing Comment

by:Erik Bjers
ID: 38789459
Thanks again for the help
0
 
LVL 39

Expert Comment

by:footech
ID: 38789628
You're welcome.  I'm curious about the errors you mentioned - doesn't seem like there should be any.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 23

Author Comment

by:Erik Bjers
ID: 38789638
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
 
LVL 39

Expert Comment

by:footech
ID: 38789865
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38789880
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
 
LVL 39

Expert Comment

by:footech
ID: 38790099
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38793098
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38793099
Actually exactly 55 less, but it also looks like a few accounts expired over night
0
 
LVL 39

Expert Comment

by:footech
ID: 38794071
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38801060
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
 
LVL 23

Author Comment

by:Erik Bjers
ID: 38827328
Just so you know I ran this again as a .ps1 file and had no issues.  Thanks again for the help.

eb
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
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 …
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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 from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

757 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

19 Experts available now in Live!

Get 1:1 Help Now