QRadarCreateAQLQuery

Build QRadar AQL Query.

python · IBM QRadar

Details

IDQRadarCreateAQLQuery
Languagepython
From Version6.0.0
Docker Imagedemisto/python3:3.12.13.10116658
TagsUtility

README

Build QRadar AQL Query. (Available from Cortex XSOAR 6.0.0).

Script Data

Name Description
Script Type python3
Tags Utility
Cortex XSOAR Version 6.0.0

Inputs

Argument Name Description
base_values_to_search The values of the first field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
base_fields_to_search The field names of the first field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
base_field_state The state of the second field to search, meaning whether the values in the field should be included or excluded. Valid options are include or exclude.
base_field_match Whether the values of the second field should be an exact match or a partial match. Valid options are exact or partial.
select_fields The list of fields to select within the AQL query. The default fields are DATEFORMAT(devicetime,’dd-MM-yyyy hh:mm’),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username
time_frame Time frame as used in AQL examples can be LAST 7 DAYS START ‘2019-09-25 15:51’ STOP ‘2019-09-25 17:51’. For more examples, view IBM’s AQL documentation.
first_additional_values The values of the second field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
first_additional_fields The field names of the second field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
first_additional_field_state The state of the second field to search, meaning whether the values in the field should be included or excluded. Valid options are include or exclude.
first_additional_field_match Whether the values of the second field should be an exact match or a partial match. Valid options are exact or partial.
second_additional_values The values of the third field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2
second_additional_fields The field names of the third field to search. This can be a single value or a comma-separated list of values. For example username,user
second_additional_field_state The state of the third field to search, meaning whether the values in the field should be included or excluded. Valid options are include or exclude.
second_additional_field_match Whether the values of the third field should be an exact match or a partial match. Valid options are exact or partial. When choosing exact, the AQL query will use the = operator. When choosing partial, the AQL query will use ILIKE and add ‘%%’ to the values.

Outputs

Path Description Type
QRadarQuery The resultant AQL query based on the inputs. string

Script Example Search for a hash where we dont know the field

!QRadarCreateAQLQuery base_field_match=partial base_values_to_search=2367666DB8DFF58982A74695760E3EF0ACEBD050

Context Example

{
    "QRadarQuery": "select DATEFORMAT(devicetime,'dd-MM-yyyy hh:mm'),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (UTF8(payload) ILIKE '%2367666DB8DFF58982A74695760E3EF0ACEBD050%') LAST 1 HOURS"
}

Human Readable Output

select DATEFORMAT(devicetime,’dd-MM-yyyy hh:mm’),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (UTF8(payload) ILIKE ‘%2367666DB8DFF58982A74695760E3EF0ACEBD050%’) LAST 1 HOURS

Script Example Search for a hash in specific fields

!QRadarCreateAQLQuery base_field_match=exact base_values_to_search=2367666DB8DFF58982A74695760E3EF0ACEBD050 base_fields_to_search=sha1,sha1-hash

Context Example

{
    "QRadarQuery": "select DATEFORMAT(devicetime,'dd-MM-yyyy hh:mm'),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (sha1 = '2367666DB8DFF58982A74695760E3EF0ACEBD050' OR sha1-hash = '2367666DB8DFF58982A74695760E3EF0ACEBD050') LAST 1 HOURS"
}

Human Readable Output

select DATEFORMAT(devicetime,’dd-MM-yyyy hh:mm’),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (sha1 = ‘2367666DB8DFF58982A74695760E3EF0ACEBD050’ OR sha1-hash = ‘2367666DB8DFF58982A74695760E3EF0ACEBD050’) LAST 1 HOURS

Script Example Search for user and hash

!QRadarCreateAQLQuery base_field_match=exact base_values_to_search=2367666DB8DFF58982A74695760E3EF0ACEBD050 base_fields_to_search=sha1,sha1-hash first_additional_fields=username first_additional_values=admin

Context Example

{
    "QRadarQuery": "select DATEFORMAT(devicetime,'dd-MM-yyyy hh:mm'),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (sha1 = '2367666DB8DFF58982A74695760E3EF0ACEBD050' OR sha1-hash = '2367666DB8DFF58982A74695760E3EF0ACEBD050') AND (username = 'admin') LAST 1 HOURS"
}

Human Readable Output

