Using PowerShell To Actually Do Good Things?

Yes, actually, believe it or not you can. One of my harping points of PowerShell functionality is that everyone starts by building scripts to install SharePoint. A good learning exercise I suppose, but check CodePlex and you’ll understand why I think re-inventing the wheel using PowerShell is pointless. “So, Mr. SharePoint Bartender, what do you use PowerShell for in your SharePoint environments?” Glad you asked.

Let’s say you run a SharePoint installation with a lot of web apps and sites. I mean a lot. If you do, have you ever been asked “Hey, can you give me the top 25 sites in the SharePoint farm? In a format I can understand? And present to management to help out with your raise next year?” (At this point, you should be shaking your head yes, and remembering where you linked to this blog site).

Three parts: 1. A SharePoint list with some custom columns to store the data. 2. On the same site as the list, deploy the SharePoint Chart WebPart, using the list as your datasource (I’m a huge fan of the giant doughnut chart, it certainly makes its presence known). 3. You need a PowerShell script you can run as a scheduled task that goes and gets this data and populates the list.

What’s that? Sure, I guess I can do that for you as well…

#Load the SharePoint cmdlets if they are missing
if (!(Get-PsSnapin | Where-Object {$_.Name -match "Microsoft.SharePoint.PowerShell"}))
  Add-PsSnapin Microsoft.SharePoint.PowerShell

# This function will execute a 'Dispose" method on any variables that have a .Dispose() method
function Dispose-All {
	Get-Variable -exclude Runspace | Where-Object {$_.Value -is [System.IDisposable]} | 
	        Foreach-Object {$_.Value.Dispose()

function Gather-Data{
Param (
# Get all the webapps in the farm, execept for the MySites, because no one cares about MySites (you may need to change the URL)
$sites = (Get-SPWebApplication | Where-Object{$_.URL -notlike "http://mysite*"} | select -expandproperty Sites | ForEach-Object{Get-SPSite $_.url})
ForEach($site in $sites){
	$web = (Get-SPWeb $site.URL)
	$monthUsage = ($web.GetUsageData("url", "lastMonth") | sort 'Total Hits')
	If($monthUsage -ne $null){$largeUse = $monthUsage[-1]
	$tHits = ($monthUsage | Measure-Object 'Total Hits' -Sum | Select -expand Sum)
	$usageData = @{
		waURL = $site.WebApplication.URL
		URL = $site.URL
		Title = $web.title
		Day30 = $tHits
		MostActive = $largeUse.Page
		MostActiveCount = $largeUse.'Total Hits'
	New-Object PSObject -Property $usageData
	$monthUsage = $null
	$tHits = $null


function List-Output{
	$newItem = $spList.Items.Add()
	$newItem["Web Application"]="$($_.waURL)"
	$newItem["Site Collection URL"]="$($_.URL)"
	$newItem["Site Title"]="$($_.Title)"
	$newItem["Last 30 Days Access Count"]="$($_.Day30)"
	$newItem["Most Active File"]="$($_.MostActive)"
	$newItem["File Access Count"]="$($_.MostActiveCount)"

$spList = (Get-SPWeb -identity "").Lists["SiteReport"]
ForEach($killItem in $spList.items){
$spList = (Get-SPWeb -identity "").Lists["SiteReport"]

Gather-Data | sort-object MostActiveCount -descending | select-object -first 25 | %{List-Output $_}

What this script does is pull the Usage Stats (you know, from the Usage DB that no one uses but takes up a ton of SQL server disk space) for the sites, drops the Top 25 hitters into the SharePoint list so you can point the Chart Web Part at that list as a datasource.

***** Some assembly required *****
Create a custom list with the following columns using the column type listed:
Web Application (Single line of text)
Site Collection URL (Single line of text)
Site Title (Single line of text)
Last 30 Days Access Count (Number)
Most Active File (Single line of text)
File Access Count (Number)

Again, use the built-in Chart Web Part and point to this list as a datasource. The script should run once a day at least, so the data will always be fresh. Once it’s complete, amaze and impress your friends with your giant doughnut chart showing the top 25 accessed sites.