Exporting Office 365 Licenses to CSV

Recently, I was asked by a collegue to review a script that is publically available in the technet gallery (https://gallery.technet.microsoft.com/scriptcenter/Office-365-licenses-and-ee8837a0)

The author mentions in the comments “If you find a way to improve this code, please share it.”  As a result, I thought I’d post my object based version.

Regarding the “English” names for the license, you can update the $sku hashtable to change the name of the license in the exported CSV.

As far as overall design, I start with a hashtable of fixed property names ($licenseHash).  This is the template for our object.  Then, I loop through the licenses and services available in the tenant and add these as keys to the hashtable.  Once I have discovered all the licenses and services, I discover all the users in the tenant.  Foreach user, I take a copy of $licenseHash and hard type it to a [pscustomobject] ($licenseduser).  This allows me to treat each of the licenses as properties of the object.  After mapping the licenses to matching properties, I use Export-CSV to append it to the spreadsheet.

Without further ado, here is the script!


<#
.DESCRITION
This script will create a comma separated file with a line per user and the following columns:
Display Name, Domain, UPN, Is Licensed?, all the SKUs in tenant, all the services,
Errors, ImmutableId and BlockCredential.

Based on previous script by Marcus Tarquinio
https://gallery.technet.microsoft.com/scriptcenter/Office-365-licenses-and-ee8837a0

.PARAMETER
After starting the script it will ask for the credentials to connect to Office 365

.OUTPUT
CSV specified by $csvpath

.NOTES
Version: 1.0
Author: Matthew DeBoer
Creation Date: September 27 2017
Purpose/Change: Initial script development
#>

# CSV output path
$csvpath = 'C:\temp\OfficeLicenseCounts.csv'

#Translate SKUs to English
$Sku = @{
"DESKLESSPACK" = "Office 365 (Plan K1)"
"DESKLESSWOFFPACK" = "Office 365 (Plan K2)"
"LITEPACK" = "Office 365 (Plan P1)"
"EXCHANGESTANDARD" = "Office 365 Exchange Online Only"
"STANDARDPACK" = "Enterprise Plan E1"
"STANDARDWOFFPACK" = "Office 365 (Plan E2)"
"ENTERPRISEPACK" = "Enterprise Plan E3"
"ENTERPRISEPACKLRG" = "Enterprise Plan E3"
"ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
"STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"
"STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"
"ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
"ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
"STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"
"STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"
"ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
"ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
"ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
"STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"
"VISIOCLIENT" = "Visio Pro Online"
"POWER_BI_ADDON" = "Office 365 Power BI Addon"
"POWER_BI_INDIVIDUAL_USE" = "Power BI Individual User"
"POWER_BI_STANDALONE" = "Power BI Stand Alone"
"POWER_BI_STANDARD" = "Power-BI standard"
"PROJECTESSENTIALS" = "Project Lite"
"PROJECTCLIENT" = "Project Professional"
"PROJECTONLINE_PLAN_1" = "Project Online"
"PROJECTONLINE_PLAN_2" = "Project Online and PRO"
"ECAL_SERVICES" = "ECAL"
"EMS" = "Enterprise Mobility Suite"
"RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
"MCOMEETADV" = "PSTN conferencing"
"SHAREPOINTSTORAGE" = "SharePoint storage"
"PLANNERSTANDALONE" = "Planner Standalone"
"CRMIUR" = "CMRIUR"
"BI_AZURE_P1" = "Power BI Reporting and Analytics"
"INTUNE_A" = "Windows Intune Plan A"
}

# Connect to Office 365 (need modules installed)
write-verbose "Connecting to Office 365..."
$credential = Get-Credential
Connect-MsolService -Credential $credential

# Get a list of all licences that exist within the tenant
write-verbose "Geting the licenses available in tenant"
$licensetype = Get-MsolAccountSku | Where {$_.ConsumedUnits -ge 1}

# License Object. This forms the property names of the user objects we populate later
$licensehash = @{
"DisplayName"='';
"Domain"='';
"UPN"='';
"IsLicensed"='';
"Errors"='';
"ImmutableID"='';
"BlockCredential"='';
}

#Get all account SKUs in tenant
$AccountSkus = Get-MsolAccountSku

#Loop through each license in tenant and get the sku
foreach ($license in $licensetype)
{
if($license.SkuPartNumber -notin $licensehash.keys){
if($license.SkuPartNumber -in $sku.keys){
$licensename = $sku.($license.SkuPartNumber)
}else{
$licensename = $license.skupartnumber
}
$licensehash.Add($licensename,'')
}

# Get a list of all the services in the tenant
$services = ($AccountSkus | where {$_.AccountSkuId -eq $license.AccountSkuId}).ServiceStatus.serviceplan.servicename
ForEach($service in $services){
if($service -in $sku.keys){
$servicename = $sku.($service)
}else{
$servicename = $service
}
if($servicename -notin $licensehash.keys){
$licensehash.add($servicename,'')
}
}
}

# Get a list of all the users in the tenant
write-verbose "Getting all users in the Office 365 tenant..."
$users = Get-MsolUser -all

# Loop through all users found in the tenant
foreach ($user in $users)
{
$displayname = $user.displayname -Replace ",",""
$licenseduser = [pscustomobject]$licensehash
$licenseduser.Displayname = $displayname
$licenseduser.Domain = $user.UserPrincipalName.Split("@")[1]
$licenseduser.UPN = $user.userprincipalname
$licenseduser.ImmutableID = $user.immutableid
$licenseduser.Errors = $user.errors
$licenseduser.blockcredential = $user.blockcredential
if ($user.isLicensed)
{
ForEach($userlicense in $user.licenses){
if($userlicense.AccountSkuID.ToString() -in $licensetype.AccountSKUid){
$usersku = (($userlicense.accountskuid.tostring()) -split ':')[1]
if($usersku -in $sku.keys){
$usersku = $sku.($usersku)
}
if($usersku -in $licensehash.keys){
$licensedUser.$usersku = $true
}
}
}
}
$licenseduser | Export-csv -path $csvpath -Force -Append -notypeinformation
}
write-host ("Script Completed. Results available in " + $csvpath)

 

 

Advertisements

Random Clients fail to download content (0x80070003)



******Update July 27 2017:  I have confirmed that the below issue is addressed via: KB4035759.  Thanks to all who helped! ******



This posts outlines an issue that I am seeing in Configuration Manager starting in 1702. I currently have a call open with MS and will update the post once the issue is resolved. I am currently aware of 2 environments with the issue and am posting this in case this is also an issue for others. If you have this issue in your environment, please shoot me a message with any cases you have open so Microsoft can see the commonalities in our environments.

Steps to Reproduce:
1. Issue occurs on newly built computers (this may be for the simple fact as they are installing the most software). We have reproduced the issue on computers built with our current gold image, last December’s gold image as well as using a new task sequence and VLSC ISO. Failure rates are around 20% so a minimum of 5 computers should be built. For our tests we built 15 computers total all in the same subnet.
2. Mass add computers to software deployment collections

Continue reading

Silent Scripted PNP Driver Installation

Occasionally, you may find the need to push a new driver to computers.  Perhaps a driver is causing BSOD issues or whatever the reason.  Since DotNet does not have a direct way to do this, you are usually left with depending on the driver publisher to include an silent installation method.  In reality this rarely happens.  You definitely don’t want to run around and manually install the drivers, and tools like Configuration Manager don’t have support for post OS deployment of drivers.

Continue reading

Getting Physical Disk and Logical Disk Sizes

There are several use cases where it may be necessary to get the Physical Disk and Logical Disk (partition) sizes and free space. This information although easily accessible in WMI is stored in a format that is difficult to use.

These two functions should help you with that

Function Get-FreeSpace{
    Param(
        [string]$computer = 'localhost',
        [string]$disk = 'C:'
    )
    $LogicalDisks = gwmi -computername $computer -query "select * from win32_logicaldisk where deviceid='${disk}'"
    ForEach($LogicalDisk in $LogicalDisks){ 
        [pscustomobject]@{'Caption'=$LogicalDisk.caption;'Size (GB)'=$('{0:N2}' -f [double]($LogicalDisk.size/1gb));'Free (GB)'=$('{0:N2}' -f [double]($LogicalDisk.FreeSpace/1gb))}
    }
}

Get-FreeSpace -computer localhost -disk 'c:'
#*****************************
# Output
#*****************************
# Caption Free (GB) Size (GB)
# ------- --------- ---------
# C:      19.38     119.24   


Function Get-PhysicalDiskSize{
    Param(
        [string]$computer = 'localhost'
    )
    $Disks = gwmi -ComputerName $computer -query "Select Index,Size,MediaType From win32_DiskDrive"
    ForEach($Disk in $Disks){
        [pscustomobject]@{"Index"=$Disk.index;"Size (GB)"=([Double]([MATH]::Round(([double]$Disk.Size / 1073741824),2)));"MediaType"=$disk.mediatype}
    }
}

Get-PhysicalDiskSize -computer 'localhost'
#*****************************
# Output
#*****************************
#Index Size (GB) MediaType            
#----- --------- ---------            
#    1    465.76 Fixed hard disk media
#    0    119.24 Fixed hard disk media

ConfigMgr Client Fails to Install: Unable to Compile UpdatesAgent.mof

We’ve had a couple of computers in the past being unable to re-install the Configuration Manager client due to the error:
“Unable to compile UpdatesAgent.mof”

This error can have a couple of different causes.

As such, here are a couple of steps you can try:

1. Reinstall the Windows Update agent. https://support.microsoft.com/en-ca/kb/949104
2. Uninstall any existing ConfigMgr client, stop the ccmsetup service and delete c:\windows\ccm, c:\windows\ccmsetup and c:\windows\ccmcache folders
3. Run the following commands to delete the ConfigMgr namespaces completely from WMI:

Gwmi –query “Select * from __Namespace Where Name=’sms’” –NameSpace “root\cimv2” | Remove-WmiObject
Gwmi –query “Select * from __Namespace Where Name=’ccm’” –NameSpace “root” | Remove-WmiObject
Gwmi –query “Select * from __Namespace Where Name=’smsdm’” –NameSpace “root” | Remove-WmiObject

Since #3 is quite drastic, you will want to try steps 1 and 2 first before 3. However if attempting step 3, you will want to complete both steps 2 and 3 together. After this, the ConfigMgr client should successfully install.

Hopefully this helps!