Fortinet black logo

API Guide

Query API Reference

Copy Link
Copy Doc ID 72038609-ba27-11ec-9fd1-fa163e15d75b:304651
Download PDF

Query API Reference

API Routes

Apply Basic Query

GET /api/3/{collection}?param1=value&param2=value

For more information, see API Endpoints Reference chapter.

Apply Ad Hoc Query

POST /api/query/{collection}
BODY:
{ QUERY OBJECT }
  • The collection parameter matches the collection route in the typical CRUD API (/api/3/{collection})

Example

POST /api/query/incidents
BODY:
{
  "logic": "AND",
  "filters": [
    {
      "field": "status.itemValue",
      "operator": "eq",
      "value": "Open"
    }
  ]
}

Apply Persisted Query

GET /api/query/{collection}/{queryId}
  • The collection parameter matches the collection route in the typical CRUD API (/api/3/{collection})
  • The queryId parameter matches a Query Object UUID stored in /api/3/query_objects

Example

GET /api/query/incidents/2e77a714-f0c1-45ca-bd49-b71efbd9328c

Query Objects

Filter

A filter contains a field, operator, and a value.

{
"field": "{fieldName}",
"operator": "{operator}",
"value": {value}
}

Field

The field can be any valid field in the module. To access sub-elements of a relationship or picklist field, you can use dot notation or double-underscore notation.

"field": "name"
"field": "status.itemValue"
"field": "assignedToPerson.email"

Operator

The operator can be any of the available operators for a specific field type.

"operator": "eq"
"operator": "like"

For the complete list of supported operations, see the Supported Field Operators section.

Value

The value is the object of the filter or comparison. For example, if you are using the is like operator, then the value will be a pattern to match the record field.

"operator": "eq",
"value": "Alert 123: Repeated login failures - device xxx"
 
"operator": "like",
"value": "alert ___: Repeated login failures%"

For the complete list of supported operations, see the Supported Field Operators section.

Supported Field Operators

Operator Value Syntax Description
eq string Equals: Field is an exact match.
neq string Not Equals: Field is not an exact match.
lt number Less Than: Field has a lesser than value. In the case of Date/Time fields this translates to "Before", i.e. the date field is before the specified date.
lte number Less Than or Equal To: Field has a lesser than or equal to value. In the case of Date/Time fields this translates to "On or Before", i.e. the date field is on or after the specified date.
gt number Greater Than: Field has a greater than value. In the case of Date/Time fields this translates to "After", i.e. the date field is after the specified date.
gte number Greater Than or Equal To: Field has a greater than or equal to value. In the case of Date/Time fields this translates to "On or After", i.e. the date field is on the specified date or after the specified date.
in value1 | value2 ... In: Field is in the given list of values.
nin value1 | value2 ... Not In: Field is not in the given list of values.
contains string Contains: Object contains the key. Only applicable for field type Object.
like string pattern Like: Field matches the given pattern. The pattern can contain text, percent (%), or an underscrore (_)
% represents zero or more of any characters.
_ represents one character.
notlike string pattern Not Like: Field does not match the given pattern. The pattern can contain text, percent (%), or an underscrore (_)
% represents zero or more of any characters.
_ represents one character.
isnull boolean Is Null: If value is set as true, then this checks if the field is null. If value is set as false, then this checks if the field is not null.

Logic

AND

A collection of filters can be applied in conjunction using "logic": "AND"

Example

Query all records where record.assignedToUser !== null && record.status === "Open"

{
  "logic": "AND",
  "filters": [
    {
      "field": "assignedToUser",
      "operator": "isnull",
      "value": false
    },
    {
      "field": "status",
      "operator": "eq",
      "value": "Open"
    }
  ]
}

OR

A collection of filters can be applied in disjunction using "logic": "OR"

Example

Query all records where record.status === "Open" || record.status === "Pending"

{
  "logic": "OR",
  "filters": [
    {
      "field": "status",
      "operator": "eq",
      "value": "Open"
    },
    {
      "field": "status",
      "operator": "eq",
      "value": "Pending"
    }
  ]
}

Nested Logic

Nesting logic filters allows you to build queries with nested logic like conditionX && (conditionY || conditionZ)

Example

Query all records where record.assignedToUser !== null && (record.status === "Open" || record.status === "Pending")

{
  "logic": "AND",
  "filters": [
    {
      "field": "assignedToUser",
      "operator": "isnull",
      "value": false
    },
    {
      "logic": "OR",
      "filters": [
        {
          "field": "status",
          "operator": "eq",
          "value": "Open"
        },
        {
          "field": "status",
          "operator": "eq",
          "value": "Pending"
        }
      ]
    }
  ]
}

