SpreadSheetList

Script to list the Site Systems and their roles.

This script will put all the site systems and their roles in an Excel spreadsheet. This is the first script I will be putting in my BitBucket repository which you can get to from the menu above, please let me know if there are any issues with getting this script. OK, enough small talk, let’s look  this script. We start with the opening comments and parameters.

The comments pretty much explain what’s going on for the parameters, so next we have my usual log entry portion.

This is something I’m doing in all my scripts, it, by default, creates a log file in c:temp and names it the same name as the script, but with a .log extension. This file can be viewed real-time by cmtrace.exe when the script is running. I will usually change the loglevel parameter (from the first section) to 1 while testing and change to 2 when finished. In the case of this script, I got lazy on the error checking and that default will basically turn off logging.

Now for the meat.

This section starts of by logging that we are starting and then imports the Configuration Manager Module. I use the one from Microsoft which I got here. The nice thing about these is they work on 64 bit platforms.

Next, we get the SiteCode, use Push-Location to save the current directory on the stack and change to the SiteCode: location so the ConfigurationManager cmdlets will work.

Now we start creating the Excel spreadsheet.

Now, we get the roles, make sure to sort them alphabetically and only get unique names.

Now this section first fills out the header line with Server Name and Site Code and formats the text and fill color.

We finish off the header row with the list of Roles and end it with a column for the total number of roles/server.

Now that we have the header done, let’s start putting data in. We set some variables and gather data.

Now that we’ve got the data, fill out the sheet. The comments in the code should explain what is going on.

Now there’s just a little bit left to do. We need to put the totals on the bottom row so we know how many servers in each role.

The last thing we do is autofit the columns, put the drive back in the location and log that we are done!

When you have finished the script, you end up with a spreadsheet that looks something like this:

SpreadSheetList
SpreadSheetList

I hope you find this script useful!