How to query with API v2

This article lists and describes the parameters you can use in API v2 query requests as well as the different response formats:

Remember that all API queries must be validated using one of the available authorization methods.

Query request

POST /query

Perform a query

Parameters

Parameter

Type

Description

Content-Type *required String (header) This is always application/json
Authorization String (header)

Include this header if you want to use a token to authorize your request. When you use a token, only the Content-Type and Authorization parameters are required.

See Authorization methods for more information.

x-logtrust-apikey String (header) This is the Devo domain API key found in Administration → Credentials.
x-logtrust-sign String (header) HMAC SHA-256 using the API_SECRET to sign the concatenation of the api_key, body message and timestamp
x-logtrust-timestamp String (header) Timestamp in milliseconds
query

String (body)

This is the query that you want to run expressed in LINQ script. To find the query's LINQ script, open the query in the Data Search area, then choose Toggle Query Editor from the toolbar.

The body of the request must contain either the query or the queryId parameter.

queryId String (body)

This is the ID of the query that you want to run. To find the query ID, open the query in the Data Search area, then choose Additional Tools → Query Info → Get ID.

The body of the request must contain either the query or the queryId parameter.

from *required Number (body) The start date as a UTC timestamp in seconds. See the Relative dates section below to learn more about this parameter.
to Number (body) The end date as a UTC timestamp in seconds. If this parameter is left out, the query will be continuous. See the Relative dates section below to learn more about this parameter.
mode Object (body) This object contains the mode.type parameter to specify the format of the response. If left out of the request body, the default response type of JSON will be used.
mode.type String (body)

The format in which you want the response to be sent. The possible values are:

  • JSON
  • JSON/compact
  • JSON/simple
  • JSON/simple/compact
  • msgpack
  • csv
  • tsv
  • xml

These response formats are fully described later in this article. When you indicate a response format other than JSON , you must include the dateFormat and timeZone parameters.

destination Object (body)

This object specifies where the response should be sent. If this object is left out of the request body, the response will be sent back to request source.

destination.type String (body)

This is the type of system to which the response should be sent. The possible values are:

  • HDFS
  • Kafka
  • S3

Depending on the destination.type, additional parameters will be required. See the related HDFS, Kafka, and S3 articles.

destination.params List (body) Destination parameter, depends on destination.type. Check the HDFS, Kafka and S3 articles to see the parameters required for each destination
dateFormat String (body)

This is required only when you specify a mode.type other than JSON. The possible formats are:

  • default - yyyy-MM-dd' 'HH:mm:ss.SSS
  • sql - yyyy-MM-dd' 'HH:mm:ss.SSS
  • iso - yyyy-MM-dd'T'HH:mm:ss.SSSXX
timeZone String (body)

Change the timezone of the query, only for mode types different from JSON. This parameter supports any positive or negative GMT timezone, like “GMT-2” or “GMT+1”

skip/offset Number (body)

You can use either the skip or offset parameters to skip the first X elements of the query.

limit Number (body) Limit the results of the query. The query will stop after returning the first X elements of the query or reaching its end.

Examples

{
    "query": "from siem.logtrust.malote.query select *",
    "from": "1519645036",
    "to": "1519645136",
    "mode": {
        "type": "json"
    },
    "destination": {
      "type": "hdfs",
      "params": {
        "param1": "value1",
        "param1": "value2"
      }
    }
}

.

{
    "from": 1519989362,
    "mode": {
        "type": "json/simple"
    },
    "query": "from my.synthesis.vec00.suricataalert group every 30s every 30s select *",
    "to": 1519989392
}


{ 
 "query": "from demo.ecommerce.data select *",
 "limit": 10, 
 "from": 1528306922,
 "to": 1528306952,
 "mode": {
 "type": "tsv" 
 }
}

Relative dates

A relative date range is a period of time that is relative to the current date (last week, last month, etc). You can add different operators to the from and to parameters of your query request to indicate specific time ranges. Note that the date you enter in the to parameter must always be greater than or equal to the from date.

For all the following examples, we assume that the moment of execution was 08-10-2018, 14:33:12. All the date/time formats are dd-MM-yyyy and HH:mm:ss.

