A Qlik-Cli backed PowerShell script to assign users user access passes from a flat file (XLSX or CSV)

qs-cli-UserLicenseAutoAssigner.ps1

#Requires -Modules Qlik-Cli

# Assumes the ImportExcel module: `Install-Module -Name ImportExcel`
#############################
## Configurable Parameters ##
#############################

$inputFile = '<absolute file path>/<filename>.<fileExtension>'
# column number of sheet id column in Excel file if using Excel
#$sheetIdColumnNumber = '2'

#######################
## System Parameters ##
#######################

# Analyze the Host.cfg file to determine the hostname
$Data = Get-Content C:\ProgramData\Qlik\Sense\Host.cfg
# Convert the base64 encoded install name for Sense to UTF data
$FQDN = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($($Data)))

###################
##### Logging #####
###################

# Function for logging
function Get-TimeStamp {
    return "[{0:MM/dd/yyyy} {0:HH:mm:ss}]" -f (Get-Date)
}

# Build log file
Set-Location $PSScriptRoot
$logdir = $PSScriptRoot + '\logs'
$logfile = $logdir + "\" + "userLicenseAutoAssigner" + ".log"
if (!(Test-Path $logdir)){
    New-Item -path $logdir -type directory | Out-Null
}

# Check file for file extension
$fileExtension = Get-Item -Path $inputFile | Select-Object Extension

# Conditionally deploy based on the file type
if ($fileExtension.Extension -eq '.xlsx') {
    # This is an Excel file
    Write-Output "$(Get-TimeStamp): $($fileExtension.Extension) found"  | Out-File -FilePath $logfile -Append
    $users = @((Import-Excel $inputFile -DataOnly -StartColumn $sheetIdColumnNumber -EndColumn $($sheetIdColumnNumber + 1)).USERID)
} elseif ($fileExtension.Extension -eq '.csv') {
    Write-Output "$(Get-TimeStamp): $($fileExtension.Extension) found"  | Out-File -FilePath $logfile -Append
    $users = (Import-Csv -Path $inputFile).USERID
} else {
    Write-Output "$(Get-TimeStamp): Unsupported file, exiting"  | Out-File -FilePath $logfile -Append
    Exit
}

# Split off the userDirectory
$userDir = $users.Split('\')[0]
# Store userIds as string after the userDir\
$users = $users.SubString(((($users.Split('\')[0]).Length)+1)) 

Write-Output "$(Get-TimeStamp): $($users.count) users found"  | Out-File -FilePath $logfile -Append

# Connect to Qlik
Write-Output "$(Get-TimeStamp): Begin connect to Qlik Sense"  | Out-File -FilePath $logfile -Append
$Certificate = Get-ChildItem -Path cert:CurrentUser\My | where {$_.Subject -eq "CN=QlikClient"} 
Connect-Qlik -ComputerName https://$($FQDN):4242 -Username INTERNAL\sa_api -Certificate $Certificate -ErrorAction SilentlyContinue | Out-Null

$tmp = Invoke-QlikGet -path /qrs/about -ErrorAction SilentlyContinue

if ($($tmp.buildVersion).Length -gt 0) {
    Write-Output "$(Get-TimeStamp): Connected to Qlik Sense"  | Out-File -FilePath $logfile -Append
} else {
    Write-Output "$(Get-TimeStamp): Failed to connect to Qlik Sense. Exiting."  | Out-File -FilePath $logfile -Append
    Exit
}

# Start Counter & Loop
$counter = 0
foreach ($user in $users) {
    ++$counter
    $usertmp = ''
    # Check if user exists, elseif >1 record for userId then exit, else create the user
    $usertmp = Get-QlikUser -filter "userId eq '$($user)'"
    if($($usertmp.id).count -eq 1) {
        Write-Output "$(Get-TimeStamp): 1 user with userId of $($usertmp.userId) found"  | Out-File -FilePath $logfile -Append
    } elseif ($($usertmp.id).count -gt 1) {
        Write-Output "$(Get-TimeStamp): >1 user with userId of $($usertmp.userId) found, exiting"  | Out-File -FilePath $logfile -Append
        Exit
    } else {
        # Create the user
        Write-Output "$(Get-TimeStamp): Creating user $($user)"  | Out-File -FilePath $logfile -Append
        $userbody = '{
            "userId": "'
        $userbody += $($user)
        $userbody += '",
            "userDirectory": "'
        $userbody += $($userDir)
        $userbody += '",
            "name": "'
        $userbody += $($user)
        $userbody += '",
          }'
        Invoke-QlikPost -path /qrs/user -body $userbody | Out-Null
        $usertmp = Get-QlikUser -filter "userId eq '$($user)'"
    }
    # Check for license, else create it
    $licensetmp = ''
    # /qrs/license/useraccesstype/full?filter=(user.userId eq 'ltu')
    $licensetmp = Invoke-QlikGet -path "/qrs/license/useraccesstype/full?filter=(user.userId eq '$($user)')"
    if($($licensetmp.id).count -eq 1) {
            Write-Output "$(Get-TimeStamp): $($user) already has a license"  | Out-File -FilePath $logfile -Append
        } else {
        # Assign license
        $body = '{
        "user": {
          "id": "'
        $body += $usertmp.id 
        $body +='"
        }
        }'
        Invoke-QlikPost -path "/qrs/license/useraccesstype" -body $body | Out-Null
        #Write-Output "$(Get-TimeStamp): $($user) assigned license"  | Out-File -FilePath $logfile -Append
        $licensetmp = Invoke-QlikGet -path "/qrs/license/useraccesstype/full?filter=(user.userId eq '$($user)')"
        if($($licensetmp.id).count -eq 1) {
            Write-Output "$(Get-TimeStamp): $($user) now has a license"  | Out-File -FilePath $logfile -Append
        } else {
            Write-Output "$(Get-TimeStamp): $($user) license assignment failed"  | Out-File -FilePath $logfile -Append
        }
    }
    Write-Output "$(Get-TimeStamp): $($counter) of $($users.Count) users processed"  | Out-File -FilePath $logfile -Append
}