Get a list of site collections created yesterday

Inspired by a question asked on the TechNet forums, Get site collections created on the previous day. Let's look at how can we list all the site collections created yesterday in a SharePoint farm.

The SPWeb.Created property stores the date that a site was created. In the case of a site collection you can look at the creation date of the root web (SPSite.RootWeb), as this is created as part of your new site collection. Therefore the Created property of the root web, i.e. the SPSite.RootWeb.Created property, contains the creation date for the site collection.

For example, to see the creation date of all the site collections in the farm:

Get-SPSite -Limit All | select Url, {$_.RootWeb.Created}

Which outputs something like this:

Url                                                         $_.RootWeb.Created
---                                                         ------------------
http://intranet.example.com                                 2/12/2013 4:06:01 PM
http://intranet.example.com/Search                          2/12/2013 4:12:09 PM
http://intranet.example.com/Projects/Banana                 3/14/2013 11:03:12 AM
http://intranet.example.com/Projects/Cayenne                2/27/2013 9:57:12 AM
http://intranet.example.com/Projects/Cherry                 4/21/2013 3:42:12 PM
http://intranet.example.com/Projects/Ghost                  4/20/2013 2:15:12 PM
http://intranet.example.com/Projects/Pasilla                6/16/2013 1:08:12 PM
http://intranet.example.com/Projects/Scotch                 5/18/2013 10:48:12 AM
http://intranet.example.com/Projects/Serrano                6/12/2013 9:26:12 PM

To get only the site collections created yesterday, we'll first need to figure out when yesterday was:

$Today = [DateTime]::Today
$Yesterday = $Today.AddDays(-1)

The DateTime.Today method returns today's date at midnight (i.e. 12:00 AM or 00:00:00). We assign this to a variable called $Today and then use the DateTime.AddDays method to subtract a day from $Today, giving us yesterday's date (also at midnight). By assigning this to the variable $Yesterday, we now have two date variables with a difference between them of 24 hours.

Using these variables we can then filter our site collections and return only those whose root web were created during the hours between $Yesterday (including midnight) and $Today:

Get-SPSite -Limit All | where {$_.RootWeb.Created -ge $Yesterday -And $_.RootWeb.Created -lt $Today} | select Url, {$_.RootWeb.Created}

If we assume today is June 17, 2013 then using our example the script would have the following output:

Url                                                         $_.RootWeb.Created
---                                                         ------------------
http://intranet.example.com/Projects/Pasilla                6/16/2013 1:08:12 PM

The full script is:

$Today = [DateTime]::Today
$Yesterday = $Today.AddDays(-1)
Get-SPSite -Limit All | where {$_.RootWeb.Created -ge $Yesterday -And $_.RootWeb.Created -lt $Today} | select Url, {$_.RootWeb.Created}

Running this in an elevated SharePoint Management Shell will return a list of site collections created yesterday.

SharePoint 2007 Bonus

Since SharePoint 2007 doesn't have any out of the box cmdlets, we'll need to get the list of site collections the long way. This also works in SharePoint 2010 and SharePoint 2013, displaying the same result as the script above.

$Today = [DateTime]::Today
$Yesterday = $Today.AddDays(-1)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$websvcs = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
$webapps = @()
foreach ($websvc in $websvcs) {
	foreach ($webapp in $websvc.WebApplications) {		
		$webapp.Sites | where {$_.RootWeb.Created -ge $Yesterday -And $_.RootWeb.Created -lt $Today} | select Url, {$_.RootWeb.Created}
    }
}