Dates

Operator Description
today

Get the current day at 00:00:00

  • "from": "today"
    This sets the starting date to 08-10-2018, 00:00:00

  • "to": "today"
    This sets the ending date to 08-10-2018, 00:00:00
now

Get the current day and time

  • "from": "now"
    This sets the starting date to 08-10-2018, 14:33:12

  • "to": "now"
    This sets the ending date to 08-10-2018, 14:33:12
endday

If you use this in the from field you will get the current day and the last second of the day. If you use it in the to field you will get the from date and the last second of that day.

  • "from": "endday"
    This sets the starting date to 08-10-2018, 23:59:59

  • "from": "01/09/2018 12:22:11"

    "to": "endday"
    This sets the ending date to 01-09-2018, 23:59:59

endmonth

If you use this in the from field you will get the last day of the current month and the last second of that day. If you use it in the to field, you will get last day of the month indicated in the date field and the last second of that day.

  • "from": "endmonth"
    This sets the starting date to 31-10-2018, 23:59:59

  • "from": "05/09/2018 12:22:11"

    "to": "endmonth"
    This sets the ending date to 30-09-2018, 23:59:59

Days

Operator Description
d

Enter a number followed by d in the from parameter to substract N days from the current date. If you use it in the to field you will get the from date plus the indicated number of days.

  • "from": "2d"
    This sets the starting date to 06-10-2018, 14:33:12

  • "from": "05/09/2018 12:22:11"

    "to": "2d"
    This sets the ending date to 07-09-2018, 14:33:12
    .

  • "from": "5d"

    "to": "2d"
    This sets the starting date to 03-10-2018, 14:33:12 and the ending date to 05-10-2018, 14:33:12

ad

Enter a number followed by ad in the from parameter to substract N days from the current date and set time to 00:00:00. If you use it in the to field you will get the from date plus the indicated number of days and set time to 00:00:00.

  • "from": "2ad"
    This sets the starting date to 06-10-2018, 00:00:00

  • "from": "05/09/2018 12:22:11"

    "to": "2ad"
    This sets the ending date to 07-09-2018, 00:00:00


  • "from": "5d"

    "to": "2d"
    This sets the starting date to 03-10-2018, 00:00:00 and the ending date to 05-10-2018, 00:00:00

Hours

Operator Description
h

Enter a number followed by h in the from parameter to substract N hours from the current time. If you use it in the to field you will get the from time plus the indicated number of hours.

  • "from": "2h"
    This sets the starting date to 08-10-2018, 16:33:12

  • "from": "16h"
    This sets the starting date to 07-10-2018, 22:33:12

  • "from": "05/09/2018 12:22:11"

    "to": "2h"
    This sets the ending date to 05-09-2018, 14:22:11


  • "from": "5h"

    "to": "2h"
    This sets the starting date to 08-10-2018, 09:33:12 and the ending date to 08-10-2018, 11:33:12

ah

Enter a number followed by ah in the from parameter to substract N hours from the current date at 00:00:00. If you use it in the to field you will add the indicated number of hours to the from date at 00:00:00.

  • "from": "2ah"
    This sets the starting date to 07-10-2018, 22:00:00

  • "from": "05/09/2018 02:22:11"

    "to": "12ah"
    This sets the ending date to 05-09-2018, 12:00:00
    .

  • "from": "5ah"

    "to": "21ah"
    This sets the starting date to 07-10-2018, 19:00:00 and the ending date to 07-10-2018, 21:00:00

Query response formats

Responses to your queries can be either returned to the source of the request or forwarded to an HDFSS3, or Kafka type system.

Given the different possible destinations for query responses, you can also specify the format in which you want the response to be sent. This is specified in the mode.type parameter of the request body. The available response formats are:

Response type JSON

Response type JSON/compact

Response type JSON/simple

Response type JSON/simple/compact

Response type MsgPack

Response type CSV

Response type TSV

Response type Excel

Response type JSON

This is the default response format. This means that if you leave out the mode object from the request body, the response will automatically be returned in this format.

So, to receive responses in JSON format, you can either leave the mode object out of the request or you can specify it like this:

"mode": {
    "type":"json"
}

