Troubleshooting
Problem
When you run a scheduled report with the Run Report option, it might not generate data and display the following error message:
Cause
The report fails due to the lack of an aggregate function in the AQL search used in this function.
Here is a sample of an AQL that would cause the error message:
SELECT DATEFORMAT(devicetime,'yyyy-MM-dd') AS "Date", sourceip AS "Sourceip", username AS "Username" FROM events WHERE
qid = '1234' OR qid = '5678' OR qid = '29123' AND username IS NOT NULL
GROUP BY username,"DATE"
ORDER BY username ASC
Below the corrected AQL sample working for the report using aggregate functions.
SELECT DATEFORMAT(devicetime,'yyyy-MM-dd') AS "Date", sourceip AS "Sourceip", username AS "Username", SUM(eventcount) AS "Number of Events in Interval" FROM events WHERE
qid = '1234' OR qid = '5678' OR qid = '9123' AND username IS NOT NULL
GROUP BY username,"DATE"
ORDER BY username ASC
Diagnosing The Problem
- SSH into the QRadar Console as the root user.
- Using the following grep command to extract the error log message that points to this behavior:
Note: You can also search this error message in the /var/log/qradar.error file using the same grep command.
grep 'Global View Creation failed' /var/log/qradar.log | less
Oct 6 16:06:15 ::ffff:1.1.1.1 [tomcat.tomcat] [admin@1.1.1.1 (2884) /console/do/core/genericsearchlist] com.q1labs.reporting.ReportServices: [ERROR] [NOT:0000003000][1.1.1.1/- -] [-/- -]Global View Creation failed: The accumulated data view cannot be created because your saved search's select properties are not countable. Please put them in one of the aggregate functions including count, sum min and max etc.
Resolving The Problem
To resolve this issue, you should include an aggregate function as count, min, and max in the AQL statement.
Note: In the following example, the AQL statement, we are using the SUM(eventcount) as an aggregate function:
SELECT DATEFORMAT(devicetime,'yyyy-MM-dd') AS "Date", sourceip AS "Sourceip", username AS "Username", SUM(eventcount) AS "Number of Events in Interval" FROM events WHERE
qid = '1234' OR qid = '5678' OR qid = '9123' AND username IS NOT NULL
GROUP BY username,"DATE"
ORDER BY username ASC
Related Information
Document Location
Worldwide
[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwstAAA","label":"Accumulator"},{"code":"a8m0z000000cwtmAAA","label":"Reports"}],"ARM Case Number":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
Modified date:
03 December 2020
UID
ibm16343351