Export Compliance Data to BI or a Data Warehouse

You can load your Scrut compliance data into a data warehouse or BI tool, such as Snowflake, BigQuery, Looker, or Power BI, and report on it alongside the rest of your business data. This guide shows you how to build a scheduled job that takes a daily snapshot of your frameworks, controls, policies, evidence, tests, and vulnerabilities using read-only endpoints.

Example Use Case

Your security team wants a compliance dashboard that leadership can check without signing in to Scrut. The dashboard needs to answer questions such as:

• How close is each framework to audit readiness, and how has that changed over the last quarter?
• Which controls are non-compliant, and who owns them?
• Which policies and evidence tasks are due for review in the next 30 days?
• How many automated tests need a fix, grouped by application?
• How many open critical and high vulnerabilities are there, and how long have they been open?

The Scrut API returns the current state of your data. To report on trends, the job in this guide runs once a day and stores each run as a dated snapshot. Over time, the snapshots build the history your dashboard charts.

How the Export Works

A typical export job does the following:

1. Requests an access token with a Read Only credential.
2. Calls each list endpoint once, adding the extra fields your reports need.
3. Pages through the vulnerabilities list.
4. Optionally, calls the controls list once per framework to get framework-specific control status.
5. Writes each record to your warehouse with the snapshot time.

Every endpoint this guide uses is read-only:

Prerequisites

• An Org Admin who can create a credential in Settings → Developer Console.
• A scheduler to run the job, such as cron, a CI/CD pipeline, or your data orchestration tool.
• A destination for the data, such as a data warehouse, a cloud storage bucket, or a database.
• The examples use https://api.scrut.io. Replace it with your region's base URL.

Step 1: Create a Read-Only Credential

A Read-Only credential has every scope this job needs.

1. Ask an Org Admin to create a credential in Settings → Developer Console with Read Only access. See Create API Credentials.
2. Copy the Client ID and Client Secret. The client secret is shown only once.
3. Store both values in your scheduler's secrets manager or in environment variables:

export SCRUT_CLIENT_ID="your-client-id"
export SCRUT_CLIENT_SECRET="your-client-secret"

Step 2: Request an Access Token

Exchange your credentials for an access token at the start of each run:

curl -X POST https://api.scrut.io/oauth/token \
  -H "Content-Type: application/json" \
  -d '{
    "grant_type": "client_credentials",
    "client_id": "'"$SCRUT_CLIENT_ID"'",
    "client_secret": "'"$SCRUT_CLIENT_SECRET"'"
  }'

Step 3: Choose the Fields to Export

List endpoints return a default set of fields. Use the fields parameter to add the columns your reports need. Pass multiple values as a comma-separated list.

For the use case in this guide, add at least these fields:

• mappedFrameworkIds on controls, policies, evidence, and tests, so you can report on each framework.
• mappedControlIds on policies and evidence, so you can link artifacts to controls.
• modifiedOn on every endpoint that supports it, so you can see what changed between snapshots.
• firstSeen on vulnerabilities, so you can calculate how long findings have been open.

Step 4: Fetch the Data

Frameworks, Controls, Policies, Evidence, and Tests

These list endpoints aren't paginated. Each call returns every matching record in a single array, so you need only one call per endpoint:

curl "https://api.scrut.io/v1/evidence?fields=mappedFrameworkIds,mappedControlIds,recurrence,evidenceCollectionMethod,modifiedOn" \
  -H "Authorization: Bearer $SCRUT_ACCESS_TOKEN"

You can also narrow each export with filters, such as entityIds to export one entity or frameworkIds to export one framework.

Vulnerabilities

The vulnerabilities list is the only paginated endpoint:

1. Request the first page without nextCursor. Set count to 100 to fetch the largest page size. The default is 50.
2. Pass meta.nextCursor from each response as the nextCursor query parameter to fetch the next page.
3. Stop when meta.nextCursor is null.

curl "https://api.scrut.io/v1/vulnerabilities?count=100&fields=cvssScore,resourcesCount,firstSeen&nextCursor=$NEXT_CURSOR" \
  -H "Authorization: Bearer $SCRUT_ACCESS_TOKEN"

meta.totalCount is the number of findings that match your filters across all pages. Compare it with the number of records you fetched to confirm the run is complete.

Control Status for Each Framework

On the controls list, the frameworkIds filter also changes how compliance is calculated. When you set it, each control's status and controlCompliancePercentage reflect only the frameworks you pass. The same control can be compliant in one request and non_compliant in another.

To report control status for each framework, call the controls list once per framework and store the frameworkId you filtered by with each record:

curl "https://api.scrut.io/v1/controls?frameworkIds=14c4c45d-6c31-4097-a02a-ad9cfd04850e" \
  -H "Authorization: Bearer $SCRUT_ACCESS_TOKEN"