Sort

Records can be sorted by field/direction using the "sort": [] list. This list contains objects with "field" and "direction" keys. The "field" key identifies which field (or association) to sort by. The "direction" key identifies whether to sort ascending ("ASC") or descending ("DESC"). Order is maintained when applying these sorts.

{
  "logic": "AND",
  "filters": [],
  "sort": [
    {
      "field": "createDate",
      "direction": "DESC"
    }
  ]
}

Aggregation

Records can be aggregated using the "aggregates": [] list. This list contains objects with "operator", "field", and "alias" keys. The "operator" key identifies which aggregate operator to apply to the specified field. The "field" key identifies the field to apply it to. The "alias" key identifies what the returned field looks like.

Supported Aggregate Operators

  • fields
  • select
  • count
  • countdistinct
  • groupby
  • distinct
  • sum
  • max
  • min
  • median
  • avg

Example

Count all records in each status grouping.

{
  "logic": "AND",
  "filters": [],
  "aggregates": [
    {
      "operator": "groupby",
      "field": "status",
      "alias": "status"
    },
    {
      "operator": "countdistinct",
      "field": "*",
      "alias": "total"
    }
  ]
}

Associations

Wherever a "field" key is specified in a query object, you can use dot-notation to query against an associated entity using the same operators that are available for the root record.

Example

Query all records assigned to a user named Jeff.

{
  "logic": "AND",
  "filters": [
    {
      "field": "assignedToUser.firstname",
      "operator": "eq",
      "value": "Jeff"
    }
  ]
}

Model Type

Use this API to search for records from different models like Incident, Alerts or any other custom models based on multiple valid fields in the model.

The API is: https://{{hostname}}/api/query/{{modelType}}. For example, https://{{hostname}}/api/query/incidents where incidents is a model in which you want to search for records. This API uses POST as the request method.

Example

BODY: 
{
    "aggregates": [
        {
            "operator": "groupby",
            "field": "phase.itemValue",
            "alias": "phase"
        },
        {
            "operator": "avg",
            "field": "dwellTime",
            "alias": "maxDwellTime"
        }
    ],
    "logic": "AND",
    "filters": []
}

Response: JSON list of records matching the filter criteria provided in the BODY of the modelType.

Query API Reference

API Routes

Apply Basic Query

GET /api/3/{collection}?param1=value&param2=value

For more information, see API Endpoints Reference chapter.

Apply Ad Hoc Query

POST /api/query/{collection}
BODY:
{ QUERY OBJECT }
  • The collection parameter matches the collection route in the typical CRUD API (/api/3/{collection})

Example

POST /api/query/incidents
BODY:
{
  "logic": "AND",
  "filters": [
    {
      "field": "status.itemValue",
      "operator": "eq",
      "value": "Open"
    }
  ]
}

Apply Persisted Query

GET /api/query/{collection}/{queryId}
  • The collection parameter matches the collection route in the typical CRUD API (/api/3/{collection})
  • The queryId parameter matches a Query Object UUID stored in /api/3/query_objects

Example

GET /api/query/incidents/2e77a714-f0c1-45ca-bd49-b71efbd9328c

Query Objects

Filter

A filter contains a field, operator, and a value.

{
"field": "{fieldName}",
"operator": "{operator}",
"value": {value}
}

Field

The field can be any valid field in the module. To access sub-elements of a relationship or picklist field, you can use dot notation or double-underscore notation.

"field": "name"
"field": "status.itemValue"
"field": "assignedToPerson.email"

Operator

The operator can be any of the available operators for a specific field type.

"operator": "eq"
"operator": "like"

For the complete list of supported operations, see the Supported Field Operators section.

Value

The value is the object of the filter or comparison. For example, if you are using the is like operator, then the value will be a pattern to match the record field.

"operator": "eq",
"value": "Alert 123: Repeated login failures - device xxx"
 
"operator": "like",
"value": "alert ___: Repeated login failures%"

For the complete list of supported operations, see the Supported Field Operators section.

Supported Field Operators

