Solved

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

Posted on 2013-06-21
2
3,517 Views
Last Modified: 2013-06-21
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

Open in new window


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

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

Open in new window


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}

Open in new window


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.


Lastly I received one last suggestion in another forum -

$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

Open in new window


This seemed like it should have worked but the output looked like this - http://i.imgur.com/4j4CyHM.png

I'm a beginner with powershell so any help would be appreciated.
0
Comment
Question by:asgJimk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
footech earned 500 total points
ID: 39267308
I think that last example was almost there.  But instead of filling up the $lines array with hashtables, you need to fill it with objects with the desired properties (which can be specified as a hashtable).  Try the following:
$lines = @()
foreach($msolUser in (Get-MSOLUser))
{
    $UserInfo = Get-User -Identity $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

Open in new window

0
 

Author Comment

by:asgJimk
ID: 39267341
Thanks.  That is exactly what I needed!!
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question