IBM QRadar SOAR

 View Only

 How to Query SOAR Database to Extract Playbook Execution Time?

Andy J. Del Aguila Villanueva's profile image
Andy J. Del Aguila Villanueva posted Thu November 14, 2024 10:34 AM

Hi everyone,

I am working with IBM SOAR (formerly Resilient) and I am trying to extract specific information regarding the execution of playbooks. Specifically, I want to retrieve the completion time of a playbook (e.g., "Send Email") as part of my workflow.

Here is the scenario:

  • I have a playbook running on SOAR, and at the end of the playbook, I need to query for the exact timestamp when the playbook finished executing.
  • Ideally, I would like to achieve this through a direct query to the SOAR database or using a REST API, but I’m unsure how to approach this.

What I’ve Tried:

  1. Using the REST API:

    • I have looked at the incident and workflow-related endpoints, but I couldn’t find clear documentation on how to retrieve workflow execution logs or timestamps for specific playbooks.
    • If there's a specific endpoint or payload format I should use, I would appreciate guidance.

  2. Direct Database Query:

    • I understand SOAR uses PostgreSQL for its backend, but I haven’t found detailed information on where playbook execution details (like timestamps) are stored in the database schema.
    • I have administrative access but need assistance identifying the relevant tables or fields.

What I Need:

  1. A method (via API or database) to extract the end time of a specific playbook's execution.
  2. If possible, an example query or script that shows how to retrieve this information.

Any advice, best practices, or references to relevant documentation would be greatly appreciated.

Thank you in advance for your help!

PATRICK MCKENNA's profile image
PATRICK MCKENNA posted Tue November 19, 2024 10:10 AM

Hello,

  I might share with you a bash script I wrote to retrieve the most recent 1000 completed executions of a playbook, then processes the output to extract end time and elapsed seconds for the executions. I suspect that it would illustrate how I went about doing so, against the REST API.

  Of necessity it uses an API key to access the REST API. The key needs permissions as follows:

  • Incident permissions: read
  • Playbook permissons: read
  • Other Permissions: Read Incident Action Invocations

The parameters are:

  1. Base URL
  2. API key
  3. API secret
  4. Organisation ID
  5. Playboook name

Here's an example of using it.

$ ./getExecutionTime.sh http://localhost:8080 b6885ab2-e6a8-4a26-969d-7fc15f74082c SOQq-IUouG-J1J1vCUs5x0Dgwqd5iTcncNeV-5291S8 201 "Just script"


{
  "instance": 5,
  "end_time": "2024 11 19 13:05:52 IST",
  "elapsed_time_seconds": 0.269
}
{
  "instance": 4,
  "end_time": "2024 11 19 13:05:50 IST",
  "elapsed_time_seconds": 0.153
}
{
  "instance": 3,
  "end_time": "2024 11 19 13:05:49 IST",
  "elapsed_time_seconds": 0.164
}
{
  "instance": 2,
  "end_time": "2024 11 19 13:05:38 IST",
  "elapsed_time_seconds": 0.159
}
{
  "instance": 1,
  "end_time": "2024 11 19 10:27:46 IST",
  "elapsed_time_seconds": 0.394
}

PATRICK MCKENNA's profile image
PATRICK MCKENNA posted Tue November 19, 2024 10:12 AM

Hello,

Here is the script.

Attachment  View in library
getExecutionTime.sh 975 B
PATRICK MCKENNA's profile image
PATRICK MCKENNA posted Tue November 19, 2024 10:30 AM

Hello,

  It appears that attachments aren't sent as part of the email handling of questions here, so this is the script in question:

#!/bin/bash
BASE_URL=$1
API_KEY=$2
API_SECRET=$3
ORG_ID=$4
PLAYBOOK_NAME=$5

QUERY='{
  "sorts": [
    {
      "field_name": "start_time",
      "type": "DESC"
    }
  ],
  "filters": [
    {
      "logic_type": "all",
      "conditions": [
        {
          "field_name": "playbook_name",
          "method": "equals",
          "value": "'$PLAYBOOK_NAME'"
        },
        {
          "method": "in",
          "field_name": "status",
          "value": [
            "completed"
          ]
        }
      ]
    }
  ],
  "start": 0,
  "length": 1000
}'



URL="${BASE_URL}/rest/orgs/${ORG_ID}/playbooks/execution/query_paged?include_activity_error_msg=false"

curl -s -k "$URL" -H 'Content-Type: application/json' -basic -u $API_KEY:$API_SECRET -H 'X-Requested-With: XMLHttpRequest' --data-raw "$QUERY" | jq '.data.[] | { instance: .id, end_time: (.last_activity_time / 1000 | floor | strftime("%Y %m %d %H:%M:%S %Z")),  elapsed_time_seconds:  (.elapsed_time /1000)}'