Solved

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

Posted on 2013-06-21
2
3,455 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

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 …
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

732 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