The JSON response will include the following fields: 

Field name

Type

Description

success

Boolean

The value true means that processing occurred without error.

The value of false means that an error occurred during processing.

msg

String

This describes the error in that case that the request was not successful. If not error occurs, it shows valid request.

status

Integer

Numeric value that specifies the error code.

0 - OK

1 - Invalid request

object

JSON Object

This object contains the query result. The format of the object's content depends on the query data.

cid String An ID value to uniquely identify yourself across multiple systems.
timestamp String Indicates the moment when a certain event ocurred.

Example

Here is a response in JSON format that occurred without error. 

{
 "success": true,
 "status": 0,
 "cid": "ue7bt8PFMN",
 "timestamp": 1528308389081,
 "msg": "valid request",
 "object": [
   {
     "eventdate": "2016-10-24 06:35:00.000",
     "host": "aws-apiodata-euw1-52-49-216-97",
     "memory_heap_used": "3.049341704E9",
     "memory_non_heap_used": "1.21090632E8"
   },
   {
     "eventdate": "2016-10-24 06:36:00.000",
     "host": "aws-apiodata-euw1-52-49-216-97",
     "memory_heap_used": "3.04991028E9",
     "memory_non_heap_used": "1.21090632E8"
   },
   {
     "eventdate": "2016-10-24 06:37:00.000",
     "host": "aws-apiodata-euw1-52-49-216-97",
     "memory_heap_used": "3.050472496E9",
     "memory_non_heap_used": "1.21090632E8"
   },
  ………

}

Response type JSON/compact

To receive responses in JSON/compact format, specify the mode object in your request like this:

"mode": {
    "type":"json/compact"
}

The JSON/compact response will include the following fields: 

Field name

Type

Description

success

Boolean

The value true means that processing occurred without error.

The value of false means that an error occurred during processing.

msg

String

This describes the error in that case that the request was not successful. If not error occurs, it shows valid request.

status

Integer

Numeric value that specifies the error code.

0 - OK

1 - Invalid request

object

JSON Object

This object contains the query result. The format of the object's content depends on the query data.

object.m

Array


Lists the fields returned in the object.d object and provides the type and index metadata for each field.

type - the data type of the value returned for the field. This will be one of:

  • timestamp - epoch value in milliseconds
  • str - string
  • int8 - 8-byte integer
  • int4 - 4-byte integer
  • bool - boolean
  • float8 - 8-byte floating point

index - an integer value that indicates the position of the field's value in the arrays returned in the object.d object.

object.d

Array

List of arrays containing the values of the query response.

cid String An ID value to uniquely identify yourself across multiple systems.
timestamp String Indicates the moment when a certain event ocurred.

Example

Here is an example of a response in JSON/compact format that occurred without error. The m object lists the fields and their data types and the d object contains the array of the field values of the query results.

{
    "msg": "",
    "success": true,
    "cid": "ut9br4PMYN",
    "timestamp": 1525317379082,
    "status": 0,
    "object": {
        "m": {
            "eventdate": {
                "type": "timestamp",
                "index": 0
            },
            "domain": {
                "type": "str",
                "index": 1
            },
            "userEmail": {
                "type": "str",
                "index": 2
            },
            "country": {
                "type": "str",
                "index": 3
            },
            "count": {
                "type": "int8",
                "index": 4
            }
        }
    },
        "d": [
            [1506442210000, "dom", "user1@logtrust.com", null, 2],
            [1506442220000, "dom", "user2@gmail.com", null, 2],
 
.....
        ]
 
    }

Response type JSON/simple

To receive responses in JSON/simple format, specify the mode object in your request like this:

"mode": {
    "type":"json/simple"
}

The response is a stream of JSON objects of the values that the query generates with the structure below. When the query does not generate more information, the connection is closed by the server. In case no date to value is requested, the connections are kept alive.

Example

{"eventdate":1488369600000,"domain":"none","userEmail":"","country":null,"count":3}
 {"eventdate":1488369600000,"domain":"user1@logtrust.com","userEmail":"0:0:0:0:0:0:0:1","country":null,"count":18}
 {"eventdate":1488369600000,"domain":"none","userEmail":"user2@logtrust.com","country":null,"count":7}
 {"eventdate":1488373200000,"domain":"user3@logtrust.com","userEmail":"127.0.0.1","country":null,"count":10}
 {"eventdate":1488373200000,"domain":"user4@logtrust.com","userEmail":"0:0:0:0:0:0:0:1","country":null,"count":28}
 {"eventdate":1488373200000,"domain":"dom","userEmail":"user5@logtrust.com","country":null,"count":15}
 
...

Response type JSON/simple/compact

To receive responses in JSON/simple/compact format, specify the mode object in your request like this:

"mode": {
    "type":"json/simple/compact"
}

The response is a stream of JSON objects with the structure below:

the first line is a JSON object map with the metadata information:

Field name Description
key name of the field

The m object lists the fields included in each query result along with their type and index metadata.

Field name Description
type The data type of the value returned. This will be one of:

timestamp - epoch value in milliseconds

str - string

int8 - 8-byte integer

int4 - 4-byte integer

bool - boolean

float8 - 8-byte floating point

index An integer value that indicates the position of the field's value in the arrays returned in the object.d object.

The rest of the lines are data lines. The field d gives access to the array of values with the information.

When the query does not generate more information, the connection is closed by the server. In case no date to value is requested, the connections are kept alive.

Example

{"m":{"eventdate":{"type":"timestamp","index":0},"domain":{"type":"str","index":1},"userEmail":{"type":"str","index":2},"country":{"type":"str","index":3},"count":{"type":"int8","index":4}}}
 {"d":[1506439800000,"dom","user1@logtrust.com",null,1]}
 {"d":[1506439890000,"dom","user2@logtrust.com",null,1]}
 {"d":[1506439940000,"dom","user3@logtrust.com",null,1]}
 {"d":[1506439950000,"dom","user4@logtrust.com",null,1]}
 {"d":[1506440130000,"dom","user5@logtrust.com",null,1]}
 {"d":[1506440130000,"dom","user6@logtrust.com",null,2]}
 {"d":[1506440170000,"dom","user7@logtrust.com",null,3]}

Response type MsgPack

To receive responses in MsgPack format, specify the mode object in your request like this:

"mode": {
    "type":"msgpack"
}

The response format is the same as a JSON object, but encoded using MsgPack, an efficient binary serialization format. See the msgpack website for more information. 

Response type CSV

To receive responses in CSV format, specify the mode object in your request like this:

"mode": {
    "type":"csv"
}

The system will return the information in CSV (Comma Separated Values) format. The following is a simple example of a CSV response.

Example

eventdate,domain,userEmail,country,count
2017-03-01 12:00:00.000,none,,,3
2017-03-01 12:00:00.000,user1@logtrust.com,0:0:0:0:0:0:0:1,,18
2017-03-01 12:00:00.000,none,user2@logtrust.com,,7
2017-03-01 13:00:00.000,user3@logtrust.com,127.0.0.1,,10
2017-03-01 13:00:00.000,user4@logtrust.com,0:0:0:0:0:0:0:1,,28
2017-03-01 13:00:00.000,dom,user5@logtrust.com,,15

Response type TSV

To receive responses in TSV format, specify the mode object in your request like this:

"mode": {
    "type":"tsv"
}

The system will return the information in CSV (Comma Separated Values) format. The following is a simple example of a TSV response.

Example

eventdate   domain  userEmail   country count
2017-03-01 12:00:00.000 none            3
2017-03-01 12:00:00.000 user1@logtrust.com    0:0:0:0:0:0:0:1     18
2017-03-01 12:00:00.000 none    user2@logtrust.com        7
2017-03-01 13:00:00.000 user3@logtrust.com    127.0.0.1       10
2017-03-01 13:00:00.000 user4@logtrust.com    0:0:0:0:0:0:0:1     28
2017-03-01 13:00:00.000 dom    user5@logtrust.com     15

Response type Excel

To receive responses in XLS format, specify the mode object in your request like this:

"mode": {
    "type":"xls"
}

The system sends the query results in a Microsoft Excel format file.

Have we answered your question?

If not, please contact our technical support team via email by clicking the button below.

CONTACT US