Import User from CSV and Find Office 365 License

sicfc1 · April 8, 2020, 10:56am

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     = 'https://outlook.office365.com/Powershell'

    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

        continue

    }

    $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)"

    Write-Host

    #Disable AD account

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

    Disable-ADAccount -Identity $adacct.SamAccountName

    Write-Host -ForegroundColor Green "Disabled AD account"

    Write-Host

    #Disable ActiveSync

    Write-Host -ForegroundColor Yellow "Disabling ActiveSync"

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

    Write-Host -ForegroundColor Green "ActiveSync disabled"

    Write-Host

    #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"

    Write-Host

    #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"

    Write-Host

    #Disable MAPI

    #Write-Host -ForegroundColor Yellow "Disabling MAPI"

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

    #Write-Host -ForegroundColor Green "MAPI disabled"

    #Write-Host

    #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"

    Write-Host

    #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"

    Write-Host

    #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

    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       = 'leaverspending@domain.com'

        To         = 'support@domain.com'

       SmtpServer = 'o365relay.com'

        Body       = $report + $body

    }

    Send-MailMessage @sendMailMessageSplat

}

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

Write-Host

$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

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

Write-Host

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

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

krzydoug · April 8, 2020, 12:37pm

Can you show me a sample of your csv?

krzydoug · April 8, 2020, 1:19pm

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@domain.com" #<user account UPN, such as belindan@contoso.com>
 
$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"
    $nolicense=$true
}
if($assignedlicense -notlike '*exchange online*'){
    Write-Host"License for $userUPN is not exchange online"
    $notexchangelicense=$true
}
 
#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!

sicfc1 · April 8, 2020, 4:15pm

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	joe.bloggs@email.com	INC-1234

Topic		Replies	Views
Leaver Script Help PowerShell Help	4	163	May 17, 2019
Powershell O365 PowerShell Help	0	130	July 7, 2022
Trouble with o365/exchange script PowerShell Help	1	146	October 9, 2014
Office 365 powershell script issue PowerShell Help	1	178	February 27, 2019
Batch import of users into Microsoft exchange PowerShell Help	4	152	September 24, 2021

Import User from CSV and Find Office 365 License

Related Topics