Avatar of AHAC EL
AHAC EL
 asked on

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

PowershellMicrosoft 365* licensePower BI

Avatar of undefined
Last Comment
AHAC EL

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vasil Michev (MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
AHAC EL

ASKER
That solve my issue with the export, simple mistake that drove me insane.
Your help has saved me hundreds of hours of internet surfing.
fblack61