3

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.

6 Answers 6

4

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
4

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.

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.

3
  • Thanks. I don't really know powershell or scripting, all those commands were ones I found in other forums etc... I copy and pasted your commands into powershell and the output did not look right in the csv file. Please see the image for an example - link
    – ASGJim
    Jun 21, 2013 at 19:07
  • If you don't really understand the above or know what you're looking for, and you're venturing into Office 365 management I strongly suggest the following: 1) Read through some of the great resources for powershell online. 2) Get familiar with the Office 365 Powershell basics Jun 21, 2013 at 19:14
  • OK. I just needed this one output every month for billing purposes, I'm not trying to manage office 365. Thanks for the input.
    – ASGJim
    Jun 21, 2013 at 19:19
1

I just tested this and it's PERFECT THANK YOU VERY MUCH FOR YOUR HELP!

$lines = @()
foreach($msolUser in (Get-MSOLUser -ALL | where {$_.isLicensed -eq $true}))
{
    $UserInfo = Get-User -identity $msolUser.UserPrincipalName
    foreach($license in $msolUser.Licenses)
    {
        $lines += New-Object PsObject -Property @{
                    "Nom/Prenom"="$($UserInfo.DisplayName)";
                    "Societe"="$($UserInfo.Company)";
                    "AdressePrincipale"="$($UserInfo.UserPrincipalName)";
                    "Licences"="$($license.AccountSKUid)"
                  }
    }
}
$lines | Export-CSV C:\out1.csv -Delimiter ";" -Encoding Unicode
0

This script worked great for me but I couldn't get it to export directly to CSV so I used copy/paste from into Excel.

Get-MsolUser -all | Where-Object {$_.isLicensed -eq "True"} | Select UserPrincipalName,DisplayName,Licenses | Out-GridView
0

Well as you dont know the powershell or scripting, you should automate the whole powershell Export log process with standard windows instruments. There are many third party tools that can help. try searching for them . it will make ur work easier

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .