Skip to content

How to bulk assign Office 365 licenses from CSV file

March 13, 2013

The most frequest gripe I hear from customers regarding Office 365 is license management. Managing licenses manually through the MOP for one or two users is fine. Managing licenses for 15,000 users is a bit more involved. This post deals with bulk license assignment using a CSV input file.

1. Log on to a machine that has ‘Microsoft Online Services Module for Windows PowerShell’ installed. You can install this on your own workstation but it should also be installed on your ADFS Servers.

2. Create a new text file. The example used here is called c:\temp\userlist.csv

3. Open c:\temp\userlist.csv in Notepad

4. Populate userlist.csv

  • The first row should contain the heading UserPrincipalName
  • All remaining rows should contain a list of Active Directory UPNs for the users that you want to license (one UPN per line). If you’re a multinational tenant with multiple usage locations then don’t add users in different countries to the same CSV file. The process detailed in this article will assign all users in the CSV file with the same ‘Usage Location’ value in MSOL.

See the following example:

2013-03-07csv1

5. Open Microsoft Online Services Module for Windows PowerShell:

2013-03-07csv2

6. Enter the following command and specify credentials for an account that has administrator permissions to your MSOL subscription (such as admin@{tenant_id}.onmicrosoft.com):

$cred = Get-Credential

2013-03-07csv3

7. Enter the following command to connect to Microsoft Online Services:

Connect-MsolService -Credential $cred

2013-03-07csv4

8. Enter the following command to retrieve your organization’s license details:

Get-MsolAccountSku |ft AccountSkuId

9. Note the AccountSkuId value returned and use in the next step:

2013-03-07csv5

10. Run the following command, editing the first two lines as necessary:

$AccountSkuId = "{AccountSkuId}"
$UsageLocation = "{UsageLocation}"
$Users = Import-Csv c:\temp\userlist.csv
$Users | ForEach-Object {
Set-MsolUser -UserPrincipalName $_.UserPrincipalName -UsageLocation $UsageLocation
Set-MsolUserLicense -UserPrincipalName $_.UserPrincipalName -AddLicenses $AccountSkuId
}

For example:

$AccountSkuId = "ExitCodeZero:STANDARDWOFFPACK"
$UsageLocation = "GB"
$Users = Import-Csv c:\temp\userlist.csv
$Users | ForEach-Object {
Set-MsolUser -UserPrincipalName $_.UserPrincipalName -UsageLocation $UsageLocation
Set-MsolUserLicense -UserPrincipalName $_.UserPrincipalName -AddLicenses $AccountSkuId
}

2013-03-07csv6

11. You can now confirm the license assignment via the Microsoft Online Portal:

2013-03-07csv7

2013-03-07csv8

Advertisements
6 Comments
  1. Dave Bole permalink

    This used to work great it would create the users and licence them. Now it fails at “new-msoluser -us” saying
    WARNING: One or more headers were not specified. Default names starting with “H” have been used in place of any missing
    headers.
    New-MsolUser : A positional parameter cannot be found that accepts argument ‘-‘.
    At line:1 char:64
    + Import-Csv C:\Users\David\Desktop\PupilsBlank1.csv | foreach { New-MsolUser -Use …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Import-Csv C:\Users\Desktop\Office365files\y7pupils4.csv | foreach { New-MsolUser –UserPrincipalName $_.UPN –DisplayName $_.DisplayName -FirstName $_.FirstName –LastName $_.LastName –Password $_.Password – UsageLocation GB}
    Import-Csv C:\Users\Desktop\Office365files\y7pupils4.csv | Foreach {Set-MsolUserLicense -UserPrincipalName $_.UPN -addlicense “contosoco:STANDARDWOFFPACK_STUDENT”}

    • Hi Dave. It looks like a problem with the headers in your import CSV file. Check the headers match your script (so the column containing your user principal names, for example, has a header of ‘UPN’). I don’t think the file format matters (unicode vs ANSII) but the headers must match and the file must be correctly delimited. Remove any blank lines too, although this isn’t causing the problem you’re experiencing.

      Hope this helps. Regards, Dave

      • Dave Bole permalink

        Hi Dave, thanks for your reply.

        I’m going out of my mind with this. I took fully on-board what you said and went back to a script I used two years ago. I removed all the users except one and deleted that user from Office 365. I ran the script and got the same error.

        So I created a new csv and ran a more simplified script:

        Import-Csv C:\Users\Me\Desktop\Year7.csv | foreach {New-MsolUser –UPN $_.UPN -Password $_.Password – UsageLocation GB}

        Error=

        New-MsolUser : A parameter cannot be found that matches parameter name ‘UPN’.
        At line:1 char:76
        + Import-Csv C:\Users\Me\Desktop\Year7.csv | foreach {New-MsolUser
        -UPN …
        +
        ~~~~
        + CategoryInfo : InvalidArgument: (:) [New-MsolUser], ParameterBi
        ndingException
        + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.Online.Administ
        ration.Automation.NewUser

        So I changed -UPN to -UserPrincipleName and got the same error.

        So I tried this:

        Import-Csv C:\Users\me\Desktop\Year7.csv | foreach {New-MsolUser -DisplayName $_.DisplayName -UserPrincipalName $_.UPN -Password $_.Password – UsageLocation GB}

        Error=

        New-MsolUser : A positional parameter cannot be found that accepts argument
        ‘-‘.
        At line:1 char:63
        + Import-Csv C:\Users\Me\Desktop\Year7.csv | foreach {New-MsolUser
        -Disp …
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        ~~~
        + CategoryInfo : InvalidArgument: (:) [New-MsolUser], ParameterBi
        ndingException
        + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.Online.Adm
        inistration.Automation.NewUser

        It doesn’t look as if its getting to the CSV before the command is failing.

  2. Hi Dave

    You’ve got a space between the last hyphen in your script and UsageLocation.

    Change your command line from:
    Import-Csv C:\Users\me\Desktop\Year7.csv | foreach {New-MsolUser -DisplayName $_.DisplayName -UserPrincipalName $_.UPN -Password $_.Password – UsageLocation GB}

    to:
    Import-Csv C:\Users\me\Desktop\Year7.csv | foreach {New-MsolUser -DisplayName $_.DisplayName -UserPrincipalName $_.UPN -Password $_.Password –UsageLocation GB}

    Hope this helps! Regards, Dave

  3. Jesse permalink

    If we are using a Hybrid environment where the mailbox is on premise, do you know which licenses we would use? I would like their work mailbox to show up in the interface for Office 365, or vice versa have the OneDrive show up in OWA. Do you know of any links that explain this? We have single sign-on setup and working and Hybrid is setup and it says it is working, but I am not sure how we actually integrate the two experiences together.

  4. Alex permalink

    Thank you so much for this posting. This has helped me tremendously. If I wanted to log an output file after i entered my commads, what is the best practice on doing that?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: