Getting contents of a csv and for each line, search a like or contains that

This topic contains 11 replies, has 3 voices, and was last updated by  Lakatta 3 years, 5 months ago.

  • Author
    Posts
  • #16408

    Lakatta
    Participant

    Any guidance or help is appreciated. Where I am, we are trying to set up a quick script to get the contents of a csv (names – but not the dn, only full names are in the file), then search ad for a user that contains the name from the csv, maybe even like, then disable and move the account to an ou, pipe the results out to an excel sheet and open.

    I've got the export, get the contents, and can even list the contents, but what I can't do is figure out how to search for each line like a variable with a wildcard.

    I tried (obviously wrong) – Get-Content c:\scripts\test.csv | Get-ADUser -Filter Name -contain * | Move-ADObject -TargetPath "OU=Disabled User Accounts,DC=ad,DC=library,DC=ucla,DC=edu" -PassThru | Disable-ADAccount

    Tried then to get just the contents and assign each line as a variable then search that variable with a wildcard – wrong again

    $fubar = Get-Content C:\scripts\test.csv | out-string
    ForEach ($fubars in $fubar) {
    $fubars -add '*' | Out-File c:\scripts\new.txt -Append
    }

    Invoke-item c:\scripts\new.txt

    Then I thought hmm – steal from another script of mine that gets group members and removes the disabled accounts from the group
    $fubar = Get-Content C:\scripts\test.csv
    $snooplion = Get-aduser $fubar -Recursive | where-object

    and nope not gonna work.

    My logic is

    $get = get the contents of the csv

    Get-aduser name (first and last name field property) filter on it so ( it either contains or is like each name in each line of the csv)

    from there

    pipe it to a excel file

    and disable the account, and move it to another ou.

    invoke item to open the excel file showing the moved accounts names

    I can figure out the second half, but I can't figure out the meat and bones of it. I use other scripts pieces and parts for examples, but this one I can't seem to search it out correctly. If anyone has any imput, I appreciate it.

    After I have the meat and bones, I can figure out how to put in the functions and make it more elaborate with other inputs and checks and balances.

    Dave
    UCLA Library

  • #16418

    Don Jones
    Keymaster

    If it's a CSV file, is there a reason you're not using Import-CSV instead of Get-Content? It seems like you're trying to parse the file yourself, which is what Import-CSV already does. I don't know what your CSV file structure looks like, so I can't be more specific than that suggestion.

    Also, -contains isn't a string wildcard comparison operator. You might read up on about_comparison_operators to see what -contains actually does.

  • #16482

    Lakatta
    Participant

    Wow. Thanks for the reply. I've been out sick.

    The csv is simple. We receive an excel spreadsheet from HR, I remove all the junk, and keep the name of the employees in 1 column. The name they give us, is the same as display name in the ad.

    I will look that up, and get your book too.

  • #16484

    Don Jones
    Keymaster

    Import-csv file.csv | select columnIcareAbout

    That should grab just the column you want without you having to do the cleanup manually. That becomes a property of an object.

  • #16486

    Lakatta
    Participant

    Thanks again. That works great.

    Here is where I am at.

    I can do a get-aduser -filter {surname -like "*bruin*} | FT Name

    I get the results desired searching for a last name like bruin.

    But when I add the rest of what I'm trying to do. I don't get an output, and I can't see what is being used to get the information. I know it's not the most efficient way to search and could use a searchbase to make it faster, but it's really weird. I'm missing something really basic and simple.

    Here is the code:

    $UserObjects = Import-csv C:\scripts\staff.csv | select name

    ForEach ($UserObject in $UserObjects)

    { Get-ADuser -filter {surname -like "*$UserObject"} | FT Name}

  • #16487

    Rob Simmers
    Participant

    There is no asterisk after your $UserObject. While you are still testing, you can output to the console so you validate the search is valid, something like this:

    ForEach ($UserObject in $UserObjects) { 
        $filter = "*{0}*" -f $UserObject
        "Searching for {0}" -f $filter
        Get-ADuser -filter {surname -like $filter} | FT Name
    }
  • #16489

    Don Jones
    Keymaster

    Also bear in mind that once you format an object, it isn't usable for anything else. So if you're piping that elsewhere, it won't work out.

  • #16490

    Lakatta
    Participant

    You guys are amazing. This all worked. For some reason I couldn't get the last one to work correctly, and now I did.

    I'll post the completed script in a bit.

  • #16492

    Lakatta
    Participant

    I made a slight change and I don't know if that fixed it or what. We get a file from our HR department and we'll manipulate it a bit (as soon as we settle on a format – add it to the powershell script to format it automatically). It has the users, and department. Since we don't want to go through our OU's and find them one by one and disable (another story and we are in the midst organizing it correctly because I can't handle it anymore) the accounts and move them, I decided to try this out with your help of course and it worked.

    Now the small IT staff we have can run this in the ISE and it will take care of it for us.

    Of course, using surname as the property to pull is not ideal, and will be changed for sure. I have a user with the letter I as his last name – this doesn't work to well.

    Next is to automate the entire adding a domain account correctly. Our people will only need to type in the information since we hire one by one.

    A big thank you and here it is:

    cls

    Write-Host "Disable and Move Seperated Staff"
    $StaffFile = Read-Host "Enter the name of the file csv file on your desktop"

    $UserObjects = Import-csv -path "$env:USERPROFILE\desktop\$StaffFile.csv" | select name

    # Future and to scriptblock below $name
    #$unit = $UserObject.unit

    ForEach ($UserObject in $UserObjects) {

    $name = $UserObject.name

    $filter = "*{0}*" -f $name

    "Searching for {0}" -f $filter
    Get-ADuser -filter {Surname -like $filter}

    # | Move-ADObject -TargetPath "OU=Disabled User Accounts,DC=,DC=,DC=,DC=" -PassThru | Disable-ADAccount
    }

  • #16493

    Lakatta
    Participant

    forgot to mention, the commented last line is what the whole thing was for. When in production I'll remove the comment and move up that line, but at the moment, it's too dangerous for me. It will disable everyone because of the one user.

  • #16510

    Rob Simmers
    Participant

    Two quick tips:

    [ol]

    [li]Personal preference of course, but I like to actually initiate a Open File Dialog to have users browse to a file versus manually adding a path. Check out this post and just update the PS1 to CSV and try it out: http://powershell.com/cs/blogs/tips/archive/2013/02/14/using-open-file-dialogs.aspx%5B/li%5D

    [li]You can disable your last line, but many Powershell functions that 'do or change something' have a -WhatIf switch. This tells you what WOULD happen if the command were to run, which ensures the commands are going to do what you expect. So, in ISE, put the cursor over Move-ADObject & Disable-ADAccount and press F1. You will see all of the command-line options. Verify they have -WhatIf functionality and then try: Move-ADObject -TargetPath "OU=Disabled User Accounts,DC=,DC=,DC=,DC=" [b]-Whatif[/b] -PassThru | Disable-ADAccount [b]-WhatIf[/b][/li]
    [/ol]

    Edit: Another tip I forgot to mention. It sound like you are Disabling termed employees, so it's going to be easier to ensure during onboarding that a employeeID is assigned to a user account from your HR DB and then do your search on that versus looking for someone with givenName Rob and surName Simmers...

  • #16518

    Lakatta
    Participant

    Thank you, we are having on-boarding discussions (another story) currently, and I will be using all of this.

    I had no expectations from the forum, and wow. Thanks for all the help.

You must be logged in to reply to this topic.