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

      Can you show me a sample of your csv?

    • #216708
      Participant
      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@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!

    • #216777
      Participant
      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 joe.bloggs@email.com INC-1234
Viewing 3 reply threads
  • You must be logged in to reply to this topic.