Adding Email Column in Recent Workflow History report of Sitecore Powershell Extensions Module

If you are using Federated Authentication in Sitecore, you probably have noticed that user IDs of all AD users have random numbers in the IDs and you almost have to put an investigator cap on to find out the user if you click on the item. The DefaultExternalUserBuilder class in Sitecore creates a random user name of all external users. (Click here for more details). There are some instructions out there that you can use to customize the processor to use your own logic for user Ids. However, If you are using the Sitecore Powershell module and in need of a column or email of the user on the report then you can simply edit the Powershell script to add the desired column.

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

Elastic Beanstalk Extension to Add IIS Log Custom Field

If IIS web server is not capturing the client IP address then we can easily create a custom field "X-Forwarded-For" but these manual changes will be overridden once you deploy your code using AWS Elastic Beanstalk. You can use the Elastic Beanstalk extension and Powershell to programmatically add IIS Log custom field when the code is deployed to the server. Copy and Paste the below code in a config file. (Make sure code is indented correctly or else it won't work).

The below line will get all custom field from your IIS Website and store in an array called $customFields

$customFields = Get-ItemProperty 'IIS:\Sites\Default Web Site' -Name logfile.customFields.collection

The code below will check if the "X-Forwarded-For" custom field already exists. If it does not exist then it will add a new IIS Log custom field called "X-Forwarded-For".

if([string]::IsNullOrWhiteSpace(($customFields | where-object {$_.logFieldName -eq "X-Forwarded-For"}).logFieldName)) { New-ItemProperty 'IIS:\Sites\Default Web Site' -Name logfile.customFields.collection -Value @{logFieldName='X-Forwarded-For';sourceType='RequestHeader';sourceName='X-Forwarded-For'} }