Public/Get-CitrixPowerBIReport.ps1
|
<#
.SYNOPSIS Generates a comprehensive monthly Power BI-ready Citrix report with infrastructure, application, session, license, and usage metrics. .DESCRIPTION This function automates the process of collecting Citrix Virtual Apps and Desktops usage data, licensing details, infrastructure inventory, and VDI information. It retrieves and exports this data from multiple sources including Citrix Delivery Controllers, Active Directory domains, SharePoint, and Excel workbooks. The report is consolidated and exported to CSV and JSON formats, and optionally updated in an Excel table and uploaded to SharePoint. .PARAMETER MonthsBack Specifies how many months back the report should be generated for. For example, use 1 to generate the report for last month, 2 for the month before that, and so on. Valid range: 1 to 12. .PARAMETER ExportFolder Specifies the root folder path on the local system where all generated CSV and JSON reports will be saved before being uploaded to SharePoint. .EXAMPLE Get-CitrixPowerBIReport -MonthsBack 1 -ExportFolder "C:\Reports\Citrix" -Inifilepath "C:\temp\config.ini" This example runs the function to generate and export the previous month’s report to the folder "C:\Reports\Citrix". All artifacts such as server list, sessions, licensing, and application usage will be saved there and published to SharePoint. #> function Get-CitrixPowerBIReport { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [ValidateRange(1, 12)] [int]$MonthsBack, [Parameter(Mandatory = $true)] [string]$ExportFolder, [Parameter(Mandatory = $true)] [string]$Inifilepath ) try { $targetDate = (Get-Date).AddMonths(-$MonthsBack) $Exportto = New-CitrixExportFolders -RootPath $ExportFolder Clear-CitrixExportFolders -RootExportFolder $ExportFolder # Folder paths $ServerList = Join-Path $ExportFolder "ServerList\ServerList.csv" $CitrixResourceList= Join-Path $ExportFolder "CitrixResourceList\ListOfCitrixResources.csv" $CitrixDetails = Join-Path $ExportFolder "CitrixDetails\CitrixDetails.csv" $Hypervisors = Join-Path $ExportFolder "Hypervisors\Hypervisor.csv" $VDIListexport = Join-Path $ExportFolder "VDIList\VDIList.csv" $licexport = Join-Path $ExportFolder -ChildPath Licensing $CitrixSessions = Join-Path $ExportFolder ("CitrixSessions\CitrixSessions_{0}.csv" -f $targetDate.ToString('yyyy_MM')) $jsonfolder = Join-Path -Path $ExportFolder -ChildPath Json # Dates $startDateRaw = Get-Date -Year $targetDate.Year -Month $targetDate.Month -Day 1 -Hour 0 -Minute 0 -Second 0 $endDateRaw = $startDateRaw.AddMonths(1).AddSeconds(-1) $formattedStartDate = $startDateRaw.ToString("yyyy-MM-dd") $formattedEndDate = $endDateRaw.ToString("yyyy-MM-dd") # INI values $prodControllers = Get-INISectionValues -IniPath $Inifilepath -Section "prod" -Key "DeliveryControllers" $drControllers = Get-INISectionValues -IniPath $Inifilepath -Section "dr" -Key "DeliveryControllers" $sitepath = Get-INISectionValues -IniPath $Inifilepath -Section "Site" $xlpath = Get-INISectionValues -IniPath $Inifilepath -Section "Excel" $dnpath = Get-INISectionValues -IniPath $Inifilepath -Section "path" $accountpath = Get-INISectionValues -IniPath $Inifilepath -Section "Account" $domainDetails = Get-INISectionValues -IniPath $Inifilepath -Section "Domains" $DNValue = $dnpath["DN"] $Worksheet = $xlpath["Worksheet"] $TableName = $xlpath["TableName"] $Excelpath = $dnpath["ExcelPath"] $SiteValue = $sitepath["Sharepointsite"] $SPFileUrlPath = $sitepath["SharepointFileUrlPath"] $spfilename = $sitepath["SharepointFileName"] $username = $accountpath["username"] $secret = $accountpath["secret"] $licfolder = $dnpath["LicenseFolder"] $sharePointPathForPnP = $SPFileUrlPath -replace '/', '\' $fullSharePointPath = Join-Path -Path $sharePointPathForPnP -ChildPath $spfilename $localpath = Join-Path -Path $Excelpath -ChildPath $spfilename # Servers $prodServer = Get-RandomPingableServer -ServerList $prodControllers $drServer = Get-RandomPingableServer -ServerList $drControllers $deliveryControllers = @($prodServer, $drServer) # Step 1: Infra inventory $InfraInventory = Get-InfraXA7Inventory -Type "Infra" -DN $DNValue -AdminAddress $prodServer -InfraType Static | Where-Object { $_.OSType -notlike "*File*" } $InfraInventory | Export-Csv -Path $ServerList -NoTypeInformation -Append -Force $HypervisorList = @() foreach ($dc in $deliveryControllers) { Get-InfraXA7Inventory -Type "XA7" -InfraType "Dynamic" -AdminAddress $dc | Export-Csv -Path $ServerList -NoTypeInformation -Append -Force Get-CitrixXA7Applications -AdminAddress $dc | Export-Csv -Path $CitrixResourceList -NoTypeInformation -Append -Force Get-CitrixMachineCatalogReport -AdminAddress $dc | Export-Csv -Path $CitrixDetails -NoTypeInformation -Append -Force $HypervisorList += Get-CitrixHypervisorStatus -DeliveryController $dc $HypervisorList | Export-Csv -Path $Hypervisors -NoTypeInformation -Append -Force Get-CitrixAppUsagePeak -DeliveryController $dc -StartDate $formattedStartDate -EndDate $formattedEndDate | Export-Csv -Path $CitrixSessions -NoTypeInformation -Append -Force Export-CitrixUsageMetrics -DeliveryControllers $dc -StartDate $formattedStartDate -EndDate $formattedEndDate -ExportFolder $jsonfolder } # Step 8: Get Excel from SharePoint #Get-SharePointExcelDocument -SiteUrl $SiteValue -SharePointFileServerRelativeUrl $fullSharePointPath -LocalDownloadPath $Excelpath -Username $username -Passcode $secret # Step 9: Additional data collection $VDIList = Get-CitrixVDIDesktopGroups -ComputerName $prodServer $XA7Apps = Get-CitrixXA7Applications -AdminAddress $prodServer $ProdXA7 = Get-InfraXA7Inventory -Type "XA7" -InfraType "Dynamic" -AdminAddress $prodServer $XA7HypCount = ($HypervisorList | Where-Object { $_.Name -like "*XenApp*" }).Count $VDIHypCount = ($HypervisorList | Where-Object { $_.Name -like "*VDI*" }).Count $VDIList| Export-Csv -Path $VDIListexport -NoTypeInformation -Append -Force # Step 10: RDS License $totalRDSLicenses = 0 foreach ($domain in $domainDetails.Keys) { $dn = $domainDetails[$domain] $domainLower = $domain.ToLower() $result = Get-ADRemoteDesktopLicenseReport -SearchBase $dn -DomainName $domainLower -ExportToCSV $true $totalRDSLicenses += $result.Count } # Step 11: License Excel $CtxLicenseData = Get-CitrixLicenseExcelData -ReportPath $licfolder -MonthsBack $MonthsBack # Step 12: Update Excel table $logData = @{ Month = ([datetime]::Today).AddMonths(-1).AddDays(-(([datetime]::Today).Day - 1)) "License_Available" = $CtxLicenseData.LicensesAvailable "License_inuse" = $CtxLicenseData.LicensesInUse "%utilised" = $CtxLicenseData.PercentageUsed "Total_Licenses" = $CtxLicenseData.TotalLicenses "Total_Infra_Servers" = $InfraInventory.Count "Total_Citrix_Applications" = $XA7Apps.Count "Total_VDI_Published" = $VDIList.Count "Total_XenApp7_Servers" = $ProdXA7.Count "RDS_Licensing" = $totalRDSLicenses "Nutanix_VDI_Clusters" = $VDIHypCount "Nutanix_XA7_Clusters" = $XA7HypCount } Update-ExcelTableDataOptimized -ExcelPath $localpath -WorksheetName $Worksheet -ValuesToUpdate $logData -TableName $TableName # Copy the file Copy-Item -Path $localpath -Destination $licexport -Force Write-Host "📁 File copied to: $licexport" # Step 13: Upload back to SharePoint #Send-ExcelToSharePointSecure -SiteUrl $SiteValue -SharePointFileServerRelativeUrl $fullSharePointPath -LocalExcelFilePath $localpath -Username $username -Passcode $secret #Step14: Upload the folders to sharepoint #Publish-FolderToSharePoint ` # -LocalFolderPath $ExportFolder ` # -SharePointSiteUrl $SiteValue ` # -SharePointFolderRelativeUrl $SPFileUrlPath ` # -Username $username ` # -Passcode $secret Write-LogEntry -Message "The Monthly Report for $targetDate is Generated Successfully" } catch { Write-Error "❌ Failed to calculate date ranges or export data: $_" } } |