One of my colleagues needed a PowerShell script to report on the SharePoint Site Collection Quotas in use on all sites, as well as how much of the site was being used.
Not being a PowerShell or SharePoint expert they asked for a second opinion, since I had an hour an a half of train journey they got a bit more than they expected.

The original Script:

 
$t = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false
$webApp = Get-SPWebApplication | %{$_.Sites} | Get-SPSite -Limit ALL
$webApp | fl Url, @{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={ foreach($qt in $t){if($qt.QuotaId -eq [int](($_.Quota).QuotaID)){$qt.Name; $tFound = $true}} if($tFound -eq $false){“No Template Applied”}$tFound=$false;}} >> c:\quotaoutput.txt
if($parent) {$webApp.Dispose(); $t.Dispose()}

First. Scripts are written by humans, for humans. There’s also a direct correlation between consistency of indenting and code quality. That monolithic block has to go.

$t = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false
 

$webApp = Get-SPWebApplication | %{$_.Sites} | Get-SPSite -Limit ALL
$webApp | fl Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} >> c:\PS\output.txt
 
if($parent)
{
$webApp.Dispose();
$t.Dispose()
}

At this point we can actually work out what happens. A collection of site collections are fetched, then we iterate through each of them, capturing bits of information, and then try to work out if the site has a quota and if so what it is called.

You might already have spotted the second item in there. If not here’s a hint, we’re getting a Collection of Site Collections.

Not a WebApplication, nor even a collection of them.

Point 2: Always use meaningful names.
I was slightly confused when I first read this as I assumed the names were meaningful. It took me a second, and a run through in debug mode, to convince myself otherwise.

So, let’s correct that name to something more meaningful. We’re in a simple scenario so we can use something short but descriptive like ‘AllSites’. While we’re there let’s also tidy up that $t to $templates


$templates = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false
 

$AllSites= Get-SPWebApplication | %{$_.Sites} | Get-SPSite -Limit ALL
$AllSites| fl Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} >> c:\PS\output.txt
 
if($parent)
{
$AllSites.Dispose();
$template.Dispose()
}

Now, that makes it a bit nicer to read. The mislabeled variable is a big hint to our next item, return values from cmdlets. Let’s look at this one line:

$AllSites= Get-SPWebApplication | %{$_.Sites} | Get-SPSite -Limit ALL

Let’s work through what this does. First we get all the WebApplications in the farm, then for each of those we get their sites, then for each of those sites we run the Get-SPSite -Limit All comand for that single site.
Wait, what?
Yup, we get a collection of all the sites and then we step through each and fetch it again. It’s almost surprising it works until you realise just how clever the PowerShell compiler is at converting types.
In fact, all three lines following are equivalent:
 
$sites = Get-SPWebApplication | % { $_.Sites} | Get-SPSite –Limit All
$sites = Get-SPWebApplication | % { $_.Sites}
$sites = Get-SPSite –Limit All

Why make things more complicated than they need to be? Let’s go with the last one.

$templates = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false

$AllSites = Get-SPSite -Limit ALL
$AllSites| fl Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} >> c:\PS\output.txt
 
if($parent)
{
$AllSites.Dispose();
$template.Dispose()
}

That’s better, but looking down the script there’s another item that has probably grabbed your notice. What the heck is $parent?
I have my suspicions it is orriginally from a 2007 PowerShell script, back when we were still using WSS 3, STSADM, PowerShell V1.0 and dinosaur attacks were listed on the risk register.
Either way this has no place here, if we’re executing in Strict mode (which wwe should be) then the script won’t even compile. If we’re not then it’ll never fire as $null evaluates to $false.
That’s probably for the best really as $AllSites, being a collection, doesn’t have a .Dispose() method and nor does $template.
Let’s just blow that away completely.

$templates = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false

$AllSites = Get-SPSite -Limit ALL
$AllSites| fl Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} >> c:\PS\output.txt

That’s better. Sleeker and more readable. On the other hand that .Dispose method should be ringing some bells, as you all know SharePoint is infamous for not properly releasing memory for the key components. Without the .Dispose method the objects will sit in memory until the PowerShell session ends.

In C# we’d have ‘using’ blocks but they don’t really exist in PowerShell. Here we use the pipeline, anything that’s run in a pipeline is disposed of at the end by default.

It just so happens that our $AllSites object is only used once after being declared, by rolling that into the pipeline we can make use of this wonderful feature and streamline our code further!

$templates = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false
 
Get-SPSite -Limit ALL| fl Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} >> c:\PS\output.txt

Of course, that doesn’t work because of the aforementioned crapness of SharePoint and it’s memory handling. I’m working on a longer post on how to deal with it but for now just remember to kill your sessions as soon as you can.

So, if you run this you get a nice text file with a rubbish name dumped out at the end. The format might look something like this:

Url : http://mysites:8080
Storage Used/1MB : 2
Storage Available Warning/1MB : 0
Storage Available Maximum/1MB : 0
Sandboxed Resource Points Warning : 100
Sandboxed Resource Points Maximum : 300
Quota Name : No Template Applied

Url : http://sharepoint
Storage Used/1MB : 7
Storage Available Warning/1MB : 0
Storage Available Maximum/1MB : 0
Sandboxed Resource Points Warning : 100
Sandboxed Resource Points Maximum : 300
Quota Name : No Template Applied

I’m liking the data but if you’ve got hundreds of sites that’s going to be a nightmare to go through. It just so happens we can make use of one of the lesser known, but highly awesome, PowerShell features to help us here.

As we all know the world floats on Excel and if we’re honest that’s where this data’s going anyway for us to sort. Let’s dump it out into a CSV file, now we could re-write the format-list statement to dump the stuff out in strings and then concatenate our hearts out.
Or we can change two things, swap fl out for Select and insert ConvertTo-CSV.


$templates = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false
 
Get-SPSite -Limit ALL| Select Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} | ConvertTo-CSV >> c:\PS\output-CSV.CSV

That turns our text output into something like this:

#TYPE Selected.Microsoft.SharePoint.SPSite
“Url”,”Storage Used/1MB”,”Storage Available Warning/1MB”,”Storage Available Maximum/1MB”,”Sandboxed Resource Points Warning”,”Sandboxed Resource Points Maximum”,”Quota Name”
http://mysites:8080″,”2″,”0″,”0″,”100″,”300″,”No Template Applied”
http://sharepoint”,”7″,”0″,”0″,”100″,”300″,”No Template Applied”
http://sharepoint/sites/CTHub”,”3″,”0″,”0″,”100″,”300″,”No Template Applied”
http://sharepoint/sites/sync”,”3″,”0″,”0″,”100″,”300″,”No Template Applied”
http://sharepoint/sites/TechNet”,”2″,”0″,”0″,”100″,”300″,”No Template Applied”
http://insightinvestment”,”4″,”0″,”0″,”100″,”300″,”No Template Applied”

A hell of a lot uglier but with a little Excel care and attention it’s usable!

What if we’re not going to be using Excel but we are going to be inspecting by eye, isn’t there a better format there? Well yes there is, you can use the ConvertTo-HTML option and that’ll turn the entire lot into a fully formed HTML file for you. With a modicum of genius and/or a particulary epic book by Don Jones you can add your own CSS and Jquery.

That works, but if this is going to be run more than once I don’t’ want my files overwriting the old ones, or even worse appending (as the script above will do, talk about confusing!)

Let’s slap a date stamp onto our output file:

$outputFolder = "C:\"
$path = $outputFolder + "Output-" + (Get-Date -Format "dd-MM-yyyy") + ".txt"
 
Yes, I'm a Brit, we will use the only sensible date format in this blog.

With a slight modification we're now here:

$outputFolder = "C:\"
$path = $outputFolder + "Output-" + (Get-Date -Format "dd-MM-yyyy") + ".csv"

$templates = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.quotatemplates
$tFound = $false
 
Get-SPSite -Limit ALL| Select Url,
@{n=”Storage Used/1MB”;e={[int]($_.Usage.Storage/1MB)}},
@{n=”Storage Available Warning/1MB”; e={[int](($_.Quota).StorageWarningLevel/1MB)}},
@{n=”Storage Available Maximum/1MB”; e={[int](($_.Quota).StorageMaximumLevel/1MB)}},
@{n=”Sandboxed Resource Points Warning”;e={[int](($_.Quota).UserCodeWarningLevel)}},
@{n=”Sandboxed Resource Points Maximum”;e={[int](($_.Quota).UserCodeMaximumLevel)}},
@{n=”Quota Name”; e={
        foreach($qt in $t)
        {
            if($qt.QuotaId -eq [int](($_.Quota).QuotaID))
            {
                 $qt.Name;
                 $tFound = $true
            }
        }
        if($tFound -eq $false)
        {
            “No Template Applied”
        }
    $tFound=$false;
    }
} | ConvertTo-CSV >> $path

We’ve turned a script that shouldn’t even run into something that’s more legible, probably faster (more to come on this I hope), more efficient and giving more useful results.

What haven’t we done? We haven’t touched on the, frankly brutal, RAM leaks which are the massive elephant in the room. This script will make your server cry, if it’s a really large farm then it might even impact the stability or performance of your CA box or wherever you run it. If you’ve got thousands of site collections I recommend running this out of hours with Task manager open and a hand hovering over Ctrl + C.

What next? Elephant hunting and SPAssignments