none
Office 365 License Inventory RRS feed

  • Question

  • I'm trying to pull Office 365 licenses into Excel.

    This code is run from the "Windows Azure Active Directory Module for Windows PowerShell" and requires installation of "Microsoft Online Services Sign-In Assistant for IT Professionals"  see below links for more info:

    azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

    Microsoft.com/en-us/download/details.aspx?id=28177

    My questions is this:

    1. How can I construct the script so I don't have to enter my creds every time I run it?  Is there a way I can test if I'm connected/authenticated?

    2. How can I export this into a .csv rather that dumping comma-separated strings into a txt file?  This current method works well enough, but I'd like to extend/expand the script and want to eliminate the "import into Excel from text file" step (I had trouble trying to make an array of comma-separated strings).

    thx in advance!

    if(-not (Get-Module -name 'MSOnline')) {
        Import-Module MSOnline
    }
    $mscred = get-credential
    connect-msolservice -credential $mscred
    
    $domain = 'mydomain.com'
    $licfile = 'c:\o365\Licenses.txt'
    
    foreach($msusr in (Get-MSOLUser -All | Where-Object { (($_.isLicensed -eq 'True') -and 
        ($_.UserPrincipalName -match $domain)) })) {
        foreach ($license in $msusr.Licenses)
        {
            $licinfo = ($($msusr.DisplayName) + ',' + $($msusr.UserPrincipalName) + ',' + 
                 ($($license.AccountSKUid).Split(':')[1]))
            Add-Content $licfile $licinfo
        }
    }
    

    Wednesday, May 13, 2015 12:09 AM

