Reporting API - Export results to a CSV in Bash

#!/bin/bash

# Check if the -h flag was provided to get usage help
if [[ "$1" == "-h" || "$1" == "-help" || "$1" == "--help" || "$1" == "help" ]]; then
	echo "Transform mabl batch results JSON (provided on STDIN) into a CSV format with optional header row (included if -p provided as argument)"
    echo "Example usage: cat results.json | $0 -p > results.csv"
    exit 0
fi

# Check whether the -p flag was provided to print the header
if [ "$1" == "-p" ]; then
    print_header=true
else
    print_header=false
fi

# Extract the test_results array using jq
results=$(jq -r '.test_results')


# Define the list of variable names to extract
fields=(
    'application_id'
    'application_name'
    'environment_id'
    'environment_name'
    'initial_url'
    'scenario_name'
    'browser'
    'browser_version'
    'execution_runner_type'
    'plan_id'
    'plan_name'
    'plan_run_id'
    'test_id'
    'test_version'
    'test_name'
    'test_type'
    'branch'
    'test_run_id'
    'test_run_app_url'
    'is_ad_hoc_run'
    'failure_category'
    'start_time'
    'end_time'
    'run_time'
    'status'
    'success'
    'trigger_type'
    'triggering_deployment_event_id'
    'emulation_mode'
    'metrics.cumulative_speed_index'
    'metrics.cumulative_api_response_time'
    'metrics.accessibility_rule_violations.critical'
    'metrics.accessibility_rule_violations.serious'
    'metrics.accessibility_rule_violations.moderate'
    'metrics.accessibility_rule_violations.minor'
)

# Print the header as a comma-separated list of field names
if [ "$print_header" == true ]; then
    echo "${fields[*]}" | tr ' ' ','
fi

# Loop through each result in the array and output a flat CSV format, using base64 encoding+decoding to handle spaces and special characters in the result rows during loop
for row in $(echo "${results}" | jq -r '.[] | @base64'); do
    decoded_row=$(echo ${row} | base64 --decode)
    fields_values=()

    for field in "${fields[@]}"; do
        value=$(echo $decoded_row | jq -r ".$field // empty") # extract the field, using the empty string instead of "null" for fields with no value
        value=$(echo "$value" | sed 's/,/_/g')  # replace commas with underscores
        fields_values+=("$value")
    done

    # Join field values with comma separators
	printf -v joined '%s,' "${fields_values[@]}"
	echo "${joined%,}"
done

curl "https://api.mabl.com/results/workspace/<WORKSPACE_ID>/testRuns?advanced_metrics=true" \
-u "key:<API_KEY>" \
| ./mabl_results_to_csv.sh -p \
> results.csv

#!/bin/bash

# Check number of arguments and provide help with usage example if incorrect
if [[ $# -lt 3 || $# -gt 4 ]]; then
  echo "Usage: $0 <workspace ID> <API key> <output filename> [query parameter string]"
  echo "Example: $0 'MY-WORKSPACE-ID-w' 'API-KEY' 'results.csv' 'test_id=MY-TEST-ID-j&earliest_run_start_time=1677587852400'"
  exit 1
fi

workspace_id=$1
api_key=$2
output_file=$3
endpoint="https://api.mabl.com/results/workspace/$workspace_id/testRuns?"

# Add given query parameters to API endpoint URL
if [ "$4" ]; then
  endpoint+="$4"
fi

# Set initial cursor value to null
cursor="null"

# Add header row to output file
echo "" | ./mabl_results_to_csv.sh -p >> $output_file

# Loop until there is no more data to retrieve
while : ; do
  echo "Getting batch of results with cursor = $cursor"
  # Make API call with current cursor value
  if [[ $cursor != "null" ]]; then
  	response=$(curl -s "$endpoint&cursor=$cursor" -u "key:$api_key")
  else
  	response=$(curl -s "$endpoint" -u "key:$api_key")
  fi

  # Extract cursor value from API response
  cursor=$(echo $response | jq -r '.cursor')

  # Process data from API response
  echo "Processing batch of results with cursor = $cursor"
  echo "$response" | ./mabl_results_to_csv.sh >> $output_file
  echo "Finished processing batch of results with cursor = $cursor"

  [[ $cursor != "null" ]] || break
done

./mabl_get_batch_results.sh <WORKSPACE_ID> <API_KEY> <OUT_FILE> '<QUERY_PARAMETER_STRING>'