Using Sitecore PowerShell to generate a report on last updates to your content

Recently I had to build a report in Sitecore to show the latest updates done on the content tree of a Sitecore instance and had to determine which users made those changes. One of the other important things on this report was the ability to have content authors generate the report from the Content Editor.
If you ever need to perform such actions, the Sitecore Powershell Extensions (SPE) module is probably your best bet.

In case you are wondering, the SPE module provides an easy way to create scripts and customize the Content Editor interface in a way that, for example, on a click of a button you run your custom script. You can read more about how to add a button to the Content Editor and run your scripts in this post, which offers a good example of how to do that.

Also, if you are following this approach of creating a Module with a Powershell Script library, you can easily deploy these changes to your various environments (QA, UAT, PROD, etc) once you are done with your development.
To do that you can create a Sitecore package with your module. For example, if I had created a module called Reports I would see it on this path: /sitecore/system/Modules/PowerShell/Script Library/Reports. So to deploy you can create a regular Sitecore package, adding your module and installing on whatever environments you want. Once you do that, just open your script with Powershell ISE, go to the Settings tab – Rebuild All and click on the arrow to select “Sync Library with Content Editor Ribbon”:

But before you start building this report you should know that there is a report you can use that comes with out of the box SPE; it generates a report of the last items updated before and after a specific date. You can check them out here:
/sitecore/system/Modules/PowerShell/Script Library/Content Reports/Reports/Content Audit/Items last updated after date

I myself used this script as a starting point but in my case I needed some specifics on my report. Here are some of the things I needed to build:
a) I wanted to provide the ability for the user to select an item as a root for the report – for example, the home item of a specific site.
b) I wanted to provide the ability for the user to select which language they wanted to use on the report.
c) I wanted to show only Sitecore items with presentation renderings associated with them. So components were not desirable on the report.
d) I wanted to list the user’s full name as opposed to the username.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

Ok – enough with introductions!

The first thing I defined was a function and a cmdlet. The function I used to get the user full name based on its Sitecore username and the Cmdlet was used to validate whether the item had any layouts associated with them.

Here is how they were defined. You can review the inline comments to understand what’s being done:

#gets the user full name based on the username
function Get-UserFullName($username){

#Gets the user based on the username i.e. sitecore\diego should return the full name for the user diego
$user = Get-User -Identity $username

if($user.Profile.FullName){
#returns the fullname
return $user.Profile.FullName
}else{
#full name is not available on the user. Returns the username instead
return $username
}
}

#CMDLET to check whether an item has layout or not. Used to determine whether it's a component or not
function Has-Layout{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true, Position=0)]
[Sitecore.Data.Items.Item]$Item
)

$layout = Get-Layout -FinalLayout -Item $Item

#Get-Layout returns the layout item. If null means that no presentation is set on the FinalLayout
if($layout){
return $Item;
}

return "";

}

The next thing you want to use is the Read-Variable cmdlet. This is personally one of my favorite features of this module. It provides an ability to create dialogs, define fields and add validation to the form in an easy way.
In my case I created two parameters I wanted to set on the dialog and then just called the Read-Variable based on those parameters.
Here’s how:

$settings = @{
Title = "Report Filter"
Width = "600"
Height = "600"
OkButtonName = "Proceed"
CancelButtonName = "Abort"
Description = "Filter the results for items last updated"
Parameters = @(

@{ Name = "root"; Title="Choose the report root";
Source="DataSource=/sitecore/content/MySite&DatabaseName=master&IncludeTemplatesForDisplay=My Template&IncludeTemplatesForSelection=My Template";
editor="droptree"; Mandatory=$true;},
@{ Name = "language"; Title="Pick One Language";
Source="DataSource=/sitecore/system/Languages&DatabaseName=master";
editor="droplist"; Mandatory=$true;}

)
}

$result = Read-Variable @settings
if($result -ne "ok") {
Exit
}

If you are familiar with the Read-Variable cmdlet you know that the parameters defined will become variables. So in my case I had the parameters “root” and “language.” After the user submitted the form those variables would be set with values. In this case $root would save the root item in the content tree where the report would run against and $language would save the name of the language we would use to filter the items collection.

