Import User from CSV and Find Office 365 License

Welcome Forums General PowerShell Q&A Import User from CSV and Find Office 365 License

Viewing 3 reply threads
  • Author
    • #216669
      Topics: 8
      Replies: 9
      Points: 138
      Rank: Participant

      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'); 
    • #216702
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      Can you show me a sample of your csv?

    • #216708
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      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!

    • #216777
      Topics: 8
      Replies: 9
      Points: 138
      Rank: Participant

      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
Viewing 3 reply threads
  • You must be logged in to reply to this topic.