In my last entry on new powershell API samples, I mentioned providing some tips and tricks for automated reporting from your WSUS server in conjunction with Excel.
This will help walk you through creating a trending report for various interesting aspects of your deployment, and displaying it graphically in Excel. There will be some “solution left as an exercise for the reader” aspects, but I’ll point you in the right direction through the pitfalls that I found tricky. See the attachment to this post for a sample chart.
First, a quick reminder on the location of the script repository for our PowerShell API samples: ttp://www.microsoft.com/technet/scriptcenter/scripts/sus/server/default.mspx?mfr=true. We don’t intend these to necessarily be wonderful examples of the best use of PowerShell, but more of a handy way to introduce you to the API and give a headstart on creating your own solutions.
One of the most common requests we’ve had is around reporting information only for approved updates. That lead to a sample for server status for just approved updates: http://www.microsoft.com/technet/scriptcenter/scripts/sus/server/susvms02.mspx
As you’ll see in the sample output, it has a simple one line CSV style output which is perfect for importing into your favorite tools. It also includes both the server name and date, so you can easily order or filter the results.
Naturally you’ll need to start by installing PowerShell, and saving the script to a .ps1 file. I recommend starting this sample running on your server – you can modify the sample to connect to a remote server via a console only install, but I’d suggest limiting the number of moving pieces until you get it all working end-to-end once. Make sure you can run the .ps1 file from the command line. You may need to modify your script execution environment options. (That’s one of those exercises for the reader.)
Next, create a scheduled task to run your script every night and append the output to a file. Below is a sample command line in the Scheduled Tasks. Make sure to try the command line from a cmd prompt to make sure you have your paths and access right – it’s annoying to wait a couple of days for the scheduled task to run and discover you botched the command line.
%windir%\system32\WindowsPowerShell\v1.0\powershell.exe -command C:\WsusScript\ServerStatusForApprovedUpdates.ps1 >> C:\ReportingData\ServerStatusForApprovedUpdates.csv
Now wait a couple of days, and you should start getting some nice output. Here’s some sample data to give you a headstart:
Once you’ve got that in a .CSV file, start Office Excel 2007. Sorry if you’re on an older version – that’s the version I’m working on. I suspect you can get all of this done on an older version, but I haven’t gone back to verify.
Look across the menu options across the top, and choose the Data tab. The third option of “Get External Data” is “From Text.” This will give you a browser where you point to the .csv file you’re writing via the scheduled task. Excel should recognize the file and set the right defaults for almost everything. The fancy bits come after you click finish. You’ll want to do 2 things before finishing.
- Excel asks where you want to insert the data – choose a spot down about 20 rows, so you can leave spot for a graph at the top.
- Click Properties. Here you uncheck “Prompt for file name on refresh“, check “Refresh data when opening the file“, and “Overwrite existing cells with new data, clear unused cells.”
Now, every time you open the Excel file you’ll get the very latest data from your automatically generated file.
Next, go to the “Insert” tab, and add a line graph with markers (not stacked). Move the graph over into the empty space you reserved, and then choose the Design tab menu option for Select Data. Select the area where your data is coming in. You’ll also want to give names to the Legend Entries based on the columns of the imported data, and remove any columns you’re not interested in. You may also want to create two separate graphs – one for computers, and one for updates – because you’re likely to have significantly different total numbers of computers and updates and would want the data clearly separated and scaled appropriately.
Voila! Save the spreadsheet, and open it again in a couple of days. Post here if I’ve missed a step, to brag if you’ve gotten your own solution working, or have any nifty ideas for enhancing this for others.