# Import User from CSV and Find Office 365 License **URL:** \ **Category:** PowerShell Help\ **Created:** [April 8, 2020, 10:56am UTC](https://forums.powershell.org/t/import-user-from-csv-and-find-office-365-license/14120 "2020-04-08T10:56:11Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![sicfc1](https://avatars.discourse-cdn.com/v4/letter/s/0ea827/32.png) [@sicfc1](https://forums.powershell.org/u/sicfc1)\ **Post date:** [April 8, 2020, 10:56am UTC](https://forums.powershell.org/t/import-user-from-csv-and-find-office-365-license/14120/1 "2020-04-08T10:56:11Z")
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-ForegroundColorYellow"Enter your Office 365 details"$ulist=Import-CsvC:\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=$CloudCredentialAuthentication='Basic'AllowRedirection=$trueWarningAction='SilentlyContinue'} $CloudSession=New-PSSession@CloudSessionParametersImport-PSSession$CloudSession-PrefixCloud-DisableNameChecking #Connect to local Exchange $LocalExchangeSessionParameters= @{ ConfigurationName='Microsoft.Exchange'ConnectionUri ='http://server/Powershell/'Authentication='Kerberos'} $LocalExchangeSession=New-PSSession@LocalExchangeSessionParametersImport-PSSession$LocalExchangeSession-DisableNameCheckingConnect-MsolService-Credential$CloudCredential###### PART 1 ###### ####################$ulist|ForEach-Object {try{$adacct=Get-ADUser$\_.user -PropertiesName,SamAccountname,UserPrincipalName-ErrorActionStop}catch{Write-Error"User$($\_.user)does not exist, cannot disable"Add-Content-PathC:\folder\UsersNotProcessed.log-Value$\_.user# Skips to the next user in $ulist, does not disable anythingcontinue}$sam=Get-ADUser$\_.user |Select-ObjectSamAccountName$upn=Get-ADUser$\_.user |Select-Objectuserprincipalname$body="The above user has been moved to the LeaversPending OU in AD. The below has been completed:Password changed to random passwordAD account disabledActiveSync disabledOWA for Devices disabledOWA disabledRemoved from all DDGsConverted to a shared mailbox."#Change AD Password to a random passwordWrite-Host-ForegroundColorYellow"Changing AD Password to Random Password"$Pwd=-join ((48..122) |Get-Random-Count16|ForEach-Object { [char]$\_ })$PwdSecStr=ConvertTo-SecureString$pwd-AsPlainText-ForceSet-ADAccountPassword-Identity$adacct.SamAccountName -NewPassword$PwdSecStr-ResetWrite-Host-ForegroundColorGreen"Password changed for$($adacct.Name)"Write-Host#Disable AD accountWrite-Host-ForegroundColorYellow"Disabling user account on AD"Disable-ADAccount-Identity$adacct.SamAccountNameWrite-Host-ForegroundColorGreen"Disabled AD account"Write-Host#Disable ActiveSyncWrite-Host-ForegroundColorYellow"Disabling ActiveSync"Set-CloudCASMailbox-Identity$upn.userprincipalname -ActiveSyncEnabled$falseWrite-Host-ForegroundColorGreen"ActiveSync disabled"Write-Host#Disable OWA for DevicesWrite-Host-ForegroundColorYellow"Disabling OWA for Devices"Set-CloudCASMailbox-Identity$upn.userprincipalname -OWAforDevicesEnabled$falseWrite-Host-ForegroundColorGreen"OWA for Devices disabled"Write-Host#Disable OWAWrite-Host-ForegroundColorYellow"Disabling Outlook on the web"Set-CloudCASMailbox-Identity$upn.userprincipalname-OWAEnabled$falseWrite-Host-ForegroundColorGreen"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 DDGsWrite-Host-ForegroundColorYellow"Removing from Dynamic Distribution Groups"Set-RemoteMailbox-Identity$upn.userprincipalname -CustomAttribute1ExcludeWrite-Host-ForegroundColorGreen"Removed from all Dynamic Distribution Groups"Write-Host#Set mailbox to a shared mailboxWrite-Host-ForegroundColorYellow"Changing the mailbox to a shared mailbox"Set-CloudMailbox-Identity$upn.userprincipalname-TypeSharedWrite-Host-ForegroundColorGreen"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=$\_.ticketWrite-Host-ForegroundColorYellow"Now moving user to LeaversPending OU"Move-ADObject-Identity$adacct.DistinguishedName-TargetPath$LeaversPendingWrite-Host-ForegroundColorGreen"Moved to LeaversPending OU"Write-HostWrite-Host-ForegroundColorYellow"Generating and sending user status report directly into ticket"$report=$adacct|Select-ObjectName,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-ForegroundColorYellow"Syncing AD with Azure" Write-Host$sazure=New-PSSession-ComputerNameServerInvoke-Command-Session$sazure-ScriptBlock {C:\ITDept\ps\AzureADDeltaSync.ps1}Write-Host-ForegroundColorGreen"AD is now syncing with Azure" Write-HostWrite-Host-ForegroundColorGreen"LeaversPending process is now complete." Write-HostWrite-Host'Press any key to exit.';$null=$Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown'); ``` ---
**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\ **Post date:** [April 8, 2020, 12:37pm UTC](https://forums.powershell.org/t/import-user-from-csv-and-find-office-365-license/14120/2 "2020-04-08T12:37:35Z")
Can you show me a sample of your csv? ---
**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\ **Post date:** [April 8, 2020, 1:19pm UTC](https://forums.powershell.org/t/import-user-from-csv-and-find-office-365-license/14120/3 "2020-04-08T13:19:35Z")
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" #\   $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! ---
**Author:** ![sicfc1](https://avatars.discourse-cdn.com/v4/letter/s/0ea827/32.png) [@sicfc1](https://forums.powershell.org/u/sicfc1)\ **Post date:** [April 8, 2020, 4:15pm UTC](https://forums.powershell.org/t/import-user-from-csv-and-find-office-365-license/14120/4 "2020-04-08T16:15:10Z")
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 | ---
**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\ **Post date:** [May 16, 2024, 8:29pm UTC](https://forums.powershell.org/t/import-user-from-csv-and-find-office-365-license/14120/5 "2024-05-16T20:29:57Z")