Export Publish Transaction data to Excel

I recently got the question "how long does publishing a page take". This is an easy question which is not so easy to answer.
To at least have data to base an indication on, I created a PowerShell script that retrieves data from the CME, using the Core service module, and store this data in a CSV file.
That file can then be imported into Excel and manipulated further, for instance to get minimal, maximal and average publishing times.

The script accepts several parameters.
The important one is -delta which determines the number of days to retrieve information for. $delta is used to calculate the start date.
Other parameters allow you to set the field delimiter, default ';' and decimal separator, default ','

It uses the command Get-TridionPublishTransaction -StartDate $startDate to return all transactions created between $startDate and now.

The of output of Get-TridionPublishTransaction is converted into an array of objects, $results , using

$pts.ForEach({
  $pt = $_
  $pt.Items.ForEach({
  $it = $_
  $type = getType($it.IdRef)
$details = @{
    'Transaction Date' = $pt[0].StateChangeDateTime.ToString("dd/MM/yyyy hh:mm:ss")
    'Transaction Id' = $pt.Id
    'Item Type' = $type
    'Item Id' = $it.IdRef
    'Render Time' = ($pt.RenderingTime.TotalSeconds -replace'\.',$decimalSeparator)
    'Resolving Time' = ($pt.ResolvingTime.TotalSeconds -replace'\.',$decimalSeparator)
    'Excecution Time' = ($pt.TotalExecutionTime.TotalSeconds -replace'\.',$decimalSeparator)
    'Status' = $pt.State
  }
  $results += New-Object PSObject -Property $details
  })
})

Finally this array is written to the csv file using

$results |
Select-Object 'Transaction Date', 'Transaction Id', 'Item Type', 'Item Id', 'Render Time', 'Resolving Time', 'Excecution Time', 'Status' |
export-csv -Path $outputFile -NoTypeInformation -Delimiter $delimiter

The resulting csv file will look like this:

"Transaction Date";"Transaction Id";"Item Type";"Item Id";"Render Time";"Resolving Time";"Excecution Time";"Status"
"23-05-2018 03:35:41";"tcm:0-34440-66560";"Structure Group";"tcm:6-69216-4";"0,008";"";"9,05";"Success"
"23-05-2018 03:35:01";"tcm:0-34439-66560";"Page";"tcm:6-213495-64";"0,01";"";"7,804";"Success"
"23-05-2018 03:29:25";"tcm:0-34438-66560";"Structure Group";"tcm:6-69212-4";"0,008";"";"0,007";"Warning"
"23-05-2018 03:27:35";"tcm:0-34436-66560";"Page";"tcm:6-213475-64";"0,009";"";"3,983";"Success"
"23-05-2018 01:25:45";"tcm:0-34422-66560";"Page";"tcm:6-213478-64";"0,012";"";"5,62";"Success"
"23-05-2018 09:10:32";"tcm:0-34421-66560";"Page";"tcm:6-203830-64";"3,48";"0,102";"9,086";"Success"
"22-05-2018 04:58:50";"tcm:0-34420-66560";"Component";"tcm:6-211993";"0,316";"0,041";"5,476";"Success"
"22-05-2018 02:27:51";"tcm:0-34418-66560";"Page";"tcm:6-211964-64";"7,089";"0,015";"15,004";"Success"
"22-05-2018 02:17:34";"tcm:0-34416-66560";"Page";"tcm:6-153-64";"4,011";"0,011";"8,567";"Success"

After importing this file in Excel , you can now generate statistics and graphs, and hopefully pull conclusions from them

 


This script expects the Core Module to be is installed and configured correctly.

The current version of the script is available from here:
https://gist.github.com/JacquesKors/168e10da7f66b747ab7f030cafa5b3b4 

Anonymous