CheckInvoicing/Get-M365LicenseInvoicing.ps1
|
$AboCSVPath = "CheckInvoicing\ABO.CSV" if (-not (Test-Path $AboCSVPath)) { Write-Host "ABO.CSV file not found at path: $AboCSVPath" -ForegroundColor Red Write-Host "Please place the file in the correct location. (Export as Excel from KL)" -ForegroundColor Yellow exit } $createdDate = (Get-Item $AboCSVPath).CreationTime $threeDaysAgo = (Get-Date).AddDays(-3) if ($createdDate -lt $threeDaysAgo) { Write-Host "ABO.CSV file is older than 3 days. Please update it." -ForegroundColor Red exit } $ignoredKL = @(602948,600410,211475) $TimeStart = Get-Date Write-Host "Script started at $TimeStart" $customerLicenses = @() Write-Host "Getting all subscriptions..." $allSubscriptions = Get-CuranetCustomerSubscriptions -Account 3850 #| Where-Object { $_.subscriptionRef -eq 211475 } Write-Host "Found $($allSubscriptions.Count) subscriptions in Curanet API" Write-Host "Getting all licenses..." Write-Host "This may take a while..." -ForegroundColor Yellow $i = 0 foreach ($subscription in $allSubscriptions) { $i++ if ($i -gt 300) { #break } if ($subscription.state -ne 'Running') { continue } $kl = ($subscription.subscriptionRef).Trim() if ($kl -match 'INTERN*') { continue } if ($kl -in $ignoredKL) { continue } $licenses = Get-CuranetM365Licenses -SubscriptionId $subscription.id -Account 3850 # Group licenses by title and term $licenseArray = @{} foreach ($license in $licenses) { # Skip if price is 0 if ($license.unitPrice -eq 0) { continue } $title = $license.title $term = $license.termDuration # Initialize the key if it doesn't exist if (-not $licenseArray.ContainsKey($title)) { $licenseArray[$title] = @{} } # Initialize the term if it doesn't exist if (-not $licenseArray[$title].ContainsKey($term)) { $licenseArray[$title][$term] = 0 } $licenseArray[$title][$term] += $license.quantity } if ($licenseArray.Count -eq 0) { continue } # Add the subscription's grouped licenses to the result $customerLicenses += [pscustomobject]@{ KL = $kl Licenses = $licenseArray } } $customerKLSubscriptions = ConvertFrom-Csv (Get-Content -Path "CheckInvoicing\ABO.CSV") -Delimiter ";" $customerKLSubscriptions = $customerKLSubscriptions $skuMapping = @{ "Microsoft 365 Business Standard-OneYear" = @(170004, 170017) "Microsoft 365 Business Standard-OneMonth" = @(170003, 170016) "Microsoft 365 Business Premium-OneYear" = @(170006, 170019) "Microsoft 365 Business Premium-OneMonth" = @(170005, 170018) "Microsoft 365 Business Basic-OneYear" = @(170002, 170015,170011) "Microsoft 365 Business Basic-OneMonth" = @(170000, 170014,170010) "Microsoft 365 F1-OneYear" = @(170129) "Microsoft 365 F1-OneMonth" = @(170130) "Microsoft 365 Audio Conferencing-OneYear" = @(170071) "Microsoft 365 Audio Conferencing-OneMonth" = @(170070) "Exchange Online (Plan 1)-OneYear" = @(170009, 170024) "Exchange Online (Plan 1)-OneMonth" = @(170081, 170096) "Exchange Online (Plan 2)-OneYear" = @(170025, 170120) "Exchange Online (Plan 2)-OneMonth" = @(170140) "Microsoft 365 Copilot-OneYear" = @(170133) "Power Automate per user plan-OneYear" = @(170067) "Power Automate per user plan-OneMonth" = @(170066) "Microsoft 365 Apps for business-OneYear" = @(170021) "Microsoft 365 Apps for business-OneMonth" = @(170020) "OneDrive for business (Plan 1)-OneYear" = @(170055,170119) "OneDrive for business (Plan 1)-OneMonth" = @(170054) "OneDrive for business (Plan 2)-OneYear" = @(170121) "Microsoft 365 Apps for enterprise-OneYear" = @(170023) "Microsoft 365 Apps for enterprise-OneMonth" = @(170022) "Power Automate Premium-OneYear" = @(170139) "Power Automate Premium-OneMonth" = @(170138) "Office 365 E3-OneYear" = @(170029, 170008) "Office 365 E3-OneMonth" = @(170028, 170007) "Office 365 E3 EEA (no Teams)-OneYear" = @(170125) "Office 365 E3 EEA (no Teams)-OneMonth" = @(170126) "Visio Plan 1-OneYear" = @(170051) "Visio Plan 2-OneYear" = @(170053) "Visio Plan 2-OneMonth" = @(170052) "Microsoft 365 Business Premium (Nonprofit Staff Pricing)-OneYear" = @(170152,170145) "Power BI Pro (Non-Profit Pricing)-OneYear" = @(170146) "Office 365 E1-OneYear" = @(170027) "Office 365 E1-OneMonth" = @(170026) "Planner and Project Plan 3-OneYear" = @(170039) "Planner and Project Plan 3-OneMonth" = @(170038) "Power BI Pro-OneYear" = @(170043) "Power BI Pro-OneMonth" = @(170042) "Microsoft 365 F3-OneMonth" = @(170137) "Microsoft 365 E3-OneYear" = @(170033) "Microsoft 365 E3-OneMonth" = @(170032) "Microsoft Entra ID P1-OneYear" = @(170047) "Microsoft Entra ID P1-OneMonth" = @(170046) "Microsoft Teams Essentials-OneYear" = @(170094) "Microsoft Teams Essentials-OneMonth" = @(170095) "Planner Plan 1-OneYear" = @(170142,170037) "SharePoint (Plan 2)-OneYear" = @(170069) "Power Apps per app plan-OneYear" = @(170148,170080) "Office 365 A3 (Education Faculty Pricing)-OneYear" = @(170059) "Office 365 A3 (Education Faculty Pricing)-OneMonth" = @(170058) "Microsoft 365 A3 (Education Student Pricing)-OneYear" = @(170063) "Microsoft 365 A3 (Education Student Pricing)-OneMonth" = @(170062) "Microsoft Intune Plan 1 Devices-OneYear" = @(170075) "Microsoft Intune Plan 1 Devices-OneMonth" = @(170074) "Power Automate unattended RPA add-on-OneYear" = @(170078) "Microsoft Teams Premium-OneYear" = @(170123) "Power BI Premium Per User-OneYear" = @(170045) "Power BI Pro (Non-Profit Pricing)-OneMonth" = @(170146) "Microsoft Teams Phone Standard-OneYear" = @(170073) } $transformedKLSubscriptions = @() foreach ($klGroup in $customerKLSubscriptions | Group-Object -Property 'Kundenr.') { $kl = $klGroup.Name if ($ignoredKL -contains $kl) { continue } $licenseArray = @{} foreach ($subscription in $klGroup.Group) { $sku = $subscription.'Varenr.' if (-not $skuMapping.Values | Where-Object { $_ -contains $sku }) { continue } $quantity = [int]$subscription.'Faktureret antal' if([int]$subscription.'Antal ialt' -gt $quantity) { $quantity = [int]$subscription.'Antal ialt' } $titleTerm = $skuMapping.GetEnumerator() | Where-Object { $_.Value -contains $sku } if ($titleTerm) { $keyParts = $titleTerm.Key -split '-' $title = $keyParts[0] $term = $keyParts[1] # Initialize the title and term in the license array if not already present if (-not $licenseArray.ContainsKey($title)) { $licenseArray[$title] = @{} } if (-not $licenseArray[$title].ContainsKey($term)) { $licenseArray[$title][$term] = 0 } # Add the quantity to the appropriate group if its not expired $expired = $false if(($subscription.'Opsagt pr. dato').Length -gt 2) { if(($subscription.'N�ste hovedfaktureringsdato').Length -gt 2) { $linieUdloeb = [DateTime]::ParseExact($subscription.'N�ste hovedfaktureringsdato', "dd-MM-yyyy", $null) } $cancelledDate = [DateTime]::ParseExact($subscription.'Opsagt pr. dato', "dd-MM-yyyy", $null) $latestDate = $cancelledDate if($linieUdloeb -gt $cancelledDate) { $latestDate = $linieUdloeb } $expired = (Get-Date) -gt $latestDate } if($expired) { continue } $licenseArray[$title][$term] += $quantity } } # Add the transformed data to the result $transformedKLSubscriptions += [pscustomobject]@{ KL = $kl Licenses = $licenseArray } } # Compare $customerLicenses with $transformedKLSubscriptions $differences = @() foreach ($customerLicense in $customerLicenses) { $kl = $customerLicense.KL $registeredData = $transformedKLSubscriptions | Where-Object { $_.KL -eq $kl } if (-not $registeredData) { # Log missing KL in registered data $differences += [pscustomobject]@{ KL = $kl Title = "N/A" Term = "N/A" APIQuantity = "N/A" RegisteredQuantity = "Missing KL in registered data" } continue } foreach ($title in $customerLicense.Licenses.Keys) { foreach ($term in $customerLicense.Licenses[$title].Keys) { $apiQuantity = $customerLicense.Licenses[$title][$term] if ($registeredData.Licenses.ContainsKey($title) -and $registeredData.Licenses[$title].ContainsKey($term)) { $registeredQuantity = $registeredData.Licenses[$title][$term] if ($apiQuantity -gt $registeredQuantity) { # Log mismatch in quantities $differences += [pscustomobject]@{ KL = $kl Title = $title Term = $term APIQuantity = $apiQuantity RegisteredQuantity = $registeredQuantity } } } else { # Log missing title/term in registered data $differences += [pscustomobject]@{ KL = $kl Title = $title Term = $term APIQuantity = $apiQuantity RegisteredQuantity = "Missing in registered data" } } } } } # Output differences if ($differences.Count -gt 0) { Write-Host "Differences found:" $differences | Format-Table -AutoSize # Optionally export differences to a CSV file $differences | Export-Excel -Path "CheckInvoicing\M365Licenses.xlsx" -AutoSize -TableStyle Dark1 -Title "Differences in M365 License Invoicing" -WorksheetName "Differences" -ClearSheet } else { Write-Host "No differences found. All data matches. Something might be wrong, lol!" } $TimeEnd = Get-Date $TimeSpan = $TimeEnd - $TimeStart Write-Host "Script execution time: $($TimeSpan.Minutes) minutes and $($TimeSpan.Seconds) seconds" |