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.

2 thoughts on “Collect site collection information from Office 365

  1. Thank you very much for this post, it has made my work much easier. I have one question though, how many more properties can be added to the $temp variable?

    $temp | Add-Member -MemberType NoteProperty -Name “Title” -Value $item.Title;

    Is there a place where a list of all the possible NoteProperty -Name can be found?

    I am particularly interested in finding out if there are ‘Secondary accounts’ in Onedrive for business accounts in my organization.

    Thank you.

    • You can add as many properties as you want, with each add-member a property is added to an object. In my post about collections of custom objects, this principle is explained.
      With regards to your second question; if you want to know which properties an object in PowerShell has, you can use Get-Member to get a list of all functions and properties of an object.
      For the example in this post, if you want to know what properties the $item object has you can get these with this line:
      $item | Get-Member

Leave a Reply

Your email address will not be published. Required fields are marked *