Query specific users from CSV to get license info

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

AHAC ELAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vasil Michev (MVP)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AHAC ELAuthor Commented:
That solve my issue with the export, simple mistake that drove me insane.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.