Now we need to get the items collection based on these filters. To do that you can use the following command:

$items = Get-ChildItem -Language $language.Name -Path $root.ProviderPath -Recurse | Where-Object { (Has-Layout $_) -ne "" } | Sort-Object __Updated -descending

Notice that when I’m retrieving the items I’m specifying the language name and the root path as well as getting the item’s descendants and filtering them based on whether they have a layout or not (calling my custom Has-Layout cmdlet).

The last part is binding the item’s collection into a ListView. To do that you can define the chart columns and bind using the Show-ListView cmdlet. Notice on the code below how the Get-UserFillName function is being called to handle the proper user name.

$props = @{
Title = "Items Last Updated Report"
InfoTitle = "Items last updated"
InfoDescription = "Lists all items last updated "
PageSize = 25
}

$items |
Show-ListView @props -Property @{Label=&quot;Name&quot;; Expression={$<em>.DisplayName} },
@{Label=&quot;Path&quot;; Expression={$</em>.ItemPath} },
@{Label=&quot;Updated&quot;; Expression={$_.__Updated} },
@{Label=&quot;Updated by&quot;; Expression={Get-UserFullName($_.&quot;__Updated by&quot;) } }
@{Label=&quot;Created by&quot;; Expression={$_.&quot;__Created by&quot;} }

Putting all the pieces together we would get the following script:

<# .SYNOPSIS Lists all items based on a selection and the last updated #>

#gets the user full name based on the username
function Get-UserFullName($username){

#Gets the user based on the username i.e. sitecore\diego should return the full name for the user diego
$user = Get-User -Identity $username

if($user.Profile.FullName){
#returns the fullname
return $user.Profile.FullName
}else{
#full name is not available on the user. Returns the username instead
return $username
}
}

#CMDLET to check whether an item has layout or not. Used to determine whether it's a component or not
function Has-Layout{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true, Position=0)]
[Sitecore.Data.Items.Item]$Item
)

$layout = Get-Layout -FinalLayout -Item $Item

#Get-Layout returns the layout item. If null means that no presentation is set on the FinalLayout
if($layout){
return $Item;
}

return "";
}

$language = $null

$database = "master"

$root = Get-Item -Language $language.Name -Path "$($database):\content\MySite\Home"

$settings = @{
Title = "Report Filter"
Width = "600"
Height = "600"
OkButtonName = "Proceed"
CancelButtonName = "Abort"
Description = "Filter the results for items last updated"
Parameters = @(

@{ Name = "root"; Title="Choose the report root";
Source="DataSource=/sitecore/content/MySite&DatabaseName=master&IncludeTemplatesForDisplay=My Template&IncludeTemplatesForSelection=My Template";
editor="droptree"; Mandatory=$true;},
@{ Name = "language"; Title="Pick One Language";
Source="DataSource=/sitecore/system/Languages&DatabaseName=master";
editor="droplist"; Mandatory=$true;}

)
}

$result = Read-Variable @settings
if($result -ne "ok") {
Exit
}

#$root = Get-Item -Language $language.Name -Path (@{$true="$($database):\content\MySite\Home"; $false="$($database):\content\MySite\Home"}[(Test-Path -Path "$($database):\content\MySite\Home")])

$items = Get-ChildItem -Language $language.Name -Path $root.ProviderPath -Recurse | Where-Object {  (Has-Layout $_) -ne "" } |  Sort-Object __Updated -descending

if($items.Count -eq 0) {
Show-Alert "No items found for the path provided"
} else {
$props = @{
Title = "Items Last Updated Report"
InfoTitle = "Items last updated"
InfoDescription = "Lists all items last updated "
PageSize = 25
}

$items |
Show-ListView @props -Property @{Label=&quot;Name&quot;; Expression={$<em>.DisplayName} },
@{Label=&quot;Path&quot;; Expression={$</em>.ItemPath} },
@{Label=&quot;Updated&quot;; Expression={$_.__Updated} },
@{Label=&quot;Updated by&quot;; Expression={Get-UserFullName($_.&quot;__Updated by&quot;) } },
@{Label=&quot;Created by&quot;; Expression={$_.&quot;__Created by&quot;} }

}
Close-Window