IBM Support

Cloud Pak for Security: AQL Query does not retrieve information in Data Explorer

Troubleshooting


Problem

Users run an AQL query in QRadar that returns results on a requested offense, but when users run the same AQL query in Cloud Pak for Security no results are returned.

Environment

AQL version 2.0.1

Diagnosing The Problem

The query works in QRadar but not in Cloud Pak for Security.

Resolving The Problem

The AQL query must be modified for it to work correctly in CP4S.
  1. Given the following example of the AQL query that runs in QRadar without issue: 
    SELECT QIDNAME(qid),
DATEFORMAT(starttime,'YYYY-MM-dd HH:mm') as StartTime,
CATEGORYNAME(category),
LOGSOURCENAME(logsourceid),
"sourceIP" as 'SourceIP',
"sourcePort" as 'SourcePort',
"destinationIP" as 'DestinationIP',
"destinationPort" as 'DestinationPort',
"userName" as 'Username',
COUNT("eventCount") as 'EventCount',
UTF8(payload),
MIN(starttime) as 'starttime',
MAX(endtime) as 'endtime',
eventcount FROM events WHERE (INOFFENSE(XXXXX)) LAST 4320 MINUTES
    Note: Where (XXXXX), is the requested offense ID.
  2. The fields must be renamed so they don't get mapped to STIX: 
    SELECT QIDNAME(qid) as qidname,
CATEGORYNAME(category) as categoryname,
LOGSOURCENAME(logsourceid) as logsourcename,
"sourceIP" as sourceip,
"sourcePort" as sourceport,
"destinationIP" as destinationip,
"destinationPort" as destinationport,
"userName" as username,
UTF8(payload) as eventpayload,
starttime,
endtime eventcount FROM events WHERE (INOFFENSE(XXXXX)) LAST 4320 MINUTES
    Note: Where (XXXXX), is the requested offense ID.
    Information: The difference between the queries is the information that is retrieved from QRadar, in this type of scenario, is converted with the connector. Users can build custom AQL queries to obtain specific results relevant to their configuration. 
Result
In the initial QRadar query just `QIDNAME(qid)` was specified, but for CP4S the request must be renamed to `QIDNAME(qid) as qidname` in order to map to a result.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTDPP","label":"IBM Cloud Pak for Security"},"ARM Category":[{"code":"a8m0z0000001h8kAAA","label":"Data Explorer"}],"ARM Case Number":"TS010667657","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.10.0"}]

Document Information

Modified date:
09 March 2023

UID

ibm16891087

Page Feedback