Skip to content
TheChatArchiveThe Chat Archive
o1-preview-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Do you see any security issues with the following code? Please respond with a bullet…

  1. User

    Do you see any security issues with the following code? Please respond with a bullet list, if there are any issues:

    ReportUserAssignedLicenses-MgGraph.PS1

    Create a report of licenses assigned to Entra ID user accounts using the Microsoft Graph PowerShell SDK cmdlets

    Github link: https://github.com/12Knocksinna/Office365itpros/blob/master/ReportUserAssignedLicenses-MgGraph.PS1

    See https://practical365.com/create-licensing-report-microsoft365-tenant/ for an article describing how to run the report and

    https://practical365.com/report-user-license-costs/ for information about how to include licensing cost information

    in the output

    V1.1 27-Sep-2022 Add sign in data for users and calculate how long it's been since they signed in and used a license.

    V1.2 23-Nov-2022 Added SKU usage summary to HTML report

    V1.3 29-Sep-2023 Added support for group-based licensing

    V1.4 13-Oct-2023 Fixed some bugs

    V1.5 26-Jan-2024 Added license pricing computation

    V1.5 8-Feb-2024 Added cost analysis for departments and countries

    V1.6 12-Feb-2024 Added info to report when license costs can't be attributed to countries or departments because of missing user account properties

    V1.7 7-Mar-2024 Added company name to the set of properties output by report

    V1.8 17-Mar-2024 Tidy up

    V1.9 1-June-2024 Add cost analysis for inactive and disabled accounts

    V1.91 26-June-2024 Fix bug with color formatting of inactive account column

    V1.92 04-Jul-2024 Add optional cost center license analysis based on cost center details stored in a

    custom attribute for Exchange mailboxes that's synchronized to Entra ID. The custom attribute

    to use is held in CostCenterAttribute.IfthisvariableisundefinedorCostCenterAttribute. If this variable is undefined or null, the report

    script ignores cost centers

    V1.93 05-Sep-2024 Add analysis of costs by company name

    V1.94 08-Sep-2024 Remove licenses assigned for expired subscriptions from the report

    Function Get-LicenseCosts {

    Function to calculate the annual costs of the licenses assigned to a user account

    [cmdletbinding()] Param( [array]Licenses)[int]Licenses ) [int]Costs = 0 ForEach (LicenseinLicense in Licenses) { Try { [string]LicenseCost=LicenseCost = PricingHashTable[License] # Convert monthly cost to cents (because some licenses cost sums like 16.40) [float]LicenseCostCents = [float]LicenseCost∗100If(LicenseCost * 100 If (LicenseCostCents -gt 0) { # Compute annual cost for the license [float]AnnualCost=AnnualCost = LicenseCostCents * 12 # Add to the cumulative license costs Costs=Costs = Costs + (AnnualCost) # Write-Host ("License {0} Cost {1} running total {2}" -f License, LicenseCost,LicenseCost, Costs) } } Catch { Write-Host ("Error finding license {0} in pricing table - please check" -f $License) } }

    Return

    Return ($Costs / 100) }

    [datetime]RunDate=Get−Date[string]RunDate = Get-Date [string]ReportRunDate = Get-Date (RunDate)−format′dd−MMM−yyyyHH:mm′RunDate) -format 'dd-MMM-yyyy HH:mm' Version = "1.95"

    Default currency - can be overwritten by a value read into the $ImportSkus array

    [string]$Currency = "USD"

    Connect to the Graph, specifing the tenant and profile to use - Add your tenant identifier here

    Connect-MgGraph -Scope "Directory.AccessAsUser.All, Directory.Read.All, AuditLog.Read.All" -NoWelcome

    <# Alternative: Use Application ID and Secured Password for authentication (you could also pass a certificate thumbprint) ApplicationId="<applicationId>"ApplicationId = "<applicationId>" SecuredPassword = "" $tenantID = ""

    SecuredPasswordPassword=ConvertTo−SecureString−StringSecuredPasswordPassword = ConvertTo-SecureString -String SecuredPassword -AsPlainText -Force ClientSecretCredential=New−Object−TypeNameSystem.Management.Automation.PSCredential−ArgumentListClientSecretCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ApplicationId, SecuredPasswordPasswordConnect−MgGraph−TenantIdSecuredPasswordPassword Connect-MgGraph -TenantId tenantID -ClientSecretCredential $ClientSecretCredential #>

    This step depends on the availability of some CSV files generated to hold information about the product licenses used in the tenant and

    the service plans in those licenses. See https://github.com/12Knocksinna/Office365itpros/blob/master/CreateCSVFilesForSKUsAndServicePlans.PS1

    for code to generate the CSVs. After the files are created, you need to edit them to add the display names for the SKUs and plans.

    Build Hash of Skus for lookup so that we report user-friendly display names - you need to create these CSV files from SKU and service plan

    data in your tenant.

    SkuDataPath="C:\temp\SkuDataComplete.csv"SkuDataPath = "C:\temp\SkuDataComplete.csv" ServicePlanPath = "C:\temp\ServicePlanDataComplete.csv" $UnlicensedAccounts = 0

    Define this variable if you want to do cost center reporting based on a cost center stored in one of the

    15 Exchange Online custom attributes synchronized to Entra ID. Use the Entra ID attribute (like extensionAttribute6)

    name not the Exchange Online attribute name (CustomAttribute6) Set the variable to $null or don't define it at all

    to ignore cost centers

    #$CostCenterAttribute = "extensionAttribute6"

    If ((Test-Path skuDataPath)−eqskuDataPath) -eq False) { Write-Host ("Can't find the product data file ({0}). Exiting..." -f skuDataPath) ; break } If ((Test-Path servicePlanPath) -eq False) { Write-Host ("Can't find the serivice plan data file ({0}). Exiting..." -f servicePlanPath) ; break }

    ImportSkus=Import−CSVImportSkus = Import-CSV skuDataPath ImportServicePlans=Import−CSVImportServicePlans = Import-CSV servicePlanPath SkuHashTable=@ForEach(SkuHashTable = @{} ForEach (Line in ImportSkus) { SkuHashTable.Add([string]Line.SkuId,[string]Line.SkuId, [string]Line.DisplayName) } ServicePlanHashTable=@ForEach(ServicePlanHashTable = @{} ForEach (Line2 in ImportServicePlans) { ServicePlanHashTable.Add([string]Line2.ServicePlanId,[string]Line2.ServicePlanId, [string]Line2.ServicePlanDisplayName) }

    If pricing information is in the $ImportSkus array, we can add the information to the report. We prepare to do this

    by setting the PricingInfoAvailabletoPricingInfoAvailable to true and populating the $PricingHashTable

    PricingInfoAvailable=PricingInfoAvailable = false

    If (ImportSkus[0].Price) { PricingInfoAvailable = truetrue Global:PricingHashTable = @{} ForEach (LineinLine in ImportSkus) { PricingHashTable.Add([string]PricingHashTable.Add([string]Line.SkuId, [string]Line.Price) } If (ImportSkus[0].Currency) { [string]Currency=(Currency = (ImportSkus[0].Currency) } }

    Find tenant accounts - but filtered so that we only fetch those with licenses

    Write-Host "Finding licensed user accounts..." [Array]Users=Get−MgUser−Filter"assignedLicenses/‘Users = Get-MgUser -Filter "assignedLicenses/`count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All -PageSize 999 -Property id, displayName, userPrincipalName, country, department, assignedlicenses, OnPremisesExtensionAttributes, licenseAssignmentStates, createdDateTime, jobTitle, signInActivity, companyName, accountenabled | Sort-Object DisplayName

    If (!(Users)) { Write-Host "No licensed user accounts found - exiting"; break } Else { Write-Host ("{0} Licensed user accounts found - now processing their license data..." -f Users.Count) }

    These are the properties used to create analyses for.

    [array]Departments=Departments = Users.Department | Sort-Object -Unique [array]Countries=Countries = Users.Country | Sort-Object -Unique [array]CostCenters=CostCenters = Users.OnPremisesExtensionAttributes.(CostCenterAttribute)∣Sort−Object−Unique[array]CostCenterAttribute) | Sort-Object -Unique [array]Companies = $Users.CompanyName | Sort-Object -Unique

    Control whether to use the detailed license report information to generate a line-by-line

    report of license assignments to users. This report is useful to detect duplicate licenses and

    to help allocate license costs to operating units within an organization. Set the value to false

    if you don't want to generate the detailed report.

    DetailedCompanyAnalyis=DetailedCompanyAnalyis = true

    $OrgName = (Get-MgOrganization).DisplayName

    Current subscriptions in the tenant. We use this table to remove expired licenses from the calculation

    [array]CurrentSubscriptions=Get−MgSubscribedSkuCurrentSubscriptions = Get-MgSubscribedSku CurrentSubscriptionsHash = @{} ForEach (SinS in CurrentSubscriptions) { CurrentSubscriptionsHash.Add(CurrentSubscriptionsHash.Add(S.SkuId, $S.SkuPartNumber) }

    DuplicateSKUsAccounts=0;DuplicateSKUsAccounts = 0; DuplicateSKULicenses = 0; LicenseErrorCount=0LicenseErrorCount = 0 Report = [System.Collections.Generic.List[Object]]::new() DetailedLicenseReport=[System.Collections.Generic.List[Object]]::new()DetailedLicenseReport = [System.Collections.Generic.List[Object]]::new() i = 0 [float]TotalUserLicenseCosts=0[float]TotalUserLicenseCosts = 0 [float]TotalBoughtLicenseCosts = 0

    ForEach (UserinUser in Users) { UnusedAccountWarning="OK";UnusedAccountWarning = "OK"; i++; UserCosts=0UserCosts = 0 ErrorMsg = ""; LastLicenseChange=""Write−Host("Processingaccount01/2"−fLastLicenseChange = "" Write-Host ("Processing account {0} {1}/{2}" -f User.UserPrincipalName, i,i, Users.Count) If ([string]::IsNullOrWhiteSpace(User.licenseAssignmentStates)−eqUser.licenseAssignmentStates) -eq False) { # Only process account if it has some licenses [array]LicenseInfo=LicenseInfo = Null; [array]DisabledPlans=DisabledPlans = Null; # Find out if any of the user's licenses are assigned via group-based licensing [array]GroupAssignments=GroupAssignments = User.licenseAssignmentStates | Where-Object { $null -ne $_.AssignedByGroup -and $_.State -eq "Active" } # Find out if any of the user's licenses are assigned via group-based licensing have an error [array]$GroupErrorAssignments = $User.licenseAssignmentStates | Where-Object { Null−neNull -ne _.AssignedByGroup -and _.State -eq "Error" } [array]GroupLicensing = Null # Find out when the last license change was made If ([string]::IsNullOrWhiteSpace(User.licenseAssignmentStates.lastupdateddatetime) -eq False) { LastLicenseChange = Get-Date((user.LicenseAssignmentStates.lastupdateddatetime | Measure-Object -Maximum).Maximum) -format g } # Figure out the details of group-based licensing assignments if any exist ForEach (G in GroupAssignments) { GroupName = (Get-MgGroup -GroupId G.AssignedByGroup).DisplayNameG.AssignedByGroup).DisplayName GroupProductName = SkuHashTable[SkuHashTable[G.SkuId] GroupLicensing+=("0assignedfrom1"−fGroupLicensing += ("{0} assigned from {1}" -f GroupProductName, GroupName) } ForEach (G in GroupErrorAssignments) { GroupName = (Get-MgGroup -GroupId G.AssignedByGroup).DisplayNameG.AssignedByGroup).DisplayName GroupProductName = SkuHashTable[SkuHashTable[G.SkuId] ErrorMsg=ErrorMsg = G.Error LicenseErrorCount++LicenseErrorCount++ GroupLicensing += ("{0} assigned from {1} BUT ERROR {2}!" -f GroupProductName,GroupProductName, GroupName, ErrorMsg) } GroupLicensingAssignments = $GroupLicensing -Join ", "

    #  Find out if any of the user's licenses are assigned via direct licensing
    [array]$DirectAssignments = $User.licenseAssignmentStates | `
      Where-Object { $null -eq $_.AssignedByGroup -and $_.State -eq "Active" }
    
    # Figure out details of direct assigned licenses
    [array]$UserLicenses = $User.AssignedLicenses
    ForEach ($License in $DirectAssignments) {
      If ($SkuHashTable.ContainsKey($License.SkuId) -eq $True) {
        # We found a match in the SKU hash table
        $LicenseInfo += $SkuHashTable.Item($License.SkuId) 
      } Else {
        # Nothing found in the SKU hash table, so output the SkuID
        $LicenseInfo += $License.SkuId
      }
    }
    
    # Report any disabled service plans in licenses
    $License = $UserLicenses | Where-Object { -not [string]::IsNullOrWhiteSpace($_.DisabledPlans) }
    # Check if disabled service plans in a license
    ForEach ($DisabledPlan in $License.DisabledPlans) {
      # Try and find what service plan is disabled
      If ($ServicePlanHashTable.ContainsKey($DisabledPlan) -eq $True) {
        # We found a match in the Service Plans hash table
        $DisabledPlans += $ServicePlanHashTable.Item($DisabledPlan) 
      }
      Else {
        # Nothing doing, so output the Service Plan ID
        $DisabledPlans += $DisabledPlan 
      }
    } # End ForEach disabled plans
    
    # Detect if any duplicate licenses are assigned (direct and group-based)
    # Build a list of assigned SKUs
    $SkuUserReport = [System.Collections.Generic.List[Object]]::new()
    ForEach ($S in $DirectAssignments) {
      If ($CurrentSubscriptionsHash[$S.SkuId]) {
        $ReportLine = [PSCustomObject][Ordered]@{ 
          User        = $User.Id
          Name        = $User.DisplayName 
          Sku         = $S.SkuId
          Method      = "Direct"  
          Country     = $User.Country
          Department  = $User.Department
          Company     = $User.CompanyName
        }
        $SkuUserReport.Add($ReportLine)
      }
    }
    ForEach ($S in $GroupAssignments) {
      If ($CurrentSubscriptionsHash[$S.SkuId]) {
        $ReportLine = [PSCustomObject][Ordered]@{ 
          User        = $User.Id
          Name        = $User.DisplayName
          Sku         = $S.SkuId
          Method      = "Group" 
          Country     = $User.Country
          Department  = $User.Department
          Company     = $User.CompanyName
        }
        $SkuUserReport.Add($ReportLine)
      }
    }
    
    # Check if any duplicates exist
    [array]$DuplicateSkus = $SkuUserReport | Group-Object Sku | `
      Where-Object { $_.Count -gt 1 } | Select-Object -ExpandProperty Name
    
    # If duplicates exist, resolve their SKU IDs into Product names and generate a warning for the report
    [string]$DuplicateWarningReport = "N/A"
    If ($DuplicateSkus) {
      [array]$DuplicateSkuNames = $Null
      $DuplicateSKUsAccounts++
      $DuplicateSKULicenses = $DuplicateSKULicenses + $DuplicateSKUs.Count
      ForEach ($DS in $DuplicateSkus) {
        $SkuName = $SkuHashTable[$DS]
        $DuplicateSkuNames += $SkuName
      }
      $DuplicateWarningReport = ("Warning: Duplicate licenses detected for: {0}" -f ($DuplicateSkuNames -join ", "))
    }
    

    } Else { $UnlicensedAccounts++ }

    Figure out the last time the account signed in. This is important for detecting unused accounts

    LastSignIn=LastSignIn = User.SignInActivity.LastSignInDateTime LastNonInteractiveSignIn=LastNonInteractiveSignIn = User.SignInActivity.LastNonInteractiveSignInDateTime

    If (-not LastSignIn−and−notLastSignIn -and -not LastNonInteractiveSignIn) { DaysSinceLastSignIn="Unknown"DaysSinceLastSignIn = "Unknown" UnusedAccountWarning = ("Unknown last sign-in for account") LastAccess = "Unknown" } Else { # Get the newest date, if both dates contain values If (LastSignIn -and LastNonInteractiveSignIn) { If (LastSignIn -gt LastNonInteractiveSignIn) { CompareDate = LastSignIn } Else { CompareDate = LastNonInteractiveSignIn } } Elseif (LastSignIn) { # Only LastSignInhasavalueLastSignIn has a value CompareDate = LastSignIn } Else { # Only LastNonInteractiveSignIn has a value CompareDate=CompareDate = LastNonInteractiveSignIn }

    $DaysSinceLastSignIn = ($RunDate - $CompareDate).Days
    $LastAccess = Get-Date($CompareDate) -format g
    If ($DaysSinceLastSignIn -gt 60) { 
        $UnusedAccountWarning = ("Account unused for {0} days - check!" -f $DaysSinceLastSignIn) 
    }
    

    }

    AccountCreatedDate=AccountCreatedDate = null If (User.CreatedDateTime) { AccountCreatedDate = Get-Date($User.CreatedDateTime) -format 'dd-MMM-yyyy HH:mm' }

    If cost center reporting is enabled, extract the cost center for the user

    [string]CostCenter=CostCenter = Null If (CostCenterAttribute) { CostCenter = User.OnPremisesExtensionAttributes.(User.OnPremisesExtensionAttributes.(CostCenterAttribute) }

    Report information

    [string]DisabledPlans=DisabledPlans = DisabledPlans -join ", " [string]LicenseInfo=LicenseInfo = LicenseInfo -join (", ")

    If (User.AccountEnabled−eqUser.AccountEnabled -eq False) { AccountStatus = "Disabled" } Else { AccountStatus = "Enabled" }

    If (PricingInfoAvailable) { # Output report line with pricing info [float]UserCosts = Get-LicenseCosts -Licenses UserLicenses.SkuIdUserLicenses.SkuId TotalUserLicenseCosts = TotalUserLicenseCosts+TotalUserLicenseCosts + UserCosts ReportLine = [PSCustomObject][Ordered]@{ User = User.DisplayName UPN = User.UserPrincipalNameCountry=User.UserPrincipalName Country = User.Country Department = User.DepartmentTitle=User.Department Title = User.JobTitle Company = User.companyName"Directassignedlicenses"=User.companyName "Direct assigned licenses" = LicenseInfo "Disabled Plans" = DisabledPlans.Trim()"Groupbasedlicenses"=DisabledPlans.Trim() "Group based licenses" = GroupLicensingAssignments "Annual License Costs" = ("{0} {1}" -f Currency,(Currency, (UserCosts.toString('F2'))) "Last license change" = LastLicenseChange"Accountcreated"=LastLicenseChange "Account created" = AccountCreatedDate "Last Signin" = LastAccess"Dayssincelastsignin"=LastAccess "Days since last signin" = DaysSinceLastSignIn "Duplicates detected" = DuplicateWarningReportStatus=DuplicateWarningReport Status = UnusedAccountWarning "Account status" = AccountStatusUserCosts=AccountStatus UserCosts = UserCosts
    'Cost Center' = CostCenter } } Else { # No pricing information ReportLine = [PSCustomObject][Ordered]@{
    User = User.DisplayNameUPN=User.DisplayName UPN = User.UserPrincipalName Country = User.CountryDepartment=User.Country Department = User.Department Title = User.JobTitleCompany=User.JobTitle Company = User.companyName "Direct assigned licenses" = LicenseInfo"DisabledPlans"=LicenseInfo "Disabled Plans" = DisabledPlans.Trim() "Group based licenses" = GroupLicensingAssignments"Lastlicensechange"=GroupLicensingAssignments "Last license change" = LastLicenseChange "Account created" = AccountCreatedDate"LastSignin"=AccountCreatedDate "Last Signin" = LastAccess "Days since last signin" = DaysSinceLastSignIn"Duplicatesdetected"=DaysSinceLastSignIn "Duplicates detected" = DuplicateWarningReport Status = UnusedAccountWarning"Accountstatus"=UnusedAccountWarning "Account status" = AccountStatus } }
    Report.Add(Report.Add(ReportLine)

    Populate the detailed license assignment report

    SkuUserReport=SkuUserReport = SkuUserReport | Sort-Object Sku -Unique ForEach (IteminItem in SkuUserReport) { SkuReportLine = [PSCustomObject][Ordered]@{ User = Item.User Name = Item.nameSku=Item.name Sku = Item.Sku SkuName = (SkuHashTable[SkuHashTable[Item.Sku]) Method = Item.MethodCountry=Item.Method Country = Item.Country Department = Item.DepartmentCompany=Item.Department Company = Item.Company
    } DetailedLicenseReport.Add(DetailedLicenseReport.Add(SkuReportLine) } } # End ForEach Users

    UnderusedAccounts=UnderusedAccounts = Report | Where-Object { _.Status -ne "OK" } PercentUnderusedAccounts = (UnderUsedAccounts.Count/UnderUsedAccounts.Count / Report.Count).toString("P")

    This code grabs the SKU summary for the tenant and uses the data to create a SKU summary usage segment for the HTML report

    SkuReport=[System.Collections.Generic.List[Object]]::new()[array]SkuReport = [System.Collections.Generic.List[Object]]::new() [array]SkuSummary = Get-MgSubscribedSku | Select-Object SkuId, ConsumedUnits, PrepaidUnits SkuSummary=SkuSummary = SkuSummary | Where-Object { _.ConsumedUnits -ne 0 } ForEach (S in SkuSummary) { SKUCost = Get-LicenseCosts -Licenses S.SkuIdS.SkuId SkuDisplayName = SkuHashtable[SkuHashtable[S.SkuId] If (S.PrepaidUnits.Enabled−leS.PrepaidUnits.Enabled -le S.ConsumedUnits ) { BoughtUnits=BoughtUnits = S.ConsumedUnits } Else { BoughtUnits=BoughtUnits = S.PrepaidUnits.Enabled } If (PricingInfoAvailable) { SKUTotalCost = (SKUCost∗SKUCost * BoughtUnits) SkuReportLine = [PSCustomObject][Ordered]@{ "SKU Id" = S.SkuId "SKU Name" = SkuDisplayName"UnitsUsed"=SkuDisplayName "Units Used" = S.ConsumedUnits "Units Purchased" = BoughtUnits"Annuallicensecosts"=BoughtUnits "Annual license costs" = SKUTotalCost "Annual licensing cost" = ("{0} {1}" -f Currency,(′0:N2′−fCurrency, ('{0:N2}' -f SKUTotalCost)) } } Else { SkuReportLine = [PSCustomObject][Ordered]@{ "SKU Id" = S.SkuId "SKU Name" = SkuDisplayName"UnitsUsed"=SkuDisplayName "Units Used" = S.ConsumedUnits "Units Purchased" = BoughtUnits } } SkuReport.Add(SkuReportLine)SkuReportLine) TotalBoughtLicenseCosts = TotalBoughtLicenseCosts+TotalBoughtLicenseCosts + SKUTotalCost }

    If (PricingInfoAvailable) { AverageCostPerUser = (TotalUserLicenseCosts/TotalUserLicenseCosts / Users.Count) AverageCostPerUserOutput=("01"−fAverageCostPerUserOutput = ("{0} {1}" -f Currency, ('{0:N2}' -f AverageCostPerUser))AverageCostPerUser)) TotalUserLicenseCostsOutput = ("{0} {1}" -f Currency,(′0:N2′−fCurrency, ('{0:N2}' -f TotalUserLicenseCosts)) TotalBoughtLicenseCostsOutput=("01"−fTotalBoughtLicenseCostsOutput = ("{0} {1}" -f Currency, ('{0:N2}' -f TotalBoughtLicenseCosts))TotalBoughtLicenseCosts)) PercentBoughtLicensesUsed = (TotalUserLicenseCosts/TotalUserLicenseCosts / TotalBoughtLicenseCosts).toString('P') SkuReport=SkuReport = SkuReport | Sort-Object "Annual license costs" -Descending } Else { SkuReport=SkuReport = SkuReport | Sort-Object "SKU Name" -Descending }

    Generate the department analysis

    DepartmentReport=[System.Collections.Generic.List[Object]]::new()ForEach(DepartmentReport = [System.Collections.Generic.List[Object]]::new() ForEach (Department in Departments) { [array]DepartmentRecords = Report | Where-Object {_.Department -eq Department} DepartmentReportLine = [PSCustomObject][Ordered]@{ Department = DepartmentAccounts=Department Accounts = DepartmentRecords.count Costs = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (DepartmentRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (DepartmentRecords | Measure-Object UserCosts -Average).Average)) } DepartmentReport.Add(DepartmentReport.Add(DepartmentReportLine) } DepartmentHTML=DepartmentHTML = DepartmentReport | ConvertTo-HTML -Fragment

    Anyone without a department?

    [array]NoDepartment=NoDepartment = Report | Where-Object { null−eqnull -eq _.Department } If (NoDepartment) { NoDepartmentCosts = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (NoDepartment | Measure-Object UserCosts -Sum).Sum)) } Else { $NoDepartmentCosts = "Zero" }

    Generate the country analysis

    CountryReport=[System.Collections.Generic.List[Object]]::new()ForEach(CountryReport = [System.Collections.Generic.List[Object]]::new() ForEach (Country in Countries) { [array]CountryRecords = Report | Where-Object {_.Country -eq Country} CountryReportLine = [PSCustomObject][Ordered]@{ Country = CountryAccounts=Country Accounts = CountryRecords.count Costs = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (CountryRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (CountryRecords | Measure-Object UserCosts -Average).Average)) } CountryReport.Add(CountryReport.Add(CountryReportLine) } CountryHTML=CountryHTML = CountryReport | ConvertTo-HTML -Fragment

    Anyone without a country?

    [array]NoCountry=NoCountry = Report | Where-Object { null−eqnull -eq _.Country } If (NoCountry) { NoCountryCosts = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (NoCountry | Measure-Object UserCosts -Sum).Sum)) } Else { $NoCountryCosts = "Zero" }

    Generate cost center analysis

    If (PricingInfoAvailable−andPricingInfoAvailable -and null -ne CostCenterAttribute) { CostCenterReport = [System.Collections.Generic.List[Object]]::new() ForEach (CostCenterinCostCenter in CostCenters) { [array]CostCenterRecords=CostCenterRecords = Report | Where-Object {.′CostCenter′−eq_.'Cost Center' -eq CostCenter} CostCenterReportLine = [PSCustomObject][Ordered]@{ 'Cost Center' = CostCenter Accounts = CostCenterRecords.countCosts=("01"−fCostCenterRecords.count Costs = ("{0} {1}" -f Currency, ('{0:N2}' -f (CostCenterRecords∣Measure−ObjectUserCosts−Sum).Sum))AverageCost=("01"−fCostCenterRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f Currency, ('{0:N2}' -f (CostCenterRecords | Measure-Object UserCosts -Average).Average)) } CostCenterReport.Add(CostCenterReportLine) } CostCenterHTML = CostCenterReport | ConvertTo-HTML -Fragment # Anyone without a cost center? [array]NoCostCenter = Report | Where-Object { null -eq _.'Cost Center' } If (NoCostCenter) { NoCostCenterCosts=("01"−fNoCostCenterCosts = ("{0} {1}" -f Currency, ('{0:N2}' -f (NoCostCenter | Measure-Object UserCosts -Sum).Sum)) } Else { NoCostCenterCosts = "Zero" } }

    Generate the company analysis

    CompanyReport=[System.Collections.Generic.List[Object]]::new()ForEach(CompanyReport = [System.Collections.Generic.List[Object]]::new() ForEach (Company in Companies) { [array]CompanyRecords = Report | Where-Object {_.Company -eq Company} CompanyReportLine = [PSCustomObject][Ordered]@{ Company = CompanyAccounts=Company Accounts = CompanyRecords.count Costs = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (CompanyRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (CompanyRecords | Measure-Object UserCosts -Average).Average)) } CompanyReport.Add(CompanyReport.Add(CompanyReportLine) } CompanyHTML=CompanyHTML = CompanyReport | ConvertTo-HTML -Fragment

    Anyone without an assigned company?

    [array]NoCompany=NoCompany = Report | Where-Object { null−eqnull -eq _.Company } If (NoCompany) { NoCompanyCosts = ("{0} {1}" -f Currency,(′0:N2′−f(Currency, ('{0:N2}' -f (NoCompany | Measure-Object UserCosts -Sum).Sum)) } Else { $NoCompanyCosts = "Zero" }

    CompanyAnalysisHTML=CompanyAnalysisHTML = null

    Detailed company analysis - example of breaking down costs by SKU for each company

    ForEach (CompanyinCompany in Companies) { [array]CompanyAssignments=CompanyAssignments = DetailedLicenseReport | Where-Object {.Company−eq_.Company -eq Company} CompanyAnalysisHTML=CompanyAnalysisHTML = CompanyAnalysisHTML + ("Company Analysis: Product Licenses for {0}" -f Company)[array]Company) [array]Skus = $CompanyAssignments.Sku | Sort-Object -Unique

    ForEach ($Sku in $Skus) {
      [float]$AnnualCost = 0; [float]$AnnualCostLicense = 0; $AnnualCostLicenseFormatted = $null
      $SkuHTMLFooter = $null; [float]$AnnualCost = $null; $AnnualCostLicense = $null
      $SkuHeader = ("<h3>{0}</h3>" -f $SkuHashTable[$Sku])
      $AssignedSkus = $CompanyAssignments | Where-Object {$_.Sku -eq $Sku} | Select-Object Sku, Name, SkuName, Country, Department, Company 
      $LicenseCostSKU = $PricingHashTable[$Sku]
      [float]$LicenseCostCents = [float]$LicenseCostSKU * 100
      If ($LicenseCostCents -gt 0) {
        # Compute annual cost for the license
        [float]$AnnualCost = $LicenseCostCents * 12
        # Compute cost for this SKU assigned to this company
        $AnnualCostLicense = ($AnnualCost * $AssignedSkus.count)/100
        $AnnualCostLicenseFormatted = ("{0} {1}" -f $Currency, ('{0:N2}' -f $AnnualCostLicense))
      } Else {
        $AnnualCostLicenseFormatted = ("{0} {1}" -f $Currency, ('{0:N2}' -f 0))
      }
      # Report the set of people assigned this SKU      
      $AssignedSkusHTML = $AssignedSkus | ConvertTo-HTML -fragment
      $CompanySKUDetailHTML = $SkuHeader + "<p>" + $AssignedSkusHTML + "<p>"
      $SkuHTMLFooter = ("<p>Annual cost for {0} license(s): {1}</p>" -f $AssignedSKUs.count, $AnnualCostLicenseFormatted)
      $CompanyAnalysisHTML = $CompanyAnalysisHTML + "</p>" + $CompanySKUDetailHTML + $SkuHTMLFooter
    }
    $CompanyAnalysisHTML =  "<p>" + $CompanyAnalysisHTML + "</p>" + $CompanyHTMLFooter
    

    }

    Inactive user accounts - these are accounts that have never signed in or whose last sign-in was more than 60 days ago InactiveUserAccounts=InactiveUserAccounts = Report | Where-Object {(."Dayssincelastsignin"−ge60)−or(_."Days since last signin" -ge 60) -or (_.'Days since last signin' -eq "Unknown")}

    InactiveUserAccountsCost=("01"−fInactiveUserAccountsCost = ("{0} {1}" -f Currency, ('{0:N2}' -f (InactiveUserAccounts | Measure-Object UserCosts -Sum).Sum)) # Disabled user accounts DisabledUserAccounts = Report | Where-Object { _."Account status" -eq "disabled" } DisabledUserAccountsCost=("01"−fDisabledUserAccountsCost = ("{0} {1}" -f Currency, ('{0:N2}' -f ($DisabledUserAccounts | Measure-Object UserCosts -Sum).Sum))

    Cost spans for license comparison

    LowCost=LowCost = AverageCostPerUser * 0.8 MediumCost=MediumCost = AverageCostPerUser

    Create the HTML report

    HtmlHead = "<html> <style> BODY{font-family: Arial; font-size: 8pt;} H1{font-size: 22px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;} H2{font-size: 18px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;} H3{font-size: 16px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;} TABLE{border: 1px solid black; border-collapse: collapse; font-size: 8pt;} TH{border: 1px solid #969595; background: #dddddd; padding: 5px; color: #000000;} TD{border: 1px solid #969595; padding: 5px; } TD.disabledaccount{background: #FFC0CB;} TD.higherlicensecost{background: #E3242B;} TD.averagelicensecost{background: #FFFF00;} TD.lowerlicensecost{background: #4cff47;} TD.inactiveaccount{background: #FF474C;} TD.duplicatelicenses{background: #F8FF00} </style> <body> <div align=center> <p><h1>Microsoft 365 Licensing Report</h1></p> <p><h2><b>For the " + Orgname + " tenant Generated: " + $ReportRunDate + ""

    If (PricingInfoAvailable) { HtmlBody1 = $Report | Select-Object User, UPN, Country, Department, Title, Company, "Direct assigned licenses", "Disabled Plans", "Group based licenses", "Annual License Costs", "Last license change", "Account created", "Last Signin", "Days since last signin", "Duplicates detected", Status, "Account status" | ConvertTo-Html -Fragment

    Create an attribute class to use, name it, and append to the XML table attributes

    [xml]XML=XML = HTMLBody1 TableClass=TableClass = XML.CreateAttribute("class") TableClass.Value="State"TableClass.Value = "State" XML.table.Attributes.Append($TableClass) | Out-Null

    Conditional formatting for the table rows.

    ForEach (TableRowinTableRow in XML.table.SelectNodes("tr")) { # each TR becomes a member of class "tablerow" TableRow.SetAttribute("class","tablerow") # Make sure that we can deal with the days since last sign-in property, which could be blank for new accounts Try { [int]DaysSinceLastSignIn = TableRow.td[13] } Catch { # This Catch block will be executed if the value of the DaysSinceLastSignIn property is not an integer # like "Unknown" DaysSinceLastSignIn = 99999 } # Level of license cost Try { UserUPN=UserUPN = TableRow.td[1] } Catch { # ignore error in fetching user principal name for header lines Continue } # Fetch total license cost without currency prefix Cost=Cost = Report.Where{.UPN−eq_.UPN -eq UserUPN} | Select-Object -ExpandProperty UserCosts Switch (Cost) { {Cost -gt MediumCost} { TableRow.SelectNodes("td")[9].SetAttribute("class","higherlicensecost") #Write-Host ("High cost for {0}" -f TableRow.td[1]) } {(Cost -gt LowCost)−and(LowCost) -and (Cost -le MediumCost)} { TableRow.SelectNodes("td")[9].SetAttribute("class","averagelicensecost") #Write-Host ("Medium cost for {0}" -f TableRow.td[1]) } {Cost -le LowCost} { TableRow.SelectNodes("td")[9].SetAttribute("class","lowerlicensecost") #Write-Host ("Low cost for {0}" -f TableRow.td[1]) } } # Highlight accounts that haven't signed in for more than 90 days If ((TableRow.td) -and (DaysSinceLastSignIn -ge 90)) { ## tag the TD as inactive TableRow.SelectNodes("td")[13].SetAttribute("class","inactiveaccount") } # If duplicate licenses are detected If ((TableRow.td)−and([string]TableRow.td) -and ([string]TableRow.td[14] -ne 'N/A')) { # tag the TD with the color for duplicate licenses # Write-Host "Detected duplicate licenses for ((TableRow.td[1])" TableRow.SelectNodes("td")[14].SetAttribute("class","duplicatelicenses") } # If row has the account status set to disabled If ((TableRow.td) -and ([string]TableRow.td[16] -eq 'disabled')) { ## tag the TD with the color for a disabled account TableRow.SelectNodes("td")[16].SetAttribute("class","disabledaccount") } } # Wrap the output table with a div tag HTMLBody1=[string]::Format(′<divclass="tablediv">0</div>′,HTMLBody1 = [string]::Format('<div class="tablediv">{0}</div>',XML.OuterXml) } Else { HtmlBody1=HtmlBody1 = Report | ConvertTo-Html -Fragment } HtmlBody1=HtmlBody1 = HtmlBody1 + "Report created for: " + OrgName+"</p><p>Created:"+OrgName + "</p><p>Created: " + ReportRunDate + ""

    HtmlBody2=HtmlBody2 = SkuReport | Select-Object "SKU Id", "SKU Name", "Units used", "Units purchased", "Annual licensing cost" | ConvertTo-Html -Fragment $HtmlSkuSeparator = "Product License Distribution"

    $HtmlTail = ""

    Add first set of cost analysis if pricing information is available

    If (PricingInfoAvailable) { HTMLTail = HTMLTail+"<h2>LicensingCostAnalysis</h2>"+"<p>Totallicensingcostfortenant:"+HTMLTail + "<h2>Licensing Cost Analysis</h2>" + "<p>Total licensing cost for tenant: " + TotalBoughtLicenseCostsOutput + "" + "Total cost for assigned licenses: " + TotalUserLicenseCostsOutput+"</p>"+"<p>Percentboughtlicensesassignedtousers:"+TotalUserLicenseCostsOutput + "</p>" + "<p>Percent bought licenses assigned to users: " + PercentBoughtLicensesUsed + "" + "Average licensing cost per user: " + AverageCostPerUserOutput+"</p>"+"<p><h2>LicenseCostsbyCountry</h2></p>"+AverageCostPerUserOutput + "</p>" + "<p><h2>License Costs by Country</h2></p> " + CountryHTML + "License costs for users without a country: " + NoCountryCosts+"<p><h2>LicenseCostsbyDepartment</h2></p>"+NoCountryCosts + "<p><h2>License Costs by Department</h2></p> " + DepartmentHTML + "License costs for users without a department: " + NoDepartmentCosts+"<p><h2>LicenseCostsbyCompany</h2></p>"+NoDepartmentCosts + "<p><h2>License Costs by Company</h2></p> " + CompanyHTML + "License costs for users without a department: " + $NoCompanyCosts

    If (DetailedCompanyAnalyis) { HTMLTail = HTMLTail+HTMLTail + CompanyAnalysisHTML } }

    Add cost center information if we've been asked to generate it

    If (CostCenterAttribute) { HTMLTail = HtmlTail+"<h2>CostCenterAnalysis</h2><p></p>"+HtmlTail + "<h2>Cost Center Analysis</h2><p></p>" + CostCenterHTML + "" + "License costs for users without a cost center: " + $NoCostCenterCosts }

    Add the second part of the cost analysis if pricing information is available

    If (PricingInfoAvailable) { HTMLTail = HTMLTail+"<p><h2>InactiveUserAccounts</h2></p>"+"<p>Numberofinactiveuseraccounts:"+HTMLTail + "<p><h2>Inactive User Accounts</h2></p>" + "<p>Number of inactive user accounts: " + InactiveUserAccounts.Count + "" + "Names of inactive accounts: " + (InactiveUserAccounts.User−join",")+"</p>"+"<p>Costofinactiveuseraccounts:"+InactiveUserAccounts.User -join ", ") + "</p>" + "<p>Cost of inactive user accounts: " + InactiveUserAccountsCost + "" + "Disabled User Accounts" + "Number of disabled accounts: " + DisabledUserAccounts.Count+"</p>"+"<p>Namesofdisabledaccounts:"+(DisabledUserAccounts.Count + "</p>" + "<p>Names of disabled accounts: " + (DisabledUserAccounts.User -join ", ") + "" + "Cost of disabled user accounts: " + $DisabledUserAccountsCost + "" }

    HTMLTail=HTMLTail = HTMLTail + "-----------------------------------------------------------------------------------------------------------------------------" +
    "Number of licensed user accounts found: " + Report.Count+"</p>"+"<p>Numberofunderuseduseraccountsfound:"+Report.Count + "</p>" + "<p>Number of underused user accounts found: " + UnderUsedAccounts.Count + "" + "Percent underused user accounts: " + PercentUnderusedAccounts+"</p>"+"<p>Accountsdetectedwithduplicatelicenses:"+PercentUnderusedAccounts + "</p>" + "<p>Accounts detected with duplicate licenses: " + DuplicateSKUsAccounts + "" + "Count of duplicate licenses: " + DuplicateSKULicenses+"</p>"+"<p>Countoferrors:"+DuplicateSKULicenses + "</p>" + "<p>Count of errors: " + LicenseErrorCount + "" + "-----------------------------------------------------------------------------------------------------------------------------"

    HTMLTail=HTMLTail = HTMLTail + "Microsoft 365 Licensing Report " + Version+"</b></p>"Version + "</b></p>" HtmlReportFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Microsoft 365 Licensing Report.html" HtmlReport=HtmlReport = Htmlhead + Htmlbody1+Htmlbody1 + HtmlSkuSeparator + HtmlBody2+HtmlBody2 + Htmltail HtmlReport∣Out−FileHtmlReport | Out-File HtmlReportFile -Encoding UTF8

    Generate output report files

    If (Get-Module ImportExcel -ListAvailable) { ExcelGenerated=ExcelGenerated = True Import-Module ImportExcel -ErrorAction SilentlyContinue ExcelOutputFile=((New−Object−ComObjectShell.Application).Namespace(′shell:Downloads′).Self.Path)+"\Microsoft365LicensingReport.xlsx"ExcelOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Microsoft 365 Licensing Report.xlsx" Report | Export-Excel -Path ExcelOutputFile−WorksheetName"Microsoft365LicensingReport"−Title("Microsoft365LicensingReport0"−f(Get−Date−format′dd−MMM−yyyy′))−TitleBold−TableName"Microsoft365LicensingReport"If(ExcelOutputFile -WorksheetName "Microsoft 365 Licensing Report" -Title ("Microsoft 365 Licensing Report {0}" -f (Get-Date -format 'dd-MMM-yyyy')) -TitleBold -TableName "Microsoft365LicensingReport" If (DetailedCompanyAnalyis) { DetailedExcelOutputFile=((New−Object−ComObjectShell.Application).Namespace(′shell:Downloads′).Self.Path)+"\DetailedMicrosoft365LicensingReport.xlsx"DetailedExcelOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Detailed Microsoft 365 Licensing Report.xlsx" DetailedLicenseReport | Export-Excel -Path DetailedExcelOutputFile -WorksheetName "Detailed Microsoft 365 Licensing" ` -Title ("Detailed Microsoft 365 Licensing Report {0}" -f (Get-Date -format 'dd-MMM-yyyy')) -TitleBold -TableName "DetailedMicrosoft365LicensingReport" } } Else { CSVOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Microsoft 365 Licensing Report.CSV" Report∣Export−Csv−PathReport | Export-Csv -Path CSVOutputFile -NoTypeInformation -Encoding Utf8 }

    Write-Host "" Write-Host "Microsoft 365 Licensing Report complete" Write-Host "---------------------------------------" Write-Host "" Write-Host ("An HTML report is available in {0}" -f HtmlReportFile)If(HtmlReportFile) If (ExcelGenerated) { Write-Host ("An Excel report is available in {0}" -f ExcelOutputFile) } Else { Write-Host ("A CSV report is available in {0}" -f CSVOutputFile) }

    Disconnect-MgGraph

    An example script used to illustrate a concept. More information about the topic can be found in the Office 365 for IT Pros eBook https://gum.co/O365IT/

    and/or a relevant article on https://office365itpros.com or https://www.practical365.com. See our post about the Office 365 for IT Pros repository # https://office365itpros.com/office-365-github-repository/ for information about the scripts we write.

    Do not use our scripts in production until you are satisfied that the code meets the need of your organization. Never run any code downloaded from the Internet without

    first validating the code in a non-production environment.

  2. Assistant
    • Potential Cross-Site Scripting (XSS) Vulnerabilities: The script generates an HTML report that includes user data such as display names, user principal names, departments, and more. These fields are directly inserted into the HTML without proper sanitization or encoding. If any user data contains malicious HTML or JavaScript code, opening the report in a browser could execute unintended scripts.

    • Storing Sensitive Data in Insecure Locations: Reports containing detailed user license information are saved to the user's Downloads folder:

      $HtmlReportFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Microsoft 365 Licensing Report.html"
      

      The Downloads folder may not have appropriate security controls, potentially exposing sensitive data to unauthorized users with access to the system.

    • Hardcoded File Paths and Insecure File Handling: The script uses hardcoded paths for CSV files:

      $SkuDataPath = "C:\temp\SkuDataComplete.csv"
      $ServicePlanPath = "C:\temp\ServicePlanDataComplete.csv"
      

      If these files are modified by unauthorized users or malicious software, it could lead to inaccurate reporting or manipulation of the output data.

    • Lack of Input Validation: The script imports data from CSV files and processes user input without thorough validation or sanitization. Malicious data within these files could lead to script errors or unintended behaviors.

    • Excessive Privileges Requested: The script requests extensive permissions when connecting to Microsoft Graph:

      Connect-MgGraph -Scope "Directory.AccessAsUser.All, Directory.Read.All, AuditLog.Read.All" -NoWelcome
      

      These high-level permissions can access a wide range of directory information. If the script or the access tokens it obtains are compromised, an attacker could gain significant access to the organization's data.

    • Insecure Credential Handling in Alternative Authentication Method: Although commented out, the script includes an alternative authentication method that uses hardcoded credentials:

      $ApplicationId = "<applicationId>"
      $SecuredPassword = "<securedPassword>"
      

      If this section is used and the credentials are not securely stored (e.g., in plain text within the script), it poses a significant security risk by exposing sensitive application credentials.

    • Detailed Error Messages May Reveal Sensitive Information: The script outputs error messages that might disclose internal details:

      Write-Host ("Error finding license {0} in pricing table - please check" -f $License)
      

      Displaying such messages can provide attackers with information about the environment or the data structure, aiding in targeted attacks.

    • Possible Information Disclosure through Logs: Using Write-Host and other logging mechanisms without controlling where the output goes can lead to sensitive information being written to logs that are accessible to unauthorized users.

    • No Output Encryption or Protection: The generated reports (HTML, CSV, or Excel files) are not encrypted or password-protected. If these files are intercepted or accessed by unauthorized individuals, they can read all the contained sensitive information.

    • No Validation on User Properties Used in Reports: User properties like department, country, and company are used directly from the user data. If these properties are manipulated (either accidentally or maliciously), it could affect the report's integrity or lead to incorrect cost allocations.

    • Potential for Denial of Service through Large Data Sets: If the script is run in a tenant with a very large number of users, it might consume significant memory or processing resources, potentially impacting the performance of the system on which it's run.

    • Insufficient Error Handling on Network Operations: The script assumes that network operations (like connecting to Microsoft Graph) will succeed. Lack of robust error handling could cause the script to fail unexpectedly if network issues occur.

    • No Authentication Checks for Imported Modules: The script checks for the ImportExcel module but doesn't verify its authenticity. If a malicious module with the same name is installed on the system, it could execute unintended code:

      If (Get-Module ImportExcel -ListAvailable) {
        Import-Module ImportExcel -ErrorAction SilentlyContinue
        # ...
      }
      
    • Execution of External Commands without Validation: The script uses New-Object -ComObject to interact with system components. If an attacker can influence the parameters or the environment, it might be exploited.

    • Potential Disclosure of Organizational Structure: By exporting detailed reports that include user names, departments, job titles, and more, the script could inadvertently disclose internal organizational structures to unauthorized parties if the reports are shared or stored insecurely.

    • Lack of Secure Coding Practices: The script does not use parameter validation attributes or other PowerShell best practices that help prevent injection attacks or misuse.

    • No Access Controls on Script Execution: There's no mention of restricting who can execute the script. Without proper access controls, any user with access to the system could potentially run the script and access sensitive information.