Question & Answer
Question
I am trying to run a search in QRadar 7.4.0 fix pack 3, and everytime I run the search, it yield different number of result count. When I run the main search, it gives me the expected number of result count. It looks like the issue is related to the subquery.
SELECT
DATEFORMAT(devicetime, 'yyyy-MM-dd hh:mm:ss a') as 'DateTime', "EventID", QIDDESCRIPTION(qid),
LOGSOURCENAME(logsourceid), "Handle ID", "Logon ID", "File Path", username
FROM events
WHERE
("Logon ID" IN (SELECT "Logon ID" FROM events
WHERE
LOGSOURCETYPENAME(devicetype) = 'Microsoft Windows Security Event Log'
AND LOGSOURCENAME(logsourceid)='server name' AND "EventID"='4663')) AND "EventID"<>'5140'
ORDER BY username ASC
LAST 24 HOURS
Answer
The issue is in the subquery. You have not specified the time range in which the subquery should be executed, therefore by default QRadar will randomly choose this time frame from 5 mins up. For each time frame, the result count returned should be expected to be different.
You can confirm this by running the subquery only and observe its behavior. Also keep in mind that the maximum result count in QRadar is 10000. In such case, if the result from the subquery is more than 10000, QRadar will present them as a round robin (meaning it will be choosing a number from 1 to 10000 to display randomly).
You are welcome to specify a result limit in the AQL query.
The best way to go about this is edit the subquery and add
- GROUP BY clause
- TIME FRAME to limit the search within a specific time period. You may want this time frame to match the time frame of the main query.
For example:
SELECT
DATEFORMAT(devicetime, 'yyyy-MM-dd hh:mm:ss a') as 'DateTime', "EventID", QIDDESCRIPTION(qid),
LOGSOURCENAME(logsourceid), "Handle ID", "Logon ID", "File Path", username
FROM events
WHERE ("Logon ID" IN (SELECT "Logon ID" FROM events
WHERE LOGSOURCETYPENAME(devicetype) = 'Microsoft Windows Security Event Log' AND
LOGSOURCENAME(logsourceid) = 'server name' AND "EventID" = '4663'
GROUP BY "Logon ID"
LAST 24 HOURS)) AND "EventID" <> '5140'
ORDER BY username ASC
LAST 24 HOURS
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000TNAHAA4","label":"ATS-SecIntel Backup->QRadar Incident Forensics->Query Filters"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Product Synonym
QRadar SIEM
Was this topic helpful?
Document Information
Modified date:
07 July 2020
UID
ibm16244632