Ad hoc queries

You can execute ad hoc queries with the Scanner API, which allows you to run an arbitrary query over a specified time range.

What is an ad hoc query?

An ad hoc query is a search query with a start_time, an end_time, a max_rows, and query. It runs asynchronously in the background, and you can poll it periodically to check for results.

An ad hoc query is basically analogous to a query you make in the Search tab in Scanner:

  • The results of an ad hoc query are tabular, consisting of columns and rows.

  • The results table is limited in size to 128MB. Note that this refers to its internal memory representation, not necessarily the size of the returned JSON blob.

  • The table can have at most max_rows rows, but may have fewer due to the memory size limitation.

  • If the query is over log events with no aggregations, the returned log events are always guaranteed to be contiguous, and also to be either the latest or earliest log events, depending on the value of scan_back_to_front.

  • The table has a maximum in-memory size, which is 1GB; if a table would exceed that, then rows may be dropped to allow it to fit, regardless of the value of max_rows. This means that any paging queries should not check if the number of rows returned is equal to max_rows when determining if there are additional pages in a time range; they should instead only terminate if a returned page is empty, or the range is exhausted.

There are two ways to execute an ad hoc query: asynchronous and blocking.

How to execute an asynchronous ad hoc query

POST /v1/start_query

To execute an asynchronous ad hoc query, you first create it via POST /v1/start_query request. The Scanner API will return the id of the query, which you can use to poll its status with GET /v1/query_progress requests.

Body

Name
Type
Description

query required

string

Query text

start_time required

string

Start timestamp for the query (inclusive). The format of the timestamp is RFC 3339

end_time required

string

End timestamp for the query (exclusive). The format of the timestamp is RFC 3339

max_rows

number

Maximum number of rows to return. Default is 1000, max is 100000

scan_back_to_front

boolean

Whether to scan from back (latest) to front (earliest). Default is true

Example

curl $API_BASE/v1/start_query \
-H "Authorization: Bearer $SCANNER_API_KEY" \
-H "Content-Type: application/json" \
-X POST \
-d '{
  "query": "%ingest.source_type: \"aws:cloudtrail\" and sourceIPAddress: 174.23.51.122",
  "start_time": "2024-02-04T01:00:00.000Z",
  "end_time": "2024-02-04T01:30:00.000Z"
}'

Response

When the ad hoc query has been completed successfully, the response HTTP status code will be 200, and the result will contain the ID of the ad hoc query that was just created.

{ "qr_id": "37ccf932-42e7-4e2e-b21e-e9f67384bea7" }

Check query progress

GET /v1/query_progress/{qr_id}

Gets the current progress of the query with the supplied qr_id.

Users are expected to run GET requests periodically to check for query results. We recommend checking every 1 second.

Example

curl $API_BASE/v1/query_progress/37ccf932-42e7-4e2e-b21e-e9f67384bea7 \
-H "Authorization: Bearer $SCANNER_API_KEY" \
-H "Content-Type: application/json"

Response

When the query is still in progress, the response HTTP status code will be 200, and the is_completed field will be false:

{
  "is_completed": false,
  "results": {
    "column_ordering": [],
    "rows": []
  },
  "metadata": {
    "n_bytes_scanned": 8716223
  }
}

How to execute a blocking ad hoc query

POST /v1/blocking_query

To execute a blocking ad hoc query, you just issue a POST /v1/blocking_query request. The Scanner API will hold the request open until the query completes, or it will time out if the query takes longer than 60 seconds.

Body

Name
Type
Description

query required

string

Query text

start_time required

string

Start timestamp for the query (inclusive). The format of the timestamp is RFC 3339

end_time required

string

End timestamp for the query (exclusive). The format of the timestamp is RFC 3339

max_rows

number

Maximum number of rows to return. Default is 1000, max is 100000

scan_back_to_front

boolean

Scan from back (latest) to front (earliest). Default is true

Example

curl $API_BASE/v1/blocking_query \
-H "Authorization: Bearer $SCANNER_API_KEY" \
-H "Content-Type: application/json" \
-X POST \
-d '{
  "query": "%ingest.source_type: \"aws:cloudtrail\" and sourceIPAddress: 174.23.51.122",
  "start_time": "2024-02-04T01:00:00.000Z",
  "end_time": "2024-02-04T01:30:00.000Z"
}'

Response

When the query has completed successfully, the response HTTP status code will be 200, and the is_completed field will be true.

The results field will contain information you can use to render a table of results. The columns field is an array of the names of the columns in the results table, and the rows field is an array of JSON objects representing the rows.

{
  "is_completed": true,
  "results": {
    "column_ordering": ["time", "@index", "raw_event"],
    "rows": [
      { "time": "2024-02-04T01:02:12.210Z", "@index": "global-cloudtrail", "raw_event": "..." },
      { "time": "2024-02-04T01:12:45.761Z", "@index": "global-cloudtrail", "raw_event": "..." },
      { "time": "2024-02-04T01:12:45.761Z", "@index": "global-cloudtrail", "raw_event": "..." },
      ...
    ]
  },
  "metadata": {
    "n_bytes_scanned": 90184761
  }
}
PreviousAPINextDetection Rules

Last updated

Was this helpful?