Find AD accounts not in a input file

This topic contains 10 replies, has 2 voices, and was last updated by  Shihan Pietersz 2 months, 2 weeks ago.

  • Author
    Posts
  • #94821

    Norm Long
    Participant

    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

  • #94867

    Shihan Pietersz
    Participant

    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

  • #94917

    Norm Long
    Participant

    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

  • #94921

    Shihan Pietersz
    Participant

    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

  • #94951

    Norm Long
    Participant

    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

  • #94954

    Norm Long
    Participant

    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

  • #94971

    Shihan Pietersz
    Participant

    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

  • #95016

    Norm Long
    Participant

    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

  • #95020

    Norm Long
    Participant

    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

  • #95026

    Norm Long
    Participant

    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

  • #95104

    Shihan Pietersz
    Participant

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

    Cheers
    Shihan

You must be logged in to reply to this topic.