Queries & Samples

You can use the /queries endpoint to fetch recently seen queries, the digest associated with a query ID, and query samples. You will need an API token, represented in each example as <API_TOKEN>.

Get a list of recently seen Queries

Getting a list of queries in the environment can be done with a request like the one below:

curl -X GET 'https://app.vividcortex.com/api/v2/queries?from=-3600&until=0&limit=15&new=false&offset=0&host=0&filter=^select' \
  -H 'Authorization: Bearer <API Token>' \
  -H 'X-Indent: true'

The above request will return a response containing the list of queries starting with “select” seen within the last hour for the host IDs listed in the request. This mimics the functionality of the Queries page.

The response is formatted like this:

{
  "data": [
    {
     "id": "004fd1e630142e0b",
     "digest": "select ?",
     "firstSeen": 1510587401,
     "lastSeen": 1525976100
    },
    {
     "id": "004fd1e630142abc",
     "digest": "select a, b, c from foo",
     "firstSeen": 1503012227,
     "lastSeen": 1525976100
    }
  ]
}

You can pass the following request parameters when fetching queries to alter the results that are returned:

  • from: The start time to query active queries within. It can be a relative timestamp or an absolute one.
  • until: The end time to query active queries within. Similar to from, it can be relative or absolute.
  • limit: The number of results to return. The absolute limit is 20,000 for a single request. The API will return a response header to indicate if the number of results exceeds the limit specified on a request (or the absolute maximum allowed). Look for the x-vc-meta-more response header; it is a boolean flag that will be set to true in cases where more items can be returned. In this case, use the offset parameter (described below).
  • new: Limit our search to queries that have a firstSeen within the specified interval.
  • offset: If specified, return query results for IDs past the one specified. The value is a query hex ID. This can be used to page across a result set. The API indicates if there is more data available for a request with the x-vc-meta-more response header (see the limit parameter above).
  • host: A comma-separated list of host IDs to filter queries against. Defaults to all hosts in the environment.
  • filter: A text filter to limit queries matching the specified pattern. Filters are considered for partial matches by default, but you can specify that a query must start or end with the specified text with the ‘^’ and ‘$’ anchor characters, respectively. You can also use * to match any number of any character.
  • tags: A space-separated list of key=value pairs, keys, values, or some combination thereof. The space and = need to be encoded. For example, if you were looking for all digests with both app=mobile and version2, you would add tags=app%3Dmobile%20version2 to your request.

Note: You can ignore passing the from and until parameters if you want to fetch all queries that match the specified filter, instead of only queries that are active within a given interval.

Fetching Query Text from a Digest

In metrics such as host.queries.q.f96c564f765a4810.errors.tput, the hash value is a query ID. In order to fetch the query text associated with that query ID, you can do the following; add tags=1 if you want the Query Tags associated with the digest as well:

curl 'https://app.vividcortex.com/api/v2/queries/f96c564f765a4810?tags=1 \
  -H 'Authorization: Bearer <API_TOKEN>' \
  -H 'X-Indent: true'

This will return the full query digest associated with that query ID:

{
   "data": [
      {
	  "id": "5fa58ae1f65f399b",
	  "digest": "oplog.rs.getmore(maxtimems:?, term:?, lastknowncommittedoptime:{ts:?, t:?})",
	  "firstSeen": 1508354461,
	  "lastSeen": 1510689720,
	  "tags": [
	     {
		"key": "db",
	        "values": [
		   "demo",
		   "postgres"
		]
	     },
	     {
		"key": "user",
		"values": [
		   "demo",
		   "postgres"
		]
	     }
	  ]
      }
   ]
}

Note: lastseen is an approximation and should be considered accurate to within about a day.

Fetch samples for a given query family

Getting a list of samples for a given query family can be done with a request like the one below:

curl -X GET 'https://app.vividcortex.com/api/v2/queries/samples?query=14356059872190895566&host=1&from=-3600&until=0&limit=1&detail=0' \
  -H 'Authorization: Bearer <API Token>' \
  -H 'X-Indent: true'

The above request will return a list of samples in the last hour for the query with the specified ID. Note that the ID argument is passed in base10, whereas query IDs are returned from the GET /queries request in base16. The GET /samples response has the following format:

{
  "data": [
    {
      "query": "25557865354856889",
      "db": "template",
      "user": "shards",
      "origin": "10.10.10.10:12345",
      "applicationName": "",
      "host": 165,
      "ts": 1525975554,
      "latency": 0.564419,
      "text": "c2VsZWN0IDE=",
      "truncated": false,
      "plan": null,
      "FailedRules": null,
      "connectionId": "13571845"
    }
  ]
}

Note: The text field in the response is sent encoded as base64; you can omit returning the “text” and “plan” fields from the response by passing details=1 instead of details=0.