Operator Value Syntax Description
eq string Equals: Field is an exact match.
neq string Not Equals: Field is not an exact match.
lt number Less Than: Field has a lesser than value. In the case of Date/Time fields this translates to "Before", i.e. the date field is before the specified date.
lte number Less Than or Equal To: Field has a lesser than or equal to value. In the case of Date/Time fields this translates to "On or Before", i.e. the date field is on or after the specified date.
gt number Greater Than: Field has a greater than value. In the case of Date/Time fields this translates to "After", i.e. the date field is after the specified date.
gte number Greater Than or Equal To: Field has a greater than or equal to value. In the case of Date/Time fields this translates to "On or After", i.e. the date field is on the specified date or after the specified date.
in value1 | value2 ... In: Field is in the given list of values.
nin value1 | value2 ... Not In: Field is not in the given list of values.
contains string Contains: Object contains the key. Only applicable for field type Object.
like string pattern Like: Field matches the given pattern. The pattern can contain text, percent (%), or an underscrore (_)
% represents zero or more of any characters.
_ represents one character.
notlike string pattern Not Like: Field does not match the given pattern. The pattern can contain text, percent (%), or an underscrore (_)
% represents zero or more of any characters.
_ represents one character.
isnull boolean Is Null: If value is set as true, then this checks if the field is null. If value is set as false, then this checks if the field is not null.

Logic

AND

A collection of filters can be applied in conjunction using "logic": "AND"

Example

Query all records where record.assignedToUser !== null && record.status === "Open"

{
  "logic": "AND",
  "filters": [
    {
      "field": "assignedToUser",
      "operator": "isnull",
      "value": false
    },
    {
      "field": "status",
      "operator": "eq",
      "value": "Open"
    }
  ]
}

OR

A collection of filters can be applied in disjunction using "logic": "OR"

Example

Query all records where record.status === "Open" || record.status === "Pending"

{
  "logic": "OR",
  "filters": [
    {
      "field": "status",
      "operator": "eq",
      "value": "Open"
    },
    {
      "field": "status",
      "operator": "eq",
      "value": "Pending"
    }
  ]
}

Nested Logic

Nesting logic filters allows you to build queries with nested logic like conditionX && (conditionY || conditionZ)

Example

Query all records where record.assignedToUser !== null && (record.status === "Open" || record.status === "Pending")

{
  "logic": "AND",
  "filters": [
    {
      "field": "assignedToUser",
      "operator": "isnull",
      "value": false
    },
    {
      "logic": "OR",
      "filters": [
        {
          "field": "status",
          "operator": "eq",
          "value": "Open"
        },
        {
          "field": "status",
          "operator": "eq",
          "value": "Pending"
        }
      ]
    }
  ]
}

Sort

Records can be sorted by field/direction using the "sort": [] list. This list contains objects with "field" and "direction" keys. The "field" key identifies which field (or association) to sort by. The "direction" key identifies whether to sort ascending ("ASC") or descending ("DESC"). Order is maintained when applying these sorts.

{
  "logic": "AND",
  "filters": [],
  "sort": [
    {
      "field": "createDate",
      "direction": "DESC"
    }
  ]
}

Aggregation

Records can be aggregated using the "aggregates": [] list. This list contains objects with "operator", "field", and "alias" keys. The "operator" key identifies which aggregate operator to apply to the specified field. The "field" key identifies the field to apply it to. The "alias" key identifies what the returned field looks like.

Supported Aggregate Operators

  • fields
  • select
  • count
  • countdistinct
  • groupby
  • distinct
  • sum
  • max
  • min
  • median
  • avg

Example

Count all records in each status grouping.

{
  "logic": "AND",
  "filters": [],
  "aggregates": [
    {
      "operator": "groupby",
      "field": "status",
      "alias": "status"
    },
    {
      "operator": "countdistinct",
      "field": "*",
      "alias": "total"
    }
  ]
}

Associations

Wherever a "field" key is specified in a query object, you can use dot-notation to query against an associated entity using the same operators that are available for the root record.

Example

Query all records assigned to a user named Jeff.

{
  "logic": "AND",
  "filters": [
    {
      "field": "assignedToUser.firstname",
      "operator": "eq",
      "value": "Jeff"
    }
  ]
}

Model Type

Use this API to search for records from different models like Incident, Alerts or any other custom models based on multiple valid fields in the model.

The API is: https://{{hostname}}/api/query/{{modelType}}. For example, https://{{hostname}}/api/query/incidents where incidents is a model in which you want to search for records. This API uses POST as the request method.

Example

BODY: 
{
    "aggregates": [
        {
            "operator": "groupby",
            "field": "phase.itemValue",
            "alias": "phase"
        },
        {
            "operator": "avg",
            "field": "dwellTime",
            "alias": "maxDwellTime"
        }
    ],
    "logic": "AND",
    "filters": []
}

Response: JSON list of records matching the filter criteria provided in the BODY of the modelType.