User Provisioning/Deprovisioning from single CSV

This topic contains 13 replies, has 4 voices, and was last updated by  Nathan Stewart 1 month, 1 week ago.

  • Author
    Posts
  • #77407

    Nathan Stewart
    Participant

    I've got a bit of a weird one...
    We have a group of field employees who are not part of the corporate office, and are not issued AD accounts or company email addresses. Because of the volume of turnover at these field locations, our HR department does notify us of each individual user activation/deactivation. Until now it didn't matter because they did not have AD accounts. However...

    As our business has grown, we are now using Concur, UltiPro, and several other systems that these field employees need access to. The way that HR wants to handle user on-boarding/off-boarding (and was agreed by leadership) is that they dump a CSV from their system with the necessary employee info to provision accounts, and have us pull this data to provision/de-provision accounts based on the "isactive" column. Straightforward, right? Here's the twist... This CSV has multiple entries for employees that have changed roles/job titles within the company. One line shows that the user is inactive (old role), and the other shows active (current role). So I need help in figuring a way to remove accounts of inactives without removing the accounts of those who have only changed roles.

    My idea: Set up an array of active users, and an array of inactive users and do some sort of comparison that only deactivates if the "isactive" column is not set to true in the other array. Something like:

     
     $users = import-csv -path \\blah\thing.csv
     $inactiveusers = @()
     $activeusers = @()
    
     foreach ($user in $users) 
                {
                 if (($user.location -eq "Field") -and ($user.isactive -eq "true"))
                           {
                            $obj1 = [pscustomobject]@{"firstname"=$user.first; "lastname"=$user.last; "UPNPrefix"=$user.username; "location"=$user.location; "IsActive"=$user.isactive}
                            $inactiveusers += $obj1
                            }
                }
    
     foreach ($user in $users) 
                {
                 if (($user.location -eq "Field") -and ($user.isactive -eq "true"))
                           {
                            $obj1 = [pscustomobject]@{"firstname"=$user.first; "lastname"=$user.last; "UPNPrefix"=$user.username; "location"=$user.location; "IsActive"=$user.isactive}
                            $activeusers += $obj2
                            }
                }
    

    From there, I need to come up with the logic to disable users who exist in $inactiveusers, but not in $activeusers...
    So I guess 2 questions:

    1) is this a reasonable way to do this, or is there a better, more simplistic way?
    2) If this is reasonable, would anyone be able to help me with the comparative logic?

    Sorry for such a long post, and thanks for any help!

  • #77410

    Aaron Hardy
    Participant

    If I understand you correctly, this may work for you.

    You can simplify your code by putting it into an advanced function and use parameters to pull out active or inactive users.

    Function Get-FieldEmployees {
        [CmdletBinding()]
        param (
            [string]$Path = '\\blah\thing.csv',
            [bool]$IsActive
        )
    
        $Users = Import-CSV -Path $Path
    
        foreach ($user in $Users) {
            if (($user.location -eq "Field") -and ($user.isactive -eq $IsActive)) {
    
                $props = @{"firstname"=$user.first;
                           "lastname"=$user.last;
                           "UPNPrefix"=$user.username;
                           "location"=$user.location;
                           "IsActive"=$user.isactive }
                $obj = New-Object psobject -Property $props
                Write-Output $obj
            }
        }
    }

    You could run Get-FieldEmployee to return employees whose Active status is false:

    Get-FieldEmployee

    Or you could get the active employees whose status is active using the -IsActive switch:

    Get-FieldEmployee -IsActive $true

    Edit: Missed closing bracket of foreach statement.

    Edit #2: Forgot to add $true for -IsActive in original post. You could also consider using a switch parameter for this.

    • #77413

      Nathan Stewart
      Participant

      Awesome! That definitely simplifies getting active vs. inactive users. Thanks!

      Any thoughts on the best way to ensure that I don't disable users who have both a line showing them inactive and another line showing them as active? Some of these employees have multiple prior positions that have rows marking them inactive, but they are marked as active for their current role. That's the part that really has me stumped...

  • #77415

    Kevyn
    Participant

    Wiping this post out as I realized that a user could be just IsActive = True in the file.

  • #77424

    Kevyn
    Participant

    Picky-backing on Aaron's function, I found the following to work. I put Write-Output cmdlets in there just for testing.

    $ActiveUsers = Get-FieldEmployees -IsActive $true
    $ActiveUsersUPNPrefix = $ActiveUsers | Select-Object -ExpandProperty UPNPrefix
    $InactiveUsers = Get-FieldEmployees -IsActive $false
    
    #Process active users (i.e. IsActive = True)
    ForEach($ActiveUser in $ActiveUsers)
    {
      #Function/code to process active users
      Write-Output "User $($ActiveUser.UPNPrefix) has IsActive = True.  Processing them."
    }
    
    #Process users who have IsAcive = False in the csv
    ForEach($InactiveUser in $InactiveUsers)
    {
      If($InactiveUser.UPNPrefix -in $ActiveUsersUPNPrefix)
      {
        Write-Output "User $($InactiveUser.UPNPrefix) has IsActive = True for another position.  Don't remove them."
        #Nothing to do here since the users who were IsActive = True somewhere in the csv have already been processed above.
      }
      Else #Users are only listed as IsActive = False
      {
        Write-Output "User $($InactiveUser.UPNPrefix) is inactive.  Remove them."
        #Function/code to remove these users
      }
    }
    
  • #77425

    Kevyn
    Participant

    You can remove the If statement, on line 15, from my previous post, if you don't want to output anything to the screen or a file about users you don't need to remove. Instead, you can do the following.

    $ActiveUsers = Get-FieldEmployees -IsActive $true
    $ActiveUsersUPNPrefix = $ActiveUsers | Select-Object -ExpandProperty UPNPrefix
    $InactiveUsers = Get-FieldEmployees -IsActive $false
    
    #Process active users
    ForEach($ActiveUser in $ActiveUsers)
    {
      #Function/code to process active users
      Write-Output "User $($ActiveUser.UPNPrefix) has IsActive = True"
    }
    
    #Process users who have IsAcive = False in the csv
    ForEach($InactiveUser in $InactiveUsers)
    {
      If($InactiveUser.UPNPrefix -notin $ActiveUsersUPNPrefix)
      {
        #Users are only listed as IsActive = False
        Write-Output "User $($InactiveUser.UPNPrefix) is inactive.  Remove them."
        #Function/code to remove these users
      }
    }
    
  • #77431

    Aaron Hardy
    Participant

    You could get all the inactive users and then check if the same user is also active. If there is no active record in the CSV for the user, execute your 'disable the user' code or do whatever you need to with that user. This allows any active users to be skipped and their records untouched.

    For example:

    Function Disable-OldEmployeeAccount {
    
        $InactiveUsers = Get-FieldEmployee
        $ActiveUsers = Get-FieldEmployee -IsActive $true
    
        foreach ($user in $InactiveUsers) {
            # if inactive user is not found in active user collection, disable the account
            if (-not ($ActiveUsers | Where-Object { $_.username -eq $user.username }) ) {
                # Your code to disable the user - in AD would require an identity like samAccountName, SID, DN, etc.
            }
        }
    }
    • #77433

      Kevyn
      Participant

      Thanks, Aaron. It's always cool to see another way to do things in PowerShell. 🙂

  • #77436

    Aaron Hardy
    Participant

    Teamwork. Here is the corrected version of the Disable-OldEmployeeAccount advanced function (I noticed the username key was used for the hashtable rather than UPNPrefix – I suggest you adjust to make things consistent if you can):

    Function Disable-OldEmployeeAccount {
    
        $InactiveUsers = Get-FieldEmployee
        $ActiveUsers = Get-FieldEmployee -IsActive $true
    
        foreach ($user in $InactiveUsers) {
            # if inactive user is not found in active user collection, disable the account
            if (-not ($ActiveUsers | Where-Object { $_.UPNPrefix -eq $user.UPNPrefix}) ) {
                # Your code to disable the user - in AD would require an identity like samAccountName, SID, DN, etc.
            }
        }
    }

    Edit: I suggest you consider using SupportsShouldProcess if you are using this advanced function to actually manipulate data or objects so you can use things like -Confirm, -WhatIf, etc.

  • #77440

    Aaron Hardy
    Participant

    My apologies for the inundation of updates but I realized some no-no's in my suggestions so here are some improvements. Here is the entire solution:

    # Get users from CSV based on IsActive.
    Function Get-FieldEmployee {
        [CmdletBinding()]
        param (
            [string]$Path = '\\blah\thing.csv',
            [bool]$IsActive
        )
    
        $Users = Import-CSV -Path $Path
    
        foreach ($user in $Users) {
            if (($user.location -eq "Field") -and ($user.isactive -eq $IsActive)) {
    
                $props = @{"firstname"=$user.first;
                           "lastname"=$user.last;
                           "UPNPrefix"=$user.username;
                           "location"=$user.location;
                           "IsActive"=$user.isactive }
                $obj = New-Object psobject -Property $props
                Write-Output $obj
            }
        }
    }
    
    # Return only inactive users (excluding user if active record exists).
    Function Get-InactiveAccountToDisable {
    
        $InactiveUsers = Get-FieldEmployee
        $ActiveUsers = Get-FieldEmployee -IsActive $true
    
        foreach ($user in $InactiveUsers) {
            # if inactive user is not found in active user collection, disable the account
            if (-not ($ActiveUsers | Where-Object { $_.UPNPrefix -eq $user.UPNPrefix}) ) {
                $props = @{ UPNPrefix = $user.UPNPrefix } # consider using samAccountName or SID
                $obj = New-Object psobject -Property $props
    
                Write-Output $obj
            }
        }
    }
    
    # Disable the inactive user. Pipeline input supported.
    Function Disable-InactiveUserAccount {
        [CmdletBinding()]
        param (
            [Parameter(Mandatory, ValueFromPipeline, ValueFromPipelineByPropertyName)]
            [string[]]$UPNPrefix
        )
    
        PROCESS {
            foreach ($UPN in $UPNPrefix) {
                # Execute code to disable user account, i.e. AD
                Disable-ADAccount -Identity $UPN
            }
        }
    }

    With these advanced functions, you can run Get-InactiveAccountToDisable to view which accounts could be disabled, or you could pipe it to Disable-InactiveAccount to get them all then disable all in one shot:

    Get-InactiveAccountToDisable | Disable-InactiveAccount

    Trusting I did not leave anything out, I hope this works for you. All the best.

  • #77461

    Nathan Stewart
    Participant

    Thank you all so much! I can't tell you how much I appreciate this. I've been working on this for nearly a week. Hopefully as I get more proficient, I'll be able to pay it forward. Thanks again!

  • #77488

    Aaron Hardy
    Participant

    Edit: Scratch the Compare-Object route as using foreach is more accurate in the filtering and returns the correct results.

  • #77544

    Curtis Smith
    Participant

    Ah, looks like there was a duplicate posting of this question. See other posting (https://powershell.org/forums/topic/account-provisioningdeprovisioning-from-single-csv/)

    Here was my suggestion from the other posting, updated with some of the CSV information from this post:

    $csv = @'
    First,Last,username,location,isactive
    first1,last1,user1,field,true
    first2,last2,user2,field,false
    first3,last3,user3,field,false
    first2,last2,user2,field,true
    first4,last4,user4,field,true
    first4,last4,user4,field,false
    first5,last5,user5,field,false
    first5,last5,user5,field,false
    '@ | ConvertFrom-Csv
    
    #
    # Above CSV contains the following sample scenarios
    # 1) Single user entry with status Active (User1)
    # 2) Double user entry with first status Inactive and second status Active (User2)
    # 3) Single user entry with status Inactive (User3)
    # 4) Double user entry with first status Active and second status Inactive (User4)
    # 5) Double user entry with first and second status Inactive (User5)
    #
    
    # Actual sample code for script below.  Above is only sample data.
    $csv | 
    Group-Object username |
    ForEach-Object {
        If ($_.Group.isactive -contains $true) {
            "$($_.Name) is Active"
            #Insert Code to handle active account
        } Else {
            "$($_.Name) is Inactive"
            #Insert Code to handle inactive account
        }
    }

    Results:

    user1 is Active
    user2 is Active
    user3 is Inactive
    user4 is Active
    user5 is Inactive
    • #77586

      Nathan Stewart
      Participant

      Thanks, Curtis! I must have accidentally submitted it twice.. Sorry about that!

You must be logged in to reply to this topic.