I will be adding a column to a Recent Workflow History report.
Go to Recent Workflow History located under System>Modules>
Click on the report and then in the scripting field, add the below-highlighted lines to an existing script. we are simply using GetUser() method of System.Web.Security.Membership class to get all user membership information and that will include all properties that we need. The Get-User class only returns a few properties and does not include the Email.
Import-Function -Name Invoke-SqlCommand
$database = Get-Database -Name "master"
$connection = [Sitecore.Configuration.Settings]::GetConnectionString($database.Name)
$historyDays = [datetime]::Now.AddDays(-14)
$parameters = @{
"date" = $historyDays
}
$query = "SELECT DISTINCT ItemID, Language FROM WorkflowHistory WHERE Date > @date"
$itemIds = Invoke-SqlCommand -Connection $connection -Query $query -Parameters $parameters | Select-Object -ExpandProperty "ItemId"
$reportItems = @()
foreach($itemId in $itemIds) {
if($itemId) {
$selectedItem = Get-Item -Path "master:" -ID ([Sitecore.Data.ID]::Parse($itemId))
$workflowEvents = $selectedItem | Get-ItemWorkflowEvent | Where-Object { $_.Date -gt $historyDays -and $_.OldState -ne $_.NewState }
foreach($workflowEvent in $workflowEvents) {
$previousState = $null
$currentState = $null
if($workflowEvent.OldState) {
$previousState = Get-Item -Path "master:" -ID $workflowEvent.OldState
}
if($workflowEvent.NewState) {
$currentState = Get-Item -Path "master:" -ID $workflowEvent.NewState
}
$userInfo = Get-User -Id $workflowEvent.User
$user = [System.Web.Security.Membership]::GetUser($userInfo.Name)
$comments = $null
if($workflowEvent.CommentFields) {
$comments = $workflowEvent.CommentFields["Comments"]
}
$reportItem = [pscustomobject]@{
"Email" = "$($user.Email)"
"User" = "$($user.UserName)"
"Date" = $workflowEvent.Date
"OldState" = $previousState.Name
"NewState" = $currentState.Name
"Comments" = $comments
"ID" = $selectedItem.ID
"Name" = $selectedItem.Name
"Icon" = $selectedItem.Appearance.Icon
}
$reportItems += $reportItem
}
}
}
$reportProperties = @{
Property = @("Icon","Name","Date","OldState","NewState","Email","User","Comments")
Title = "Recent workflow history"
InfoTitle = "Recent workflow history"
InfoDescription = "View the most recent workflow history."
}
$reportItems | Show-ListView @reportProperties
Close-Window