Private/Get-CitrixLicenseExcelData.ps1
|
<#
.SYNOPSIS Retrieves and summarizes Citrix license usage data from a monthly Excel report. .DESCRIPTION The Get-CitrixLicenseExcelData function reads a Citrix license usage Excel report for a specified month and extracts license consumption metrics including licenses in use, total licenses, available licenses, and percentage used. The report filename is automatically generated using the format `MonthlyLicReport_MMM-yyyy.xlsx`, based on the provided `MonthsBack` value. .PARAMETER ReportPath The folder path where the Excel reports are stored. Defaults to `"C:\Script\CitrixLicensing\Monthly"`. .PARAMETER MonthsBack The number of months to go back from the current month to locate the report. Must be a value between 1 and 12. Defaults to 1 (last month). .EXAMPLE Get-CitrixLicenseExcelData Retrieves the license data from last month's report stored in the default report path. .EXAMPLE Get-CitrixLicenseExcelData -MonthsBack 2 Retrieves the license usage data from two months ago. .EXAMPLE Get-CitrixLicenseExcelData -ReportPath "D:\Reports\Licensing" -MonthsBack 1 Retrieves the license data from a custom report path for the previous month. .OUTPUTS [PSCustomObject] containing: - ReportMonth - LicensesInUse - TotalLicenses - LicensesAvailable - PercentageUsed - ReportPath .NOTES - The Excel file must have headers and contain columns: Licensesinuse, TotalLicenses, Percentageused. - Uses an external helper function `Get-FirstRowWithHeadersFromExcel` to extract data. - The `%` symbol in PercentageUsed is removed and cast to an integer. #> function Get-CitrixLicenseExcelData { [CmdletBinding()] param ( [Parameter()] [string]$ReportPath = "C:\Script\CitrixLicensing\Monthly", [Parameter()] [ValidateRange(1, 12)] [int]$MonthsBack = 1 ) try { $targetMonth = (Get-Date).AddMonths(-$MonthsBack) $formattedMonth = $targetMonth.ToString('MMM-yyyy') $reportName = "MonthlyLicReport_{0}.xlsx" -f $formattedMonth $fullPath = Join-Path $ReportPath $reportName if (-not (Test-Path $fullPath)) { Write-LogEntry -Message "License report not found: $fullPath" return $null } $licenseData = Get-FirstRowWithHeadersFromExcel -ExcelPath $fullPath $licenseInUse = [int]$licenseData.Licensesinuse $totalLicenses = [int]$licenseData.TotalLicenses $licenseAvailable = $totalLicenses - $licenseInUse $percentageUsed = [int]($licenseData["Percentageused"] -replace "%", "") $summary = [PSCustomObject]@{ ReportMonth = $formattedMonth LicensesInUse = $licenseInUse TotalLicenses = $totalLicenses LicensesAvailable = $licenseAvailable PercentageUsed = $percentageUsed ReportPath = $fullPath } return $summary } catch { Write-LogEntry -Message "Failed to process license usage report: $_" } } |