Powershell

Excel Charts from PowerShell data

Looking to make Reports more sharing friendly?

First start with an additional module known as “importExcel” (the link to the psgallery details is there)

Truth be told you can also Export to excel as well even though the name doesn’t give that away.

Script Time!!!! (I actually had to type this because I say this in real life in response to real challenges and tasks.

Here is a script that takes the performance counters from a machine, note this is different depending on the OS and the hardware. you can explore performance counters inside of the performance monitor tool searchable inside the start menu.

<#Script to gather Performance Data From PCs#>

#load machines for Testwrite-host "Gathering Worstation names" -foregroundcolor Yellow$Workstations= Get-Content 'C:\Performance Data\workstations.csv'$computers = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $Workstations -ErrorAction SilentlyContinue#gather Memory SamplesWrite-Host "Gathering Memory Data Samples" -foregroundcolor Cyan$memoryData=Get-Counter -Counter "\memory\available mbytes" -SampleInterval 1 -MaxSamples 10 -ComputerName $Workstations -ErrorAction SilentlyContinue -ErrorVariable memerror #gather Processor SamplesWrite-Host "Gathering Processor Data Samples" -foregroundcolor Cyan

$processorData=Get-Counter -Counter "\Processor(_Total)\% Processor Time" -MaxSamples 10 -SampleInterval 1 -ComputerName $Workstations -ErrorAction SilentlyContinue -ErrorVariable ProcErr$processorReport=$null$memoryReport=$null

$date=Get-Date -Format MM.dd.hh.mm.sstt                                              

foreach ($computer in $computers){$processorReport=$null$memoryReport=$null


#write the computernameWrite-Host $computer.Name -ForegroundColor Yellow$TotMEM='{0:0}'-f  ($($computer.TotalPhysicalMemory/1mb))

#Process Data Report$processorReport+=$processorData.countersamples | Where-Object -Property path -Match $computer.Name | Select-Object @{name="Time";e={$_.timestamp| Get-Date -Format hh:mm:sstt}},@{name="Processor %";e={$_.cookedvalue}},@{name="Model";e={$computer.Model}},@{name="Computer";e={$computer.name}}




$memoryReport+=$memoryData.countersamples | Where-Object -Property path -Match $computer.Name | Select-Object @{name="Time";e={$_.timestamp| Get-Date -Format hh:mm:sstt}},@{name="MemoryUsed";e={$TotMEM-$_.cookedvalue}},@{name="Model";e={$computer.Model}},@{name="Computer";e={$computer.name}}

$MemChart = New-ExcelChartDefinition -XRange "Table1[Time]"  -YRange "Table1[MemoryUsed]" -ChartType Line  -Width 800 -Height 350  -Title "Memory Utilization" -SeriesHeader 'Memory(MB)' -XAxisNumberformat 'h:mm:ss AM/PM'$ProcChart=New-ExcelChartDefinition -XRange "Table2[Time]"  -YRange "Table2[Processor %]"   -ChartType LineStacked  -Width 800 -Height 350  -Title "CPU Usage" -SeriesHeader 'Percent Utilized(Ghz)'  -XAxisNumberformat 'h:mm:ss AM/PM'$memoryReport   |   Export-Excel "C:\Performance Data\Reportdata_$($computer.Name).xlsx" -WorksheetName MemoryUsage  -TableStyle Medium9 -AutoSize -Append -ExcelChartDefinition $MemChart  $processorReport| Export-Excel "C:\Performance Data\Reportdata_$($computer.Name).xlsx" -workSheetName ProcessorData  -TableStyle Medium17 -AutoSize -Append -ExcelChartDefinition $ProcChart}
This is a complex one and one of my more favorite script where a lot of PowerShell magic is used.
Mind you the input is a simple csv with a list of names. explore the different elements of get-counter using get-help -full or at least get-help -examples.

2 thoughts on “Excel Charts from PowerShell data”

  1. You should probably note that you are leveraging the ImportExcel module (along with explicitly loading it into your script), as it may confuse readers if they didn’t read your previous post.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.