How To Download CPQ Performance Logs

January 17, 2024 | 7 minute read
Text Size 100%:

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.

Criteria Object
Syntax Description
q Declares a query specification expression in MongoDB format. See MongoDB Query Specifications for more information.
fields An array of field names to be returned. The fields array values determine the "attributes" in the response's state object. For example, "fields": \["field1","field2"\]
orderby An array of field names to specify the order of items returned in the response, each field name could optionally be followed by asc or desc. The default is "asc". For example, "orderby": \["listPrice:asc","totalPrice:desc"\]

Response Attributes

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

Response Attributes
Attribute Name Description
serverTime Time Elapsed in milliseconds
url The URL invoked by the user
applicationVersion The version of the application
createdDate System field indicating the date on which the object was created
component The name of the action
transactionId Transaction identifier
id Primary Key of Log Application Events
event The type of event that occurred
requestId The request id for the event
sessionId The user session id
referenceObject The object upon which the action was performed
eventDate The Datetime on which the event occurred
modifiedDate System field indicating the date on which the object was last modified
login The log-in of the user that performance the action
browserVersion The version of the browser
browserTime The time the end user perceived the action to take (ms)
ipAddress The IP address of the user
node Server Node

 

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: ''}}"
    }
}

Filter log rows by serverTime greater than value

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

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: }},{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: }}"
    }
}

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: ''}}"
    }
}


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:///rest//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

Shea Nolan


Previous Post

CPQ: Use the of function urldatabypost for REST GET calls

Patrick Mc Erlean | 1 min read

Next Post


Natural Language Queries - Oracle Autonomous Database Now Speaks "Human" - Select AI

Jeffrey Thomas | 4 min read