Find AD accounts not in a input file

PowerShell Help
1

Greetings!
Relatively new to powershell attempting to find AD service accounts that are not in the file test.txt. As you can see I’m struggling with how to get this to work

Here is the code I’m working with:

$server=get-content C:\temp\test.txt
Get-ADUser -SearchBase “OU=Serviceaccounts,DC=nlong,DC=com” -Filter {Name -like “svcxxsql*”} |Where-Object {$_.Name -notin $server} | sort | Select-Object Name | Out-File -FilePath C:\temp\foo.txt

Here are the contents of test.txt:
svcXXSQL001Agent
svcXXSQL001DBEng
svcXXSQL001Int
svcXXSQL002Agent
svcXXSQL002Eng
svcXXSQL002Int

When running the code with test.txt here is output in foo.txt:

Name

svcXXSQL002Agent
svcXXSQL002Eng

As you can see the svcXXSQL001Agent, svcXXSQL001DBEng and svcXXSQL001Int are not there, which is a good. However, svcXXSQL002Agent and svcXXSQL002Eng are still in foo.txt, they should not be there.

Now if I change the contents of test.txt to the following:
svcXXSQL001Agent
svcXXSQL001DBEng
svcXXSQL001Int

And re-run Get-ADUser -SearchBase “OU=ServiceAccounts,DC=nlong,DC=com” -Filter {Name -like “svcxxsql*”} |Where-Object {$_.Name -notin $server} | sort | Select-Object Name | Out-File -FilePath C:\temp\foo.txt

The output in foo.txt is correct the XXSQL001 entries have been removed, the first entries in the file are xxsql002Agent, Eng and Int.

Name

svcXXSQL002Agent
svcXXSQL002Eng
svcXXSQL002Int

Something is amiss, having more than three entries in test.txt is causing shall we say opportunities which I’m not smart enough to understand.

Thanks for your input.

Norm

2

HI Norm,
I have checked and double checked your code but cant seem to replicate the issue you are having i have even setup the same account in a test AD lab and it still not able to replicate it. even with 1 or 2 entries in the test.txt file i am able to get the required output.

PS C:\Users\v-spieter> Get-ADUser -SearchBase "OU=test,DC=lab,DC=local" -Filter {Name -like "svcxxsql**"} | 
Where-Object {$_.Name -notin $server} | Sort-Object | select-object Name

can you check to ensure that your AD search base is correct. and that your names don’t contain any spaces. Maybe try to run the same query with the SAMAccountName to test.

also ensure that the $server Variable is exactly the same as the text inside the test.txt file, Ensure no other characters are present

Regards
Shihan

3

Hello Shihan;
First of all would like to thank your taking the time to actually test my code! Sorry to say it still isn’t working for me. Think your correct it has something to do with my input file. I changed the creation of the input file to go after the SamAccountName.

Here is how my input file was created:
Get-ADComputer -filter {(SamAccountName -like “sql”) -and (SamAccountName -notlike “SQL-*”)} -properties * | sort | Select-Object SamAccountName | Export-Csv -Path c:\temp\sqlservers.csv

We don’t want the column headings, the following removes the column heading and re-writes the file.

$csv = Get-Content C:\temp\sqlservers.csv
$csv = $csv[2..($csv.count - 1)]
$csv > C:\temp\sqlservers.csv

Here is the first few lines of sqlservers.csv
“FWSQL001$”
“FWSQL002$”
“FWSQL003$”
“FWSQL005$”
“FWSQL006$”
“FWSQL007$”

Here is what I ran, changing as per your suggestion to SamAccountName
$server=get-content C:\temp\sqlservers.csv
Get-ADUser -SearchBase “OU=ServiceAccts,DC=nlong,DC=com” -Filter {SAMAccountName -like “svcfwsql*”} | Where-Object {$_.SAMAccountName -notin $server} | sort | Select-Object SAMAccountName | Out-File -FilePath C:\temp\foo.txt

Here is a snippet of foo.txt

SAMAccountName

svcFWSQL001Agent
svcFWSQL001DBEng
svcFWSQL001Int
svcFWSQL002Agent
svcFWSQL002Eng
svcFWSQL002Int
svcFWSQL003Agent
svcFWSQL003DBEng
svcFWSQL003Int
svcFWSQL003Link
svcFWSQL003Proxy
svcFWSQL005Agent
svcFWSQL005DBEng
svcFWSQL005Int
svcFWSQL006Agent
svcFWSQL006DBEng
svcFWSQL006Int
svcFWSQL007Agent
svcFWSQL007DBEng
svcFWSQL007Int

Oh yes I ran code once without the -SearchBase option and still did work (no change in foo.txt). I’m 99% certain the SearchBase OU is correct, wanted to let you know was taking your suggestions into account. Again the goal is to find service accounts that do not have an associated computer object.

Please let me know what you think if there is something I’m missing. Been working on this for a couple of weeks now greatly appreciate your input.

Norm

4

HI Norm,
thanks for the comments
referencing your code

you don’t need to remove the column headings in the CSV. You can simply reference the property as below
$servers = import-csv C:\temp\Servers.csv
if the csv column headings is Name then you can reference it as $servers.Name

can you try the below
get your computer names into a csv file using export-csv -notypeinformation

Get-ADComputer -filter {(Name -like "*sql*") -and (Name -notlike "SQL-*")} -properties * | sort | Select-Object Name | Export-Csv -Path c:\temp\sqlservers.csv -noTypeInformation

$servers = Import-Csv  C:\temp\servers.csv

Get-ADUser -SearchBase "OU=ServiceAccts,DC=nlong,DC=com" -Filter {Name -like "svcfwsql*"}  | Where-Object {$_.Name -notin $servers.Name} | Select-Object Name

