Office 365 Powershell - Export user, license type, and company field to csv file

I need to be able to export user name or email address (doesn't matter which), company (from the company field under the organization tab in a user account of the exchange admin console), and license type (e.g. exchange online e1, exchange online kiosk etc...)

I am able to export both values in two statements into two separate files but that doesn't do me much good.

I can export the username and license type with the following:

Get-MSOLUser | % { $user=$_; $_.Licenses | Select {$user.displayname},AccountSKuid } | Export-CSV "sample.csv" -NoTypeInformation

And, I can get the company values with the following:

Get-User | select company | Export-CSV sample.csv

Someone on another forum suggested this -

$index = @{}
Get-User | foreach-object {$index.Add($_.userprincipalname,$_.company)}
Get-MsolUser | ForEach-Object { write-host $_.userprincipalname, $index[$_.userprincipalname], $_.licenses.AccountSku.Skupartnumber}

That seems like it should work but it doesn't display any license info in my powershell, it's just blank. Also I wouldn't know how to export that to a csv file.

Any help would be appreciated. Thanks.

Solution 1:

This will export the licensed users with license type

Get-MsolUser -All |
  Where {$_.IsLicensed -eq $true } |
  Select DisplayName,UsageLocation,@{n="Licenses Type";e={$_.Licenses.AccountSKUid}},SignInName,UserPrincipalName,@{n="ProxyAddresses";e={$_.ProxyAddresses}} | 
  Export-Csv -Path C:\_Cory\Test.csv -NoTypeInformation

Solution 2:

Slight fix to support the newest commandlets:

• Line 4 updated to "$UserInfo = Get-MSOLUser -UserPrincipalName $msolUser.UserPrincipalName"
• Line 2: Add -All param

$lines = @()    foreach($msolUser in (Get-MSOLUser -All))    {
   $UserInfo = Get-MSOLUser -UserPrincipalName $msolUser.UserPrincipalName
   foreach($license in $msolUser.Licenses)
   {
       $lines += New-Object PsObject -Property @{
                   "Username"="$($UserInfo.DisplayName)";
                   "Company"="$($UserInfo.Company)";
                   "AccountSKUID"="$($license.AccountSKUid)"
                 }
   }    }    $lines | Export-CSV C:\output.csv -NoTypeInformation

It worked perfectly for me.

Solution 3:

In the first example you select the AccountSkuId property and in the second you go for AccountSKU.SkuPartNumber (which I'm pretty sure doesn't exist).

You're script/one-liner is also not too easy to read, how about this:

$lines = @()
foreach($msolUser in (Get-MSOLUser))
{
    $UserInfo = Get-User -Identity $msolUser.UserPrincipalName
    foreach($license in $msolUser.Licenses)
    {
        $lines += @{
                    "Username"="$($UserInfo.DisplayName)";
                    "Company"="$($UserInfo.Company)";
                    "AccountSKUID"="$($license.AccountSKUid)"
                  }
    }
}
$lines | Export-CSV C:\output.csv -NoTypeInformation

Much easier to get an overview of and maintain.