select DATEFORMAT(devicetime,’dd-MM-yyyy hh:mm’),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (sha1 = ‘2367666DB8DFF58982A74695760E3EF0ACEBD050’ OR sha1-hash = ‘2367666DB8DFF58982A74695760E3EF0ACEBD050’) AND (username = ‘admin’) LAST 1 HOURS

Script Example Search for a hash in payload that doesnt contain admin

!QRadarCreateAQLQuery base_field_match=exact base_values_to_search=2367666DB8DFF58982A74695760E3EF0ACEBD050 base_fields_to_search=sha1 first_additional_field_state=exclude first_additional_field_match=partial first_additional_values=admin

Context Example

{
    "QRadarQuery": "select DATEFORMAT(devicetime,'dd-MM-yyyy hh:mm'),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (sha1 = '2367666DB8DFF58982A74695760E3EF0ACEBD050') AND (UTF8(payload) NOT ILIKE '%admin%') LAST 1 HOURS"
}

Human Readable Output

select DATEFORMAT(devicetime,’dd-MM-yyyy hh:mm’),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username from events where (sha1 = ‘2367666DB8DFF58982A74695760E3EF0ACEBD050’) AND (UTF8(payload) NOT ILIKE ‘%admin%’) LAST 1 HOURS

Source

QRadarCreateAQLQuery.py QRadarCreateAQLQuery_test.py QRadarCreateAQLQuery.yml
Download History Raw 
commonfields:
  id: QRadarCreateAQLQuery
  version: -1
name: QRadarCreateAQLQuery
script: ''
type: python
tags:
- Utility
comment: Build QRadar AQL Query.
args:
- name: base_values_to_search
  required: true
  isArray: true
  description: The values of the first field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
- name: base_fields_to_search
  isArray: true
  description: The field names of the first field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
- name: base_field_state
  required: true
  auto: PREDEFINED
  predefined:
  - include
  - exclude
  defaultValue: include
  description: The state of the first field to search, meaning whether the values in the field should be included or excluded.
- name: base_field_match
  required: true
  auto: PREDEFINED
  predefined:
  - exact
  - partial
  defaultValue: exact
  description: Whether the values of the second field should be an exact match or a partial match.
- name: select_fields
  required: true
  description: The list of fields to select within the AQL query.
  defaultValue: DATEFORMAT(devicetime,'dd-MM-yyyy hh:mm'),LOGSOURCENAME(logsourceid),CATEGORYNAME(category),QIDNAME(qid),sourceip,destinationip,username
- name: time_frame
  required: true
  description: Time frame as used in AQL examples can be LAST 7 DAYS START '2019-09-25 15:51' STOP '2019-09-25 17:51'. For more examples, view IBM's AQL documentation.
  defaultValue: LAST 1 HOURS
- name: first_additional_values
  description: The values of the second field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
  isArray: true
- name: first_additional_fields
  description: The field names of the second field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
  isArray: true
- name: first_additional_field_state
  required: true
  auto: PREDEFINED
  predefined:
  - include
  - exclude
  description: The state of the second field to search, meaning whether the values in the field should be included or excluded.
  defaultValue: include
- name: first_additional_field_match
  required: true
  auto: PREDEFINED
  predefined:
  - exact
  - partial
  description: Whether the values of the second field should be an exact match or a partial match.
  defaultValue: exact
- name: second_additional_values
  description: The values of the third field to search. This can be a single value or a comma-separated list of values. For example admin1,admin2.
- name: second_additional_fields
  description: The field names of the third field to search. This can be a single value or a comma-separated list of values. For example username,user.
- name: second_additional_field_state
  required: true
  auto: PREDEFINED
  predefined:
  - include
  - exclude
  description: The state of the third field to search, meaning whether the values in the field should be included or excluded.
  defaultValue: include
- name: second_additional_field_match
  auto: PREDEFINED
  predefined:
  - exact
  - partial
  description: Whether the values of the third field should be an exact match or a partial match. When choosing exact, the AQL query will use the = operator. When choosing partial, the AQL query will use ILIKE and add '%%' to the values.
  defaultValue: exact
outputs:
- contextPath: QRadarQuery
  description: The resultant AQL query based on the inputs.
  type: string
subtype: python3
dockerimage: demisto/python3:3.12.13.10116658
runas: DBotWeakRole
fromversion: 6.0.0
tests:
- No tests (auto formatted)
supportedModules:
- agentix
- xsiam
- edr