Do you see any security issues with the following code? Please respond with a bullet…
- 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 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]Costs = 0 ForEach (Licenses) { Try { [string]PricingHashTable[License] # Convert monthly cost to cents (because some licenses cost sums like 16.40) [float]LicenseCostCents = [float]LicenseCostCents -gt 0) { # Compute annual cost for the license [float]LicenseCostCents * 12 # Add to the cumulative license costs Costs + (AnnualCost) # Write-Host ("License {0} Cost {1} running total {2}" -f License, Costs) } } Catch { Write-Host ("Error finding license {0} in pricing table - please check" -f $License) } }
Return
Return ($Costs / 100) }
[datetime]ReportRunDate = Get-Date (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) SecuredPassword = "" $tenantID = ""
SecuredPassword -AsPlainText -Force ApplicationId, 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.
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 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 }
skuDataPath servicePlanPath Line in ImportSkus) { SkuHashTable.Add([string]Line.DisplayName) } Line2 in ImportServicePlans) { ServicePlanHashTable.Add([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 true and populating the $PricingHashTable
false
If (ImportSkus[0].Price) { PricingInfoAvailable = Global:PricingHashTable = @{} ForEach (ImportSkus) { Line.SkuId, [string]Line.Price) } If (ImportSkus[0].Currency) { [string]ImportSkus[0].Currency) } }
Find tenant accounts - but filtered so that we only fetch those with licenses
Write-Host "Finding licensed user accounts..." [Array]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 DisplayNameIf (!(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]Users.Department | Sort-Object -Unique [array]Users.Country | Sort-Object -Unique [array]Users.OnPremisesExtensionAttributes.(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.
true
$OrgName = (Get-MgOrganization).DisplayName
Current subscriptions in the tenant. We use this table to remove expired licenses from the calculation
[array]CurrentSubscriptionsHash = @{} ForEach (CurrentSubscriptions) { S.SkuId, $S.SkuPartNumber) }
DuplicateSKULicenses = 0; Report = [System.Collections.Generic.List[Object]]::new() i = 0 [float]TotalBoughtLicenseCosts = 0
ForEach (Users) { i++; ErrorMsg = ""; User.UserPrincipalName, Users.Count) If ([string]::IsNullOrWhiteSpace(False) { # Only process account if it has some licenses [array]Null; [array]Null; # Find out if any of the user's licenses are assigned via group-based licensing [array]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 { _.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 GroupProductName = G.SkuId] GroupProductName, GroupName) } ForEach (G in GroupErrorAssignments) { GroupName = (Get-MgGroup -GroupId GroupProductName = G.SkuId] G.Error GroupLicensing += ("{0} assigned from {1} BUT ERROR {2}!" -f 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
User.SignInActivity.LastSignInDateTime User.SignInActivity.LastNonInteractiveSignInDateTime
If (-not LastNonInteractiveSignIn) { 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 CompareDate = LastSignIn } Else { # Only LastNonInteractiveSignIn has a value LastNonInteractiveSignIn }
$DaysSinceLastSignIn = ($RunDate - $CompareDate).Days $LastAccess = Get-Date($CompareDate) -format g If ($DaysSinceLastSignIn -gt 60) { $UnusedAccountWarning = ("Account unused for {0} days - check!" -f $DaysSinceLastSignIn) }}
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]Null If (CostCenterAttribute) { CostCenter = CostCenterAttribute) }
Report information
[string]DisabledPlans -join ", " [string]LicenseInfo -join (", ")
If (False) { AccountStatus = "Disabled" } Else { AccountStatus = "Enabled" }
If (PricingInfoAvailable) { # Output report line with pricing info [float]UserCosts = Get-LicenseCosts -Licenses TotalUserLicenseCosts = UserCosts ReportLine = [PSCustomObject][Ordered]@{ User = User.DisplayName UPN = User.Country Department = User.JobTitle Company = LicenseInfo "Disabled Plans" = GroupLicensingAssignments "Annual License Costs" = ("{0} {1}" -f UserCosts.toString('F2'))) "Last license change" = AccountCreatedDate "Last Signin" = DaysSinceLastSignIn "Duplicates detected" = UnusedAccountWarning "Account status" = UserCosts
'Cost Center' = CostCenter } } Else { # No pricing information ReportLine = [PSCustomObject][Ordered]@{
User = User.UserPrincipalName Country = User.Department Title = User.companyName "Direct assigned licenses" = DisabledPlans.Trim() "Group based licenses" = LastLicenseChange "Account created" = LastAccess "Days since last signin" = DuplicateWarningReport Status = AccountStatus } }
ReportLine)Populate the detailed license assignment report
SkuUserReport | Sort-Object Sku -Unique ForEach (SkuUserReport) { SkuReportLine = [PSCustomObject][Ordered]@{ User = Item.User Name = Item.Sku SkuName = (Item.Sku]) Method = Item.Country Department = Item.Company
} SkuReportLine) } } # End ForEach UsersReport | Where-Object { _.Status -ne "OK" } PercentUnderusedAccounts = (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
SkuSummary = Get-MgSubscribedSku | Select-Object SkuId, ConsumedUnits, PrepaidUnits SkuSummary | Where-Object { _.ConsumedUnits -ne 0 } ForEach (S in SkuSummary) { SKUCost = Get-LicenseCosts -Licenses SkuDisplayName = S.SkuId] If (S.ConsumedUnits ) { S.ConsumedUnits } Else { S.PrepaidUnits.Enabled } If (PricingInfoAvailable) { SKUTotalCost = (BoughtUnits) SkuReportLine = [PSCustomObject][Ordered]@{ "SKU Id" = S.SkuId "SKU Name" = S.ConsumedUnits "Units Purchased" = SKUTotalCost "Annual licensing cost" = ("{0} {1}" -f SKUTotalCost)) } } Else { SkuReportLine = [PSCustomObject][Ordered]@{ "SKU Id" = S.SkuId "SKU Name" = S.ConsumedUnits "Units Purchased" = BoughtUnits } } SkuReport.Add(TotalBoughtLicenseCosts = SKUTotalCost }
If (PricingInfoAvailable) { AverageCostPerUser = (Users.Count) Currency, ('{0:N2}' -f TotalUserLicenseCostsOutput = ("{0} {1}" -f TotalUserLicenseCosts)) Currency, ('{0:N2}' -f PercentBoughtLicensesUsed = (TotalBoughtLicenseCosts).toString('P') SkuReport | Sort-Object "Annual license costs" -Descending } Else { SkuReport | Sort-Object "SKU Name" -Descending }
Generate the department analysis
Department in Departments) { [array]DepartmentRecords = Report | Where-Object {_.Department -eq Department} DepartmentReportLine = [PSCustomObject][Ordered]@{ Department = DepartmentRecords.count Costs = ("{0} {1}" -f DepartmentRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f DepartmentRecords | Measure-Object UserCosts -Average).Average)) } DepartmentReportLine) } DepartmentReport | ConvertTo-HTML -Fragment
Anyone without a department?
[array]Report | Where-Object { _.Department } If (NoDepartment) { NoDepartmentCosts = ("{0} {1}" -f NoDepartment | Measure-Object UserCosts -Sum).Sum)) } Else { $NoDepartmentCosts = "Zero" }
Generate the country analysis
Country in Countries) { [array]CountryRecords = Report | Where-Object {_.Country -eq Country} CountryReportLine = [PSCustomObject][Ordered]@{ Country = CountryRecords.count Costs = ("{0} {1}" -f CountryRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f CountryRecords | Measure-Object UserCosts -Average).Average)) } CountryReportLine) } CountryReport | ConvertTo-HTML -Fragment
Anyone without a country?
[array]Report | Where-Object { _.Country } If (NoCountry) { NoCountryCosts = ("{0} {1}" -f NoCountry | Measure-Object UserCosts -Sum).Sum)) } Else { $NoCountryCosts = "Zero" }
Generate cost center analysis
If (null -ne CostCenterAttribute) { CostCenterReport = [System.Collections.Generic.List[Object]]::new() ForEach (CostCenters) { [array]Report | Where-Object {CostCenter} CostCenterReportLine = [PSCustomObject][Ordered]@{ 'Cost Center' = CostCenter Accounts = Currency, ('{0:N2}' -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) { Currency, ('{0:N2}' -f (NoCostCenter | Measure-Object UserCosts -Sum).Sum)) } Else { NoCostCenterCosts = "Zero" } }
Generate the company analysis
Company in Companies) { [array]CompanyRecords = Report | Where-Object {_.Company -eq Company} CompanyReportLine = [PSCustomObject][Ordered]@{ Company = CompanyRecords.count Costs = ("{0} {1}" -f CompanyRecords | Measure-Object UserCosts -Sum).Sum)) AverageCost = ("{0} {1}" -f CompanyRecords | Measure-Object UserCosts -Average).Average)) } CompanyReportLine) } CompanyReport | ConvertTo-HTML -Fragment
Anyone without an assigned company?
[array]Report | Where-Object { _.Company } If (NoCompany) { NoCompanyCosts = ("{0} {1}" -f NoCompany | Measure-Object UserCosts -Sum).Sum)) } Else { $NoCompanyCosts = "Zero" }
null
Detailed company analysis - example of breaking down costs by SKU for each company
ForEach (Companies) { [array]DetailedLicenseReport | Where-Object {Company} CompanyAnalysisHTML + ("Company Analysis: Product Licenses for {0}" -f 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 Report | Where-Object {(_.'Days since last signin' -eq "Unknown")}
Currency, ('{0:N2}' -f (InactiveUserAccounts | Measure-Object UserCosts -Sum).Sum)) # Disabled user accounts DisabledUserAccounts = Report | Where-Object { _."Account status" -eq "disabled" } Currency, ('{0:N2}' -f ($DisabledUserAccounts | Measure-Object UserCosts -Sum).Sum))
Cost spans for license comparison
AverageCostPerUser * 0.8 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]HTMLBody1 XML.CreateAttribute("class") XML.table.Attributes.Append($TableClass) | Out-Null
Conditional formatting for the table rows.
ForEach (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 { TableRow.td[1] } Catch { # ignore error in fetching user principal name for header lines Continue } # Fetch total license cost without currency prefix Report.Where{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 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[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 XML.OuterXml) } Else { Report | ConvertTo-Html -Fragment } HtmlBody1 + "Report created for: " + ReportRunDate + ""
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 = TotalBoughtLicenseCostsOutput + "" + "Total cost for assigned licenses: " + PercentBoughtLicensesUsed + "" + "Average licensing cost per user: " + CountryHTML + "License costs for users without a country: " + DepartmentHTML + "License costs for users without a department: " + CompanyHTML + "License costs for users without a department: " + $NoCompanyCosts
If (DetailedCompanyAnalyis) { HTMLTail = CompanyAnalysisHTML } }
Add cost center information if we've been asked to generate it
If (CostCenterAttribute) { HTMLTail = 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 = InactiveUserAccounts.Count + "" + "Names of inactive accounts: " + (InactiveUserAccountsCost + "" + "Disabled User Accounts" + "Number of disabled accounts: " + DisabledUserAccounts.User -join ", ") + "" + "Cost of disabled user accounts: " + $DisabledUserAccountsCost + "" }
HTMLTail + "-----------------------------------------------------------------------------------------------------------------------------" +
"Number of licensed user accounts found: " + UnderUsedAccounts.Count + "" + "Percent underused user accounts: " + DuplicateSKUsAccounts + "" + "Count of duplicate licenses: " + LicenseErrorCount + "" + "-----------------------------------------------------------------------------------------------------------------------------"HTMLTail + "Microsoft 365 Licensing Report " + HtmlReportFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Microsoft 365 Licensing Report.html" Htmlhead + HtmlSkuSeparator + Htmltail HtmlReportFile -Encoding UTF8
Generate output report files
If (Get-Module ImportExcel -ListAvailable) { True Import-Module ImportExcel -ErrorAction SilentlyContinue Report | Export-Excel -Path DetailedCompanyAnalyis) { 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" 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 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.
- 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
Downloadsfolder:$HtmlReportFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Microsoft 365 Licensing Report.html"The
Downloadsfolder 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" -NoWelcomeThese 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-Hostand 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
ImportExcelmodule 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 -ComObjectto 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.
-