I’ve written previously about my frustration with reporting in PowerShell – how I see admins struggle with ugly, low-level COM code to manipulate Excel spreadsheets, just so they can get nice-looking reports with a degree of automation.
Enough.
The right thing to do is put your data in SQL Server, and use SQL Server Reporting Services to generate awesome looking reports, complete with charts and graphs. With the right setup, you can completely automate data collection, report generation, and delivery. And it doesn’t have to cost a single dime. Plus, the learning curve isn’t too steep, and the skills you’ll learn along the way will be massively beneficial to you over the long haul – far more so than the time sunk into becoming an Excel jockey.
So I’ve written a little book about it, which you’ll find on at https://powershell.org/ebooks, entitled Making Historical and Trend Reports in PowerShell. Unlike my earlier book on HTML reporting, which was mainly around producing inventory reports, this one’s specifically designed to make reports based on collected-over-time data, like disk utilization, performance, and so on. And I’ve bundled in a PowerShell module that should make this easy, insulating you from 99% of the SQL Server-related stuff.
Right now (November 2012) I’m looking for folks to test stuff out and let me know (via comments here) if you find any problems. I want to make sure that what I’ve got in here works and is understandable. Final publication is scheduled for January 2013, after which I’ll start taking suggestions for stuff to add to the book.
I’d like to test drive it, if you want me to.
Johan
It’s up at PowerShellBooks.com – feel free to grab it!
OK, I got though it! I plan to put this to practical use almost immediately! Thanks so much for putting this out there.
What type of feedback are you most interested in hearing? Do you want high level impressions on the material (btw, extremely useful!!) or more details about little bumps along the way? I don’t want to waste your or my time if you are looking for something specific.
All in all, the process you lay out works very well, but I have a few things I can possibly suggest. I took some notes as I went through, so just let me know I will post what I can.
Major bugs, first and foremost. Beyond that, anything you’d like to offer. I can’t promise to put it all into action, but it’ll all be considered.
I can’t say enough about the applicability of Month of Lunches. I’m expecting that the Reporting book will be quite useful too. Thanks for sharing.
These aren’t major issues, but just some small hurdles I encountered. My apologies if they seem nitpick -ish.
In the SQLReporting.psm1 module, within the Save-ReportData function, on line 32 you call the Test-Database function with the debug option. Obviously, this creates an issue if someone were to use this as a scheduled task. Personally I read through the code so I understood it. Once I did, I removed the debug parameter entry. I guess anyone reading this material should have the expertise to do the same, but maybe a short explanation would help readers. If you think it is important to leave the –debug directive alone, especially for the first iteration when all of the table properties are first defined, then maybe just remove line 103 where Write-Debug is called for each iteration.
Under the “Finding a counter” section, the “System” performance object is listed as the only one on your machine that you can find that is considered ‘SingleInstance’. But you know there are many others like cache, memory, TCPv4, Server, IPv4, etc. You mention that you won’t use it because you want to start with the hard stuff, i.e. MultiInstance, but ‘IPv4’ is Single Instance. I was a little confused at this part.
Under the “Accessing Report Manager†section, when I launched the Reporting Services Configuration Manager, I did not have a Report Manager URL available. I needed to first create a local Report Server Database. I followed the instructions at http://msdn.microsoft.com/en-us/library/ms157300.aspx I then configured the Web Service URL, which then allowed me to configure the Report Manager URL. It may be helpful to mention that these steps are required before proceeding to access the Report Manager via the browser. Maybe a new section just prior ‘Accessing Report Manager’ called: ‘Creating a Local Report Server Database’.
Under the ‘Building a Report’ section, the third screen shot is the Data Source Properties dialogue. The connection string shows ‘server’ and ‘database’. However, I had trouble with those settings. I used the Build function button and it generated a string like this: Data Source=localhost;Initial Catalog=Powershell This worked for my system.
Again, these are just my 2 cents. Like all of the PowerShell content that you and Jeff Hicks produce, I think this ebook will be a huge help and an eye opener to many.
The -Debug thing was more because this is beta and I’m still debugging ;). I’ll take it out in the final.
On my machine, IPv4 doesn’t show as SingleInstance. System is literally the only one.
When I installed SQL Server Express, and I think I mentioned this in the walkthrough for that, I selected the option to configure Reporting Services. That’s why I didn’t have to take the extra step of creating a database like you did.
The connection string syntax you use is a legal alternate, but so is the one I used. My machine accepts either, and ConnectionStrings.com lists both as legitimate alternatives. I’m wondering if maybe something else was wrong with the connection string you tried first?