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: $_"
    }
}