Exporting Query Results in CSV or JSON Format

Data in Observe can be queried programmatically using the Export API, /v1/meta/export/query. You provide an OPAL query in a JSON-encoded POST request, and the API endpoint returns data in either CSV or newline-delimited JSON.

This tutorial demonstrates how to use the cURL command line utility to send a correctly formatted query request using the Export API.

Note

Interacting with the Observe API requires that you create a Bearer token first. Follow the instructions at Observe API Authentication for how to generate an API token.

APIs

POST https://$CUSTOMERID.observeinc.com/v1/meta/export/query

POST https://$CUSTOMERID.observeinc.com/v1/login

API reference documentation for /v1/meta/export/query can be found here.

Health Check

To determine the health of the export API, you can use the following endpoint:

curl https://api.observe-eng.com/v1/health

This command returns a JSON status and message.

{"ok":true,"message":"Everything's perfectly all right now. We're fine. We're all fine here, now, thank you. How are you?"}

Creating a cURL Request to Export Query Results

Now that you have an access key, use the /v1/meta/export/query API on an endpoint to dispatch a query.

A typical request using cURL to this endpoint looks like the following example:

curl https://123456789012.observeinc.com/v1/meta/export/query?interval=1h \
  -H "Authorization: Bearer 123456789012 1abCDE2FgHIJKLMNoPqrstuV3WXYZA4bc \
  -H 'Content-Type: application/json'\
  -H 'Accept: text/csv' \
  -d '
 {
  	"query": {
  		"stages":[
  		  {
  			 "input":[
  				 {
  				 "inputName": "system",
  				 "datasetId": "41001999"
  				}
  			],
  			"stageID":"main",
  			"pipeline":"statsby count:count()"
  		}
  	]
  }
}
'

If successful, the response returns CSV similar to the following example:

"count"
"12276614"

Let’s review the example cURL invocation and dive into concepts for the Export API.

  • Time Range

Provide the time range for the query as a parameter in the URL; in this case, interval=1h.

https://123456789012.observeinc.com/v1/meta/export/query?interval=1h

All queries in Observe are scoped to a time range. With the Export API, you can specify one or two of interval, startTime, or endTime. Specify the startTime and endTime in the RFC 3339 date format, for example, YYYY-MM-DDTHH:MM:SSZ, for 2023-03-21T15:01:23Z. interval consists of a number of seconds, minutes, or hours using s, m, or h such as 1h or 15m, and used as a relative offset to either startTime or endTime.

  • Authorization Header

In the Export API, you must provide an Authorization Header with a bearer token. Using the /v1/login endpoint, you created a bearer token.

-H 'Authorization: Bearer 123456789012 1abCDE2FgHIJKLMNoPqrstuV3WXYZA3bc' \

  • Results Format

Select the format of the query results returned by the Export API when specifying an Accept header in your HTTP request. Use either one of the following types:

-H 'Accept: text/csv' \
-H 'Accept: application/x-ndjson \

  • Query

The query is encoded in a JSON object similar to the following example:

{
  "query": {
    "stages": [
      {
        "input": [
          {
            "inputName": "system",
            "datasetId": "41001999"
          }
        ],
        "stageID": "main",
        "pipeline": "statsby count:count()"
      }
    ]
  }
}

The full specification of this object can be found in the reference documentation here.

The two most important parts of this object to replace are the datasetIdfield, which identifies the dataset you want to use as the input for your OPAL query, and the pipeline field, the OPAL query you want to send. The stageID and inputName attributes can be used to reference additional stages or datasets if your query requires multiple inputs, though most queries only reference a single input.

When viewing a dataset, you can determine the datasetId of the dataset you want to query by inspecting the browser URL in the Observe instance. For example, suppose you opened the Container Logs dataset in your Observe account. You might see a URL like the following:

https://123456789012.observeinc.com/workspace/41010101/dataset/event/Container-Logs-41001999

The trailing number in the URL indicates the datasetId, for example, 41001999.

The pipeline in the query JSON object contains the OPAL query you want to dispatch.

"pipeline": "statsby count:count()"

In this example, the query returns a single record with a count of rows in the dataset for the time range given in the request URL, for example, interval=1h. See Observe Processing and Analysis Language for full language details.

Next Steps

If you have questions or need assistance with the API, join the Observe Slack channel.