Answers

  • Hi Jott,

    To store credential in powershell, please follow these steps:

    1.  Please create a file to store your password (as an encrypted string). The following line will prompt for a password then store it in d:\anna\securestring.txt as an encrypted string.

    read-host -assecurestring | convertfrom-securestring | out-file d:\anna\securestring.txt

    2.  Wherever you see a -Credential argument on a PowerShell command then it means you can pass a PSCredential.

    The final script list as below:

    $username = "online username"
    $password = cat D:\anna\securestring.txt | convertto-securestring
    $cred = new-object -typename System.Management.Automation.PSCredential `
             -argumentlist $username, $password
    
    $output=@()
    foreach($msusr in (Get-MSOLUser -All | Where-Object { ($_.isLicensed -eq 'True') })) {
     foreach ($license in $msusr.Licenses)
        {
            $output+=New-Object PSObject -Property @{            
            DisplayName       = $msusr.DisplayName                
            UserPrincipalName = $msusr.UserPrincipalName             
            AccountSKUid      = $license.AccountSKUid }
    	}
    }
    
    $output| Export-Csv d:\O365.csv -NoTypeInformation

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Edited by AnnaWYModerator Wednesday, May 13, 2015 3:17 AM
    • Proposed as answer by Chen VMVP Wednesday, May 13, 2015 11:34 AM
    • Marked as answer by jott220 Thursday, May 14, 2015 5:09 PM
    Wednesday, May 13, 2015 3:16 AM
    Moderator

All replies

  • Hi Jott,

    To store credential in powershell, please follow these steps:

    1.  Please create a file to store your password (as an encrypted string). The following line will prompt for a password then store it in d:\anna\securestring.txt as an encrypted string.

    read-host -assecurestring | convertfrom-securestring | out-file d:\anna\securestring.txt

    2.  Wherever you see a -Credential argument on a PowerShell command then it means you can pass a PSCredential.

    The final script list as below:

    $username = "online username"
    $password = cat D:\anna\securestring.txt | convertto-securestring
    $cred = new-object -typename System.Management.Automation.PSCredential `
             -argumentlist $username, $password
    
    $output=@()
    foreach($msusr in (Get-MSOLUser -All | Where-Object { ($_.isLicensed -eq 'True') })) {
     foreach ($license in $msusr.Licenses)
        {
            $output+=New-Object PSObject -Property @{            
            DisplayName       = $msusr.DisplayName                
            UserPrincipalName = $msusr.UserPrincipalName             
            AccountSKUid      = $license.AccountSKUid }
    	}
    }
    
    $output| Export-Csv d:\O365.csv -NoTypeInformation

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com

    • Edited by AnnaWYModerator Wednesday, May 13, 2015 3:17 AM
    • Proposed as answer by Chen VMVP Wednesday, May 13, 2015 11:34 AM
    • Marked as answer by jott220 Thursday, May 14, 2015 5:09 PM
    Wednesday, May 13, 2015 3:16 AM
    Moderator
  • Thanks for the reply.

    Using PSObject is working well:

            foreach ($license in $msusr.Licenses) {
                $sku = $($license.AccountSKUid).Split(':')
                $arr += New-Object PSObject -Property @{
                    DisplayName = $($msusr.DisplayName)
                    UserPrincipalName = $($msusr.UserPrincipalName)
                    LicenseSKU = $sku[1]
                }
            }

    No more txt file!

    As for using a secure string saved in a text file, I understand how that works, but I'm looking for a way to test if the session is established.  In pseudocode it might look like this:

    if (-not msolservice) {
       $cred = get-credential
       connect-msolservice -credential $cred
    }

    The goal here is to avoid being prompted again if I need to run the same script more than once.  My guess is the session will time out eventually... but either way, it would be helpful to have a way to test if the connection has already been established.  I don't know if there is a way to do this.

    Thursday, May 14, 2015 12:21 AM
  • Hi Jott,

    Since if you ran the "read-host -assecurestring" once, then you have saved password as a secure string in text file.

    So you can run the script multiple time without prompt if you have the "d:\anna\securestring.txt" file available, and I ran the script below serveral time without issue:

    $username = "username"
    $password = cat D:\anna\securestring.txt | convertto-securestring
    $cred = new-object -typename System.Management.Automation.PSCredential `
             -argumentlist $username, $password
    Connect-MsolService -Credential $cred
    $output=@()
    foreach($msusr in (Get-MSOLUser -All | Where-Object { ($_.isLicensed -eq 'True') })) {
     foreach ($license in $msusr.Licenses)
        {
            $output+=New-Object PSObject -Property @{            
            DisplayName       = $msusr.DisplayName                
            UserPrincipalName = $msusr.UserPrincipalName             
            AccountSKUid      = $license.AccountSKUid }
    	}
    }
    
    $output

    If you want to test the online service connection before running the script, you can use try and catch, because if we haven't ran the cmdlet "Connect-MsolService", we will encounter error when run "Get-MSOLUser".

    To use try and catch, please refer to this article:

    http://www.vexasoft.com/blogs/powershell/7255220-powershell-tutorial-try-catch-finally-and-error-handling-in-powershell

    If there is anything else regarding this issue, please feel free to post back.

    Best Regards,

    Anna Wang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com


    Thursday, May 14, 2015 6:27 AM
    Moderator
  • not pretty but seems to do the trick:

    if(-not (Get-Module -name 'MSOnline')) { Import-Module MSOnline } $ErrorActionPreference = 'Stop' Try { $users = (Get-MSOLUser -All) } Catch { Write-Host 'reconnecting...' $username = 'email@address.com' $password = Get-Content 'c:\securestring.txt' | convertto-securestring $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password Connect-MsolService -Credential $cred $users = (Get-MSOLUser -All) } Finally { $ErrorActionPreference = 'Continue' $test = $users | Measure-Object | Select -ExpandProperty 'Count' } if ($test -eq 0) { Write-Host 'FAILED to connect to MSOL' exit } else { Write-Host 'Connected to MSOL' }

    Write-Host $test

    But there is no way to disconnect.  Other people have noticed this:

    community.office365.com/en-us/f/148/t/53335.aspx

    seems the module is somewhat limited at this point.

    Thursday, May 14, 2015 4:29 PM