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 perform the following tasks:

  • Obtain an API token to use with the Export API.

  • Send a correctly formatted query request using the Export API.

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?"}

Obtaining a Bearer Token from your Observe Instance

Before you begin exporting query results, generate an access token using cURL. You can generate the token using a cURL request with an email and password combination and the /v1/login endpoint. API tokens generated with the /v1/login endpoint remain valid for 31 days after creation, with a rolling 10-day extension during active use.

For example, if you have a working login for an Observe instance such as a user email, [email protected], and password, password123, create a bearer token using a command similar to the following example:

curl -H 'Content-Type: application/json' \
  https://123456789012.observeinc.com/v1/login \ 
  -d '{"user_email":"[email protected]", "user_password":"hunter1", "tokenName":"my testing token"}'

In the example, replace 123456789012.observeinc.com with the hostname of your Observe instance, and replace the email and password with your login credentials.

If successful, the response should be similar to the following key:

{"ok":true, "access_key":"1abCDE2FgHIJKLMNoPqrstuV3WXYZA4bc"}

This access key can be used for authentication in subsequent API requests, using an HTTP Authorization header formatted like the following:

Authorization: Bearer <CUSTOMER_ID> <ACCESS_KEY>

For example, Authorization: Bearer 123456789012 1abCDE2FgHIJKLMNoPqrstuV3WXYZA4bc

If you use Single Sign-on (SSO) with Observe and an SSO provider that doesn’t provide your email address as a user property, you can use the User ID instead of the email address. The User ID for your user can be found in the Observe Web app in the Settings -> Account Settings tab, at the bottom of the page.

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.