How To Download CPQ Performance Logs

Introduction

Oracle CPQ logs all user actions including logins, logouts, commerce and configuration actions. Each of these events capture elapsed server and browser times to complete which make the log essential in troubleshooting performance issues.

CPQ admins can view the performance log in the UI Designer, but potentially more useful, you can also view log entries and download logs using the CPQ REST APIs. You can use these APIs to script downloading logs so they can be ingested into OCI Observability and Management services or any other log analysis tool.

We’ll review some examples invoking the Export Performance Log Events REST endpoint using Postman.

Export Performance Log Events

In case you haven’t connected to the Oracle CPQ REST API before, check out the Quick Start guide to make you know how to configure the URL and send an authenticated request.

You can download CPQ performance logs using the Export Performance Log Events REST endpoint. This endpoint executes a post request that accepts a Criteria Object as part of the body and returns a link to the log file. In a subsequent request you can download the log file using a get request.

Using the Criteria Object

You can use the Criteria Object to apply criteria to your request in order to reduce the size of the downloaded logs.

There are a number of attributes of the Criteria Object that can be used to filter, sort, or set the returned attributes of the performance log file.

Response Attributes

The following attributes can be returned in the performance log response and can also be used in filter criteria.

Example Criteria

There are a number of attributes of the Criteria Object that can be used to filter, sort, or set the returned attributes of the performance log file.

Filter log rows by eventDate greater than timestamp

This example applies a criteria to return events whose eventDate is greater than the given timestamp.

{
    "criteria": {
        "q": "{eventDate:{$gte: '{{query_timestamp}}'}}"
    }
}

Filter log rows by serverTime greater than value

{
    "criteria": {
        "q": "{serverTime:{$gte: '{{min_response_val}}'}}"
    }
}

Filter log columns

This example sets the fields that will be returned in the downloaded log file.

{
    "criteria": {
        "fields": ["id","event","eventDate","component","transactionId","url","serverTime","browserTime"]
    }
}

Sort log rows by serverTime

This example sorts the events in the downloaded log file by serverTime descending.

{
    "criteria": {
        "orderby": ["serverTime:desc"]
    }
}

Filter by multiple criteria

This example filters by serverTime greater than a threshold value and if the browserTime value exists.

{
    "criteria": {
        "q": "{$and:[{serverTime:{$gte: {{max_response_val}}}},{browserTime:{$exists: true}}]}"
    }
}

Example Filtering Scenarios

Here are a couple scenarios for filtering performance logs.

Filter logs rows by response time over threshold

In this scenario you want to return all events where the response time (serverTime) is greater than a certain threshold. Important attributes to return include the transactionId, which can be used to return
all related events in a subsequent request.

First, in your collection Pre-request Script ensure that collection variable max_response_val exists and has a default value.

// If max_response_val variable not set in collection, then create it with default value '1000'.
if( !pm.collectionVariables.get('max_response_val')) {
    pm.collectionVariables.set('max_response_val', 1000)
}

Configure your request to the Export Performance Log Events REST endpoint to filter by serverTime, return only selected fields, and sort the response showing the largest response times first.

{
    "criteria": {
        "orderby": ["serverTime:desc"],
        "fields": ["id","event","eventDate","component","referenceObject","transactionId","url","serverTime","browserTime"],
        "q": "{serverTime:{$gte: {{max_response_val}}}}"
    }
}

Filter logs rows by events in past hour(s)

In this scenario you want to return all events that have occurred in the past hour(s).

First, in your collection Pre-request Script ensure that collection variable hour_offset exists and has a default value.

// If hour_offset varaible not set in collection, then create it with default value '1'.
if( !pm.collectionVariables.get('hour_offset')) {
    pm.collectionVariables.set('hour_offset', 1)
}

Next, in your request Pre-request Script set the *query_timestamp* value you will use in the Criteria Object based on the hour_offset.

// Get current timestamp and subtract hour_offset
var query_timestamp = new Date();
// Set query_timestamp to current time minus hour_offset value
hour_offset = pm.collectionVariables.get('hour_offset') || 1;
query_timestamp.setHours(query_timestamp.getHours() - hour_offset)
console.log(query_timestamp.toISOString().replace(/[.]\d+/, ''))
// Add to collection variables
pm.collectionVariables.set("query_timestamp", query_timestamp.toISOString().replace(/[.]\d+/, ''))

Configure your request to the Export Performance Log Events REST endpoint to filter by eventDate.

{
    "criteria": {
        "q": "{eventDate:{$gte: '{{query_timestamp}}'}}"
    }
}

Downloading the Performance Log file

The response from the Export Performance Log Events endpoint includes a reference to a CSV file that you can then download via a GET request.

Here’s an example response from Export Performance Log Events

{
    "exportedFileName": "https://{{host}}/rest/{{version}}/files/performanceLogs_123456"
}

Ensure the exportedFileName is included in the response and set the collection variable you can use in following requests.

var data = JSON.parse(responseBody)

pm.test("Exported file name present", function () {
    pm.expect(data.exportedFileName).not.eql(null);
    // Set log file variable
    pm.collectionVariables.set('exported_file_name',data.exportedFileName)
    console.log(data.exportedFileName)
});

Use the exportedFileName collection variable in a subsequent GET request in order to download the file.

Test log file correctness

Use the following tests to ensure the log file is correct based on your request criteria.

const parse = require('csv-parse/lib/sync'),
    parsedBody = parse(responseBody, {relax_column_count: true});

pm.test("Response not empty", function () {
    // Check that response not empty
    pm.expect(parsedBody).not.eql(null);
})

pm.test("Response has at least 2 rows", function () {
    // Check that we have at least 2 two (header + row)
    pm.expect(parsedBody).to.have.length.above(1);
})

pm.test("Response eventDate greater than request criteria timestamp", function () {   
    // Get index of eventDate
    const head = parsedBody.shift(),
        eventDate = head.indexOf('eventDate')
    
    // Iterate through the rows
    parsedBody.forEach(function(row) {    
        // console.log(Date.parse(row[eventDate]) + "; " + Date.parse(pm.collectionVariables.get("query_timestamp")))
        // Check eventDate greater than request criteria timestamp
        pm.expect(Date.parse(row[eventDate])).to.be.greaterThan(Date.parse(pm.collectionVariables.get("query_timestamp")));
    })
});

Summary

Use the above examples in Postman and test out how easy it is to download your CPQ performance logs. Happy hunting.

~snolan