Import User from CSV and Find Office 365 License

Hi guys

I have a script that is a “pending” leavers script as such. It essentially disables the user’s account, changes their password amongst other things but keeps their mailbox open for people to either forward emails or monitor the mailbox until they deem it OK to close the mailbox. Some of these leavers have Office 365 licenses and some just have an Exchange Online license.

The script imports the user details from CSV and then carries out the actions. I would like it to find out what license the user has and then make changes depending on the license that is assigned to the leaver. If the user has an Office 365 license, I’d like to remove it and assign an Exchange Online license. If the user has an Exchange Online license, it will just ignore it and move on. How can I incorporate that into the below script? I’ve struggled to find anything that allows me to edit the user’s license without implicitly specifying it exactly in PowerShell.

#Set the title of the window.

$host.ui.RawUI.WindowTitle = "LeaversPending Script"

Write-Host -ForegroundColor Yellow "Enter your Office 365 details"

$ulist = Import-Csv C:\folder\leaverspending.csv

$LeaversPending = 'OU=LeaversPending,OU=Azure,DC=domain,DC=domain'

$CloudCredential = Get-Credential


# Connect to Office 365 / Outlook Live

$CloudSessionParameters = @{

    ConfigurationName = 'Microsoft.Exchange'

    ConnectionUri     = ''

    Credential        = $CloudCredential

    Authentication    = 'Basic'

    AllowRedirection  = $true

    WarningAction     = 'SilentlyContinue'


$CloudSession = New-PSSession @CloudSessionParameters

Import-PSSession $CloudSession -Prefix Cloud -DisableNameChecking

#Connect to local Exchange

$LocalExchangeSessionParameters = @{        

    ConfigurationName = 'Microsoft.Exchange'

    ConnectionUri     = 'http://server/Powershell/'

    Authentication    = 'Kerberos'


$LocalExchangeSession = New-PSSession @LocalExchangeSessionParameters

Import-PSSession $LocalExchangeSession -DisableNameChecking

Connect-MsolService -Credential $CloudCredential

###### PART 1 ######


$ulist | ForEach-Object {

    try {

        $adacct = Get-ADUser $_.user -Properties Name, SamAccountname, UserPrincipalName -ErrorAction Stop

    } catch {

        Write-Error "User $($_.user) does not exist, cannot disable"

        Add-Content -Path C:\folder\UsersNotProcessed.log -Value $_.user

        # Skips to the next user in $ulist, does not disable anything



    $sam = Get-ADUser $_.user |Select-Object SamAccountName

    $upn = Get-ADUser $_.user |Select-Object userprincipalname

    $body = "The above user has been moved to the LeaversPending OU in AD. The below has been completed:

    Password changed to random password

    AD account disabled

    ActiveSync disabled

    OWA for Devices disabled

    OWA disabled

    Removed from all DDGs

    Converted to a shared mailbox."

    #Change AD Password to a random password

    Write-Host -ForegroundColor Yellow "Changing AD Password to Random Password"

    $Pwd = -join ((48..122) | Get-Random -Count 16 | ForEach-Object { [char]$_ })

    $PwdSecStr = ConvertTo-SecureString $pwd -AsPlainText -Force

    Set-ADAccountPassword -Identity $adacct.SamAccountName -NewPassword $PwdSecStr -Reset

    Write-Host -ForegroundColor Green "Password changed for $($adacct.Name)"


    #Disable AD account

    Write-Host -ForegroundColor Yellow "Disabling user account on AD"

    Disable-ADAccount -Identity $adacct.SamAccountName

    Write-Host -ForegroundColor Green "Disabled AD account"


    #Disable ActiveSync

    Write-Host -ForegroundColor Yellow "Disabling ActiveSync"

    Set-CloudCASMailbox -Identity $upn.userprincipalname -ActiveSyncEnabled $false

    Write-Host -ForegroundColor Green "ActiveSync disabled"


    #Disable OWA for Devices

    Write-Host -ForegroundColor Yellow "Disabling OWA for Devices"

    Set-CloudCASMailbox -Identity $upn.userprincipalname -OWAforDevicesEnabled $false

    Write-Host -ForegroundColor Green "OWA for Devices disabled"


    #Disable OWA

    Write-Host -ForegroundColor Yellow "Disabling Outlook on the web"

    Set-CloudCASMailbox -Identity $upn.userprincipalname -OWAEnabled $false

    Write-Host -ForegroundColor Green "Outlook on the web disabled"


    #Disable MAPI

    #Write-Host -ForegroundColor Yellow "Disabling MAPI"

    #Set-CloudCASMailbox -Identity $upn.userprincipalname -MAPIEnabled $false

    #Write-Host -ForegroundColor Green "MAPI disabled"


    #Setting custom attribute 1 to 'Exclude' so the leaver is not included in DDGs

    Write-Host -ForegroundColor Yellow "Removing from Dynamic Distribution Groups"

    Set-RemoteMailbox -Identity $upn.userprincipalname -CustomAttribute1 Exclude

    Write-Host -ForegroundColor Green "Removed from all Dynamic Distribution Groups"


    #Set mailbox to a shared mailbox

    Write-Host -ForegroundColor Yellow "Changing the mailbox to a shared mailbox"

    Set-CloudMailbox -Identity $upn.userprincipalname -Type Shared

    Write-Host -ForegroundColor Green "The mailbox has been converted to a shared mailbox"


    #Remove Office 365 License

    #Write-Host -ForegroundColor Yellow "Removing any Office 365 license"

    ###### PART 2 ######


    ### Get AD user details again as the user has moved OU

    $adacct = Get-ADUser $_.user

    $ticket = $_.ticket

    Write-Host -ForegroundColor Yellow "Now moving user to LeaversPending OU"

    Move-ADObject -Identity $adacct.DistinguishedName -TargetPath $LeaversPending

    Write-Host -ForegroundColor Green "Moved to LeaversPending OU"


    Write-Host -ForegroundColor Yellow "Generating and sending user status report directly into ticket"

$report = $adacct | Select-Object Name, SamAccountname, UserPrincipalName | Out-String

    #Sends SMTP email via o365 smtp relay

    $sendMailMessageSplat = @{

       Subject    = "[#INC-$($_.ticket)]"

        From       = ''

        To         = ''

       SmtpServer = ''

        Body       = $report + $body


    Send-MailMessage @sendMailMessageSplat


Write-Host -ForegroundColor Yellow "Syncing AD with Azure"


$sazure = New-PSSession -ComputerName Server

Invoke-Command -Session $sazure -ScriptBlock {C:\ITDept\ps\AzureADDeltaSync.ps1}

Write-Host -ForegroundColor Green "AD is now syncing with Azure"


Write-Host -ForegroundColor Green "LeaversPending process is now complete."


Write-Host 'Press any key to exit.';

$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown'); 

Can you show me a sample of your csv?

Hello, the following should work. I did not have exchange online licenses to test with. I set the script to check if it is exchange online, versus checking if it’s something else.

#set the users UPN - probably $upn.userprincipalname but i'll let you fill this in. Should be just the email or login
$userUPN= "" #<user account UPN, such as>
$licensePlanList = Get-AzureADSubscribedSku
#Get the users assigned SkuID
$userList = Get-AzureADUser -ObjectID $userUPN | Select -ExpandProperty AssignedLicenses | Select SkuID
#Look up the corresponding plan from the sku
$assignedlicense = $userList | ForEach { $sku=$_.SkuId ; $licensePlanList |
ForEach { If ( $sku -eq $_.ObjectId.substring($_.ObjectId.length - 36, 36) ) { Write-Host $_.SkuPartNumber } } }
#Modify this to be like the actual license you want to apply. This will find the business essentials license, you may have enterprise or others.
$accountskuid = Get-MsolAccountSku | ? accountskuid -Like '*exchange online*' | select -ExpandProperty AccountSkuId
#Test if the license is like exchange online (I did not have any of these licenses to test with)
if($null -eq $assignedlicense){
write-host"No license is applied to user $userUPN"
if($assignedlicense -notlike '*exchange online*'){
Write-Host"License for $userUPN is not exchange online"
#if no license or not exchange online license, set license
if($nolicense -or $assignedlicense){
Set-MsolUserLicense-UserPrincipalName $userUPN-AddLicenses $accountskuid


I also accounted for the user having no license. If so, they will also apply the exchange online license. Please note on this line

$accountskuid = Get-MsolAccountSku | ? accountskuid -Like '*essentials*' | select -ExpandProperty AccountSkuId

if there is more than one match to the -like condition, it will try to apply the first license it retrieved.


I hope this helps!

Hi Doug

Thanks for your responses. I’ll give it a go tomorrow. FYI, the CSV is very basic:

User Email Ticket reference
joe.bloggs INC-1234