Step 5: Load the Data Into Your Warehouse

How you model the data depends on your warehouse, but these conventions keep the snapshots easy to query:

• Add a snapshot column. Store the time of run with every record, so each daily run becomes its own set of rows.
• Keep one table per resource. For example, scrut_frameworks, scrut_controls, scrut_policies, scrut_evidence, scrut_tests, and scrut_vulnerabilities.
• Flatten arrays into bridge tables. Fields such as mappedFrameworkIds, mappedControlIds, assignees, and entities are arrays. Store them in separate tables keyed by the record ID, such as scrut_evidence_frameworks with evidenceId and frameworkId.
• Keep framework progress as columns. complianceProgress on each framework contains controls, policies, evidence, and tests, each with counts and a percentage. Flatten these into columns for easy charting.

When you load the data, handle these formats:

The API never returns download URLs for files hosted by Scrut, so file contents aren't part of the export.

Example Script

This Python script fetches every resource, pages through vulnerabilities, fetches control status for each framework, and writes each resource to a JSON Lines file with the snapshot time. Load the files into your warehouse with your usual loader.

import json
import os
import time

import requests

BASE_URL = "https://api.scrut.io"

LIST_EXPORTS = {
    "frameworks": ("/v1/frameworks", "addedOn,modifiedOn,tscSelections"),
    "controls": ("/v1/controls", "mappedFrameworkIds,outOfScopeReason,modifiedOn"),
    "policies": ("/v1/policies", "mappedFrameworkIds,mappedControlIds,recurrence,publishedOn,modifiedOn"),
    "evidence": ("/v1/evidence", "mappedFrameworkIds,mappedControlIds,recurrence,evidenceCollectionMethod,modifiedOn"),
    "tests": ("/v1/tests", "mappedFrameworkIds,ignoreReason,modifiedOn"),
}

class ScrutClient:
    def __init__(self):
        self.token = None
        self.expires_at = 0

    def _refresh_token(self):
        response = requests.post(
            f"{BASE_URL}/oauth/token",
            json={
                "grant_type": "client_credentials",
                "client_id": os.environ["SCRUT_CLIENT_ID"],
                "client_secret": os.environ["SCRUT_CLIENT_SECRET"],
            },
        )
        response.raise_for_status()
        body = response.json()
        self.token = body["access_token"]
        self.expires_at = time.time() + body["expires_in"]

    def get(self, path, params=None):
        for attempt in range(5):
            # Request a new token a minute before the current one expires.
            if time.time() > self.expires_at - 60:
                self._refresh_token()

            response = requests.get(
                f"{BASE_URL}{path}",
                headers={"Authorization": f"Bearer {self.token}"},
                params=params,
            )

            if response.status_code == 429:
                time.sleep(int(response.headers.get("Retry-After", 60)))
                continue
            if response.status_code == 401:
                # The token expired or is stale. Request a new one and retry.
                self.expires_at = 0
                continue
            if response.status_code in (500, 503):
                time.sleep(2 ** attempt)
                continue

            response.raise_for_status()
            return response.json()

        raise RuntimeError(f"GET {path} failed after retries")

def fetch_vulnerabilities(client):
    params = {"count": 100, "fields": "cvssScore,resourcesCount,firstSeen"}
    while True:
        body = client.get("/v1/vulnerabilities", params)
        yield from body["data"]
        next_cursor = body["meta"].get("nextCursor")
        if not next_cursor:
            break
        params["nextCursor"] = next_cursor

def write_jsonl(name, records, snapshot_at):
    with open(f"scrut_{name}.jsonl", "w") as file:
        for record in records:
            file.write(json.dumps({"snapshotAt": snapshot_at, **record}) + "
")

def main():
    client = ScrutClient()
    snapshot_at = int(time.time() * 1000)

    results = {}
    for name, (path, fields) in LIST_EXPORTS.items():
        results[name] = client.get(path, {"fields": fields})["data"]
        write_jsonl(name, results[name], snapshot_at)

    write_jsonl("vulnerabilities", fetch_vulnerabilities(client), snapshot_at)

    # Control status calculated separately for each framework.
    framework_controls = []
    for framework in results["frameworks"]:
        framework_id = framework["frameworkId"]
        controls = client.get("/v1/controls", {"frameworkIds": framework_id})["data"]
        framework_controls.extend({"frameworkId": framework_id, **c} for c in controls)
    write_jsonl("framework_controls", framework_controls, snapshot_at)

if __name__ == "__main__":
    main()

Example Reports

Once a few snapshots are loaded, you can build reports such as:

Add More Detail With Get Endpoints

Some data is available only from the get endpoint for a single record. Each of these calls is a separate request, so use them selectively:

Troubleshooting

Contact support@scrut.io for further assistance.