see how you go with this. it works when i try it. Also which version of powershell are you running
$psversiontable

Thanks
Shihan

5

Hello Shihan;
First of all thank you for your patience, I feel as though I’m failing to communicate my goal. I ran your code but the output reflects what is out in AD, for example there are for example computer objects fwsql001 and fwsql002 with the associated ServiceAccts:
svcFWSQL001Agent
svcFWSQL001DBEng
svcFWSQL001Int
svcFWSQL002Agent
svcFWSQL002Eng
svcFWSQL002Int

Since these service accounts and computer object fwsql001 and fwsql002 exist in AD my expectation would be the service accounts associated with fwsql001 and fwsql002 should not be in the output…

To continue with personal expectations for example , in the serviceaccts ou there are entries for fwsqluat450 as follows svcfwsqluat450int, svcfwsqluat450dbeng and svcfwsqluat450agent. however there is no fwsqluat450 computer account, and sure enough there is not fwsqluat450 in servers.csv as it should be.

In this case my expectation would be to see svcfwsqluat450int, svcfwsqluat450eng and svcfwsqluat450agent in foo.txt and to extend this a bit further would not expect to see svcfwsql001Agent, svcFWSQL001DBEng and svcFWSQL001Int in foo.txt since fwsql001 is a existing server with a existing computer object.

Believe there is serious or fatal flaw in my Get-ADUser line. Would greatly appreciate your help, think it has something to do with my filter and where-object logic which is not taking into consideration the int, eng and agent extensions on the service account names.

Oh yes, here is my current version of powershell
Name Value

PSVersion 5.1.14409.1012
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
BuildVersion 10.0.14409.1012
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Thank you for taking time out of your day to assist!

Norm

6

Hell Shihan;
Here is what I ran, thought this might be helpful to you.

Get-ADComputer -filter {(Name -like “sql”) -and (Name -notlike “SQL-")} -properties * | sort | Select-Object Name | Export-Csv -Path c:\temp\sqlservers.csv -noTypeInformation
$servers = Import-Csv C:\temp\servers.csv
Get-ADUser -SearchBase “OU=ServiceAccts,DC=nlong,DC=com” -Filter {Name -like "svcfwsql”} | Where-Object {$_.Name -notin $servers.Name} | Select-Object Name | Select-Object | Out-File -FilePath C:\temp\foo.txt

Norm

7

Hi Norm,
Your welcome. and glad to help out

It makes more sense now what you want to achieve I would put this in a .ps1 script so it would be more clear.
to begin with I would update the csv file manually with some sample server names to check.

the reason your code was not working correctly is the computer names are different form the service accounts. because part of your service account have the computer name within them, we can cut the computer Name out using substring method. provided that you have the computer names inside the .csv files and that your naming conventions don’t change anytime soon.

the below code should do the trick

$servers = Import-Csv  C:\temp\servers.csv


$ADUsers = Get-ADUser -SearchBase "OU=test,OU=Users,OU=lab Objects,DC=local" -Filter {Name -like "svcfwsql*"} 


foreach($User in $ADusers){

    if(($User.Name).Substring(3,8) -notin $Servers.Name){
        $User.Name 
    
        }#IF


}#Foreach

Regards
Shihan

8

Hi Shihan;
Well good news your script worked, after some modifications not because of your labor but due to our naming conventions. Here is what works:
$servers = Import-Csv C:\temp\sqlservers.csv

#$ADUsers = Get-ADUser -SearchBase “OU=ServiceAccts,DC=nlong,DC=com” -Filter {Name -like “svcfwsql*”} | Select-Object Name
$ADUsers = Get-ADUser -SearchBase “OU=ServiceAccts,DC=nlong,DC=com” -Filter {(Name -like “svcfwsql*”) -and (Name -notlike “svcfwsqldev*”) -and (Name -notlike “svcfwsqluat*”) -and (Name -notlike “svcfwsqlstg*”)} |Select-Object Name

foreach($User in $ADusers){

if(($User.Name).Substring(3,8) -notin $Servers.Name){
    $User.Name  

    }

}

The $servers file only contains computer ou’s with name fwsql***, will have to create input files containing only fwsqldev computer ou’s fwsqluat ou’s etc. Same for AD-User. Don’t see any other option. Oh yes will need to change the string param’s as well for uat, dev, stg service account from 3,8 to 3,11 right?

Also been playing with writing the output to a file but having a hard time figuring out where to put the export-csv command.

Can’t thank you enough for your help!

Norm

9

Hello Shihan;
Figured out the output file issue:
$servers = Import-Csv C:\temp\sqlservers.csv

$ADUsers = Get-ADUser -SearchBase “OU=ServiceAccts,DC=wvus,DC=org” -Filter {(Name -like “svcfwsql*”) -and (Name -notlike “svcfwsqldev*”) -and (Name -notlike “svcfwsqluat*”) -and (Name -notlike “svcfwsqlstg*”)} |Select-Object Name
Clear-Content C:\temp\svcfwsql.out

foreach($User in $ADusers){

if(($User.Name).Substring(3,8) -notin $Servers.Name){
    $User.Name | Out-File -FilePath C:\temp\svcfwsql.out -Append
     

    }#IF

} #ForEach

Norm

10

Hello Shihan;
Don’t want you to burn any more of your time, created 4 scripts to handle the different naming conventions of our sql environment, fwsql, fwsqluat, fwsqldev and fwsqlstg. Each of the scripts are producing the correct data one of our sql dba’s have confirmed the output.

Again thank you for all your time and effort!

Norm

11

HI Norm,
Thanks for the kind words. Also thanks for sharing your code, Might help someone else down the track

Cheers
Shihan