Collect site collection information from Office 365

Collecting users and groups from Office 365 is a relatively long running script (as mentioned in my previous post). Fortunately collection more general site collection information is easier. There is not very much data that can be collected, this is because a limitation in PowerShell’s access to SharePoint Online (before the upgrade to SharePoint 2013 this was not possible at all for SharePoint Online).

This script is a fairly short one but can be expanded in several ways (of which I will offer a few suggestions).

What it all comes down to can be summarized in this one-liner (don’t forget you will need to be connected to the Office 365 Service and the SharePoint Online service before running the scripts on this post):

$sitecollections = Get-SPOSite -Limit ALL -Detailed | Export-Csv -Path $outputPath -Delimiter ';' -NoTypeInformation;

In this case you may replace the variable $outputPath with the path where you want to have the results written to.

The following example is what I use at a client to monitor site collection storage quota’s (among with a few other things):

function Collect-SiteCollectionInfo
{
    Param(
    [Parameter(Mandatory=$true)]
    [ValidateNotNull()]
    [string]$outputFullFilePath,
    [Parameter(Mandatory=$false)]
    [switch]$selectSites,
    [Paremeter(Mandatory=$false)]
    [char]$csvSeparator = ';'
    )
    Write-Host "Collecting site collections";
    $sitecollections = $null;
    if($selectSites)
    {
        $sitecollections = Get-SPOSite -Limit ALL -Detailed | Out-GridView -Title "Select site collections from which to collect data." -PassThru;
    }
    else
    {
        $sitecollections = Get-SPOSite -Limit ALL -Detailed;
    }
    $itemArray = @();
    foreach($item in $sitecollections)
    {
        [double]$storageUsed = $item.StorageUsageCurrent;
        [double]$storageQuota = $item.StorageQuota;
        [double]$ratio = ($storageUsed / $storageQuota) * 100;
        [string]$percentage = $ratio.ToString("N1") + "%";

        $temp = New-Object System.Object;
        $temp | Add-Member -MemberType NoteProperty -Name "Title" -Value $item.Title;
        $temp | Add-Member -MemberType NoteProperty -Name "Url" -Value $item.Url;
        $temp | Add-Member -MemberType NoteProperty -Name "WebsCount" -Value $item.WebsCount;
        $temp | Add-Member -MemberType NoteProperty -Name "LastContentModifiedDate" -Value $item.LastContentModifiedDate;
        $temp | Add-Member -MemberType NoteProperty -Name "StorageUsageCurrent" -Value $item.StorageUsageCurrent;
        $temp | Add-Member -MemberType NoteProperty -Name "StorageQuota" -Value $item.StorageQuota;
        $temp | Add-Member -MemberType NoteProperty -Name "StoragePercentageUsed" -Value $percentage;
        $itemArray += $temp;

    }
    $itemArray | Export-Csv -Path $outputFullFilePath -Delimiter $csvSeparator -NoTypeInformation;
    Write-Host "Succesfully collected sitecollection information.`r`nFile saved at $outputFullFilePath" -ForegroundColor Green;
}

Now compared to the one-liner it may seem a bit overwhelming, but the output will be more relevant then before (where all values of all sites where returned).

For example if you pass the parameter -selectSites you will be able to select the sites from which you want to collect data (which I personally find the easiest way to choose which sites I want to receive details about), another option would be to replace the Out-GridView by a Where-Object and filter the results in that way.

In lines 25 through 28 I calculate the percentage of the assigned stored that is in use. If you are not interested in this number you can simply remove this from the script (in this casealso remove line 37 which will try to write the percentage), however I think that in most cases this is relevant and useful information. You could also add other calculations (for example a percentage of used Resources or the ammount of sub webs).

The part from line 30 through 38 does nothing more than adding only the data that is relevant for my report to the output. If you want to collect additional information (like Resource usage etc.) you can add this to the $temp object just like the other properties are added.