Solved

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

Posted on 2013-06-21
2
3,235 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
2 Comments
 
LVL 39

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

3 Use Cases for Connected Systems

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

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now