Charts in PowerShell-Generated Reports

So, as you may know, I have an ongoing hobby project called Creating HTML Reports in PowerShell. I'm working on an update for next year, and one of the things I've been looking at are embedded charts within the report.

Problem is, I don't know what people would actually chart. Now... I'm going to ask you for ideas, but you need to read this whole post before you go popping a comment in. Because there are some restrictions.

First, I'm not talking about historical data or trend reports. Those require a data store of historical data. If you're not using SQL Server for that (even free SQL Express), learn how. Excel is not your trend database, no matter how little learning it requires (and I bet if you added up all the time you've spent becoming an Excel jockey, you'd be shocked). Once you've got the data in SQL (even Express), you can use SQL Server Reporting Services (SSRS) to generate truly kick-butt reports with very little effort. Reports which can be scheduled and e-mailed. Truly, folks, this is worth spending time on - and I may make that my next ebook project.

Second, don't tell me "disk space." I know that one. Pie and stacked bar charts showing size/free space are a great idea. Got it. Anything else?

Third, I'm not talking about performance charts. PerfMon does those, and also, see my first point. PowerShell is not a performance monitoring tool. Operations Manager is. Oh, and it dumps data into SQL Server and you can use SSRS to report on it. If your company needs historical performance reports (and most probably do) and is to cheap to get you a real monitoring solution, consider taking drastic measures. I'm not suggesting you put Ex-Lax in the boss' coffee every time he asks you to re-create OpsMan on your own. He'd deserve it, and it might help, but I'm not suggesting it.

In keeping with point 3, that means I don't want suggestions like "charts showing network throughput." That's performance. I'm not suggesting such a thing wouldn't be useful, because I know it would be. I'm saying it's out of scope for this particular project. If you give me in-scope suggestions, I'll build you a tool. Fire off out-of-scope stuff and I'm just going to go build a kegerator for my beer instead.

SO... given those restrictions, what sort of data could you query from a computer (say, using WMI/CIM or something) that you'd want displayed in chart form? Anything?

About the Author

Don Jones

Don Jones is a Windows PowerShell MVP, author of several Windows PowerShell books (and other IT books), Co-founder and President/CEO of PowerShell.org, PowerShell columnist for Microsoft TechNet Magazine, PowerShell educator, and designer/author of several Windows PowerShell courses (including Microsoft's). Power to the shell!

5 Comments

  1. Don, I know where you are coming from. I have actually been in the position of having to develop a historical performance data collection and reporting solution back in the NT 4 and Win2k days. It wasn't very fun and much would've preferred an off the shelf and robust solution. But, as you know, you do what you have to sometimes.

    I was also the one that tweeted the network throughput performance data example simply because I have that real situation right now. In your earlier tweet, I thought you were crowd sourcing to see how people were leveraging powershell, not soliciting suggestions for a new tool that YOU would develop! You are more generous than I thought! 8^)

    In my scenario, by just using powershell and a command line tool called 'plink.exe' (used for issuing ssh commands), I have already written all of the code that interrogates the switch ports for stats and generates or appends to a raw csv every five minutes. I then have other functions that compute the I/O rates and generate a daily set of .NET based stacked bar charts. This is all to satisfy the need to have network performance data from the ports on a set of blade enclosures. Unfortunately, no tool is available by the blade vendor.

    The point I am at right now is to get these daily .png chart images into a daily HTML report. In other words, I need one more function like ‘ConvertTo-StylishHTML’! I completely agree with you that at some point, this will have to be loaded into an SQL database. This wouldn’t be very difficult since I have all of the well organized csv data ready to import. However, this small set of powershell functions were cobbled together in about a week or two because information was requested, well, yesterday. Again, just doing what one must to get an answer.

    But getting to your project, it seems that any information based on times series data is off limits since it makes sense to leverage SQL. But then I thought, if you are trying to "not re-invent the wheel" by charting perf data because SQL or some other tool would be better suited, then isn't using SQL, or any DB, the best way to drive data driven reports as a long term solution? Isn't almost all web based reporting driven from some back end database, so why powershell at all?

    I wanted to suggest something like event log data. Maybe certain types of errors per day broken down into a either a pie chart or summarized into hourly intervals as a stacked bar - and color coded based on error type. But then I realized that there are numerous tools that have tackled this problem also. Splunk comes to mind.
    Finally, I thought that you could gather configuration data from several systems and chart it, but I quickly realized: CMDB solutions, been there done that.

    My apologies for being long winded in this response, but you've given your fans an interesting conundrum. That is: if it is something worth charting and reporting, then it's likely something worth storing. And if you need to store it, then use a database.

    I'm stumped! I wish you luck in finding something that meets your criteria mostly because I know that I'll learn something from you, as I always do, by pushing the powershell envelope a little further.

    Cheers,
    Jim

    • For this project, time-based series are definitely off-limits. That's THIS project. And I'm having just as much trouble coming up with useful scenarios, so I'm probably going to move on to the NEXT project... which will probably be how to set up a no-cost reporting infrastructure using PowerShell, SQL Server, and SQL Server Reporting Services. I know most admins use Excel, but I think that's because they don't realize how much better and easier a SQL-based infrastructure would be, even if it's just Express running on their own machine. And, I'm going to write some tools to make it Super E-Z.

      I think you've hit it on the head, in a better expression that I'd come up with: If it's worth charting, it's worth storing, and you should use a database. Not Excel, mind you. So... that's going to be this winter's project.

  2. How about % of memory used by processes? It's not extremely different than diskspace but you could start with a basic chart showing percentage used of total physical memory, something like:

    $totMemInUse = (Get-CimInstance -ClassName Win32_Process -Property Name, WorkingSetSize | Select Name, WorkingSetSize | Measure-Object WorkingSetSize -Sum).Sum
    $totMem = (Get-CimInstance -ClassName Win32_PhysicalMemory -Property Capacity | Select Capacity).Capacity
    $percUsed = ($totMemInUse / $totMem) * 100

    Then it could be given additional detail showing the Name of the processes in use with the KB in use for that particular process.