My ProductOwner gave me an assignment: “Twice a day I receive an email with an attachment. Each attachement contains an excel file with open sessions of Citrix; at 10:00 and at 14:00. Managed devices are on the first tab; the Unmanaged devices on the second. Can you gerenate an line-chart for me so I can see the trend in days?”
I said to him “Let me do my magic”. So first I had to import all files in my PowerShell script.
$locationfiles = "C:\Temp\Files"
$allFiles = Get-ChildItems -Path $locationfiles | Sort Name
The date of the measurement is in the title of the file. The title look like “Session_Citrix_Daily-2023-07-17-10.xlsx”. First I had to cast the title to a datetime object.
# TimeStamp
[string]$timeStamp = $file.Name -replace "Sessions_Citrix_Daily-","" -replace ".xlsx",""
# TimeStampTime
[string]$timeStampTime = $timeStamp.Split("-")[-1]
[string]$timeStampTimeNotation = $timeStampTime + ':00'
# TimeStampDate
[datetime]$timeStampDate = Get-Date -Date ($timeStamp.Substring(0,$timeStamp.LastIndexOf('-')))
After that load the first Excel file:
# Managed Devices Tab
[object]$managedDevices = Import-Excel $file.FullName -WorksheetName 'Managed'
# Unmanaged Devices Tab
[object]$unmanagedDevices = Import-Excel $file.FullName -WorksheetName 'Unmanaged'
And than I had to think hard, because 1 day is in two files; one for 10:00 and one for 14:00. How to put those in one object. After trail and error, I came with the following solution: Create a PowerShell CustomObject with only the date in it. Check the file’s if information is in it and than add this to the PSCustomObject:
# Add to Custom Object
if ($sessions.Datum -notcontains $timeStampDate) {
$sessions += [PSCustomObject] @{ `
"Datum" = $timeStampDate; `
}
}
# Add a new property to the object
if ($timeStampTimeNotation -eq '10:00') {
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 10:00' -Type NoteProperty -value $managedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 10:00' -Type NoteProperty -value $unmanagedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 10:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
} else {
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 14:00' -Type NoteProperty -value $managedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 14:00' -Type NoteProperty -value $unmanagedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 14:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
}
When you loop through all files, you get one PSCustomObject with all information in it. This is the complet loop:
# Loop through each file
Write-Host "`tLoop through all files"
$sessions = @()
foreach($file in $allFiles) {
Write-Host "`t`tRead file [$($file.Name)]"
# TimeStamp
[string]$timeStamp = $file.Name -replace "Citrix_Sessions_Daily-","" -replace ".xlsx",""
# TimeStampTime
[string]$timeStampTime = $timeStamp.Split("-")[-1]
[string]$timeStampTimeNotation = $timeStampTime + ':00'
# TimeStampDate
[datetime]$timeStampDate = Get-Date -Date ($timeStamp.Substring(0,$timeStamp.LastIndexOf('-')))
# Managed Divices Tab
[object]$managedDevices = Import-Excel $file.FullName -WorksheetName 'Managed'
# Unmanaged Divices Tab
[object]$unmanagedDevices = Import-Excel $file.FullName -WorksheetName 'Unmanaged'
# Add to Custom Object
if ($sessions.Datum -notcontains $timeStampDate) {
$sessions += [PSCustomObject] @{ `
"Datum" = $timeStampDate;
}
}
# Add a new property to the object
if ($timeStampTimeNotation -eq '10:00') {
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 10:00' -Type NoteProperty -value $managedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 10:00' -Type NoteProperty -value $unmanagedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 10:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
} else {
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 14:00' -Type NoteProperty -value $managedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 14:00' -Type NoteProperty -value $unmanagedDevices.count
$sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 14:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
}
}
Write-Host "`t...done"
And now I had to create a line chart of this information. I found this blog that helpt me “https://stackoverflow.com/questions/25331407/powershell-to-create-line-chart-from-excel“.
#region ----------------------------------------- Create Line Chart ------------------------------
$scvstatus = Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel' -PassThru
$xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]
$xl = new-object -ComObject Excel.Application
$wb = $xl.workbooks.add()
$ws = $wb.activesheet
$xl.Visible = $true
# Populate data onto worksheet
$sessions |ConvertTo-CSV -NoTypeInformation -Delimiter "`t"| c:\windows\system32\clip.exe
$ws.Range("A1").Select | Out-Null
$ws.paste()
$ws.UsedRange.Columns.item(1).numberformat = "dddd, mmm dd, yyyy"
$ws.UsedRange.Columns.AutoFit() |Out-Null
Create Chart
$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlLine
$ws.shapes.item("Chart 1").top=40
#endregion -------------------------------------- Create Line Chart ------------------------------