User Provisioning/Deprovisioning from single CSV

PowerShell Help
1

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!

2

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.

3

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…

4

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

5

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
  }
}
6

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
  }
}
7

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.
        }
    }
}
8

Thanks, Aaron. It’s always cool to see another way to do things in PowerShell. :slight_smile:

9

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.

10

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.

11

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!

12

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

13

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
14

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