Query specific users from CSV to get license info

AHAC EL
AHAC EL used Ask the Experts™
on
Hello all,

I'm using the bellow script from: HERE and modify it to instead of using get-msoluser -All to actually grab a list of users from my CSV file and see if what licenses those users have. so far this is my code:

Import-Module MSOnline
  
# CSV output path
$csvpath = 'C:\Scripts\License Audit\results.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..."

Connect-MsolService
  
# Get a list of all licences that exist within the tenant
write-verbose "Geting the licenses available in tenant"
$licensetype = Get-MsolAccountSku | Where-Object {$_.ConsumedUnits -ge 1}
  
# License Object. This forms the property names of the user objects we populate later
$licensehash = @{
    "DisplayName"='';
    "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-Object {$_.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,'')
            }
        }
    }
} 
 
# Loop through all users found in the tenant
$csv = import-csv 'C:\Scripts\License Audit\ImportandCheck.csv'

foreach ($user in $csv.UPN)
{
    Get-MsolUser -UserPrincipalName "$user"
    $displayname = $user.displayname -Replace ",",""
    $licenseduser = [pscustomobject]$licensehash
    $licenseduser.Displayname = $displayname
    $licenseduser.UPN = $user.userprincipalname
    $licenseduser.ImmutableID = $user.immutableid
    $licenseduser.Errors = $user.errors
    $licenseduser.blockcredential = $user.blockcredential
    $licenseduser.IsLicensed = $user.IsLicensed
    
    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
                }
            }
            $UserLicenseConfiguredServices = $userlicense.ServiceStatus | Where-Object{$_.provisioningstatus}
            ForEach($service in $UserLicenseConfiguredServices){
                if($service.ServicePlan.ServiceName -in $sku.keys){
                    $servicename = $sku.($service.ServicePlan.ServiceName)
                }else{
                    $servicename = $service.ServicePlan.ServiceName
                }
                if($servicename -in $licensehash.keys){
                    $licensedUser.$servicename = $service.ProvisioningStatus
                }
            }
         
 
        }
    }
    $licenseduser | Export-csv -path $csvpath -Force -Append -notypeinformation -Encoding UTF8
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
The code looks OK, the only thing missing is setting the $user variable in the loop:

$user = Get-MsolUser -UserPrincipalName "$user"

Open in new window


I wouldnt say I like that format, there are tons of services missing and the output will be a mix between AccountSkuId and "translated" values. You will need to update the hashtable with the missing SKUs, you can find the full list here: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-licensing-product-and-service-plan-reference

Author

Commented:
That solve my issue with the export, simple mistake that drove me insane.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial