IBM Support

QRadar: Report fails on error message "The following chart could not have their aggregated view created due to invalid criteria or column"

Troubleshooting


Problem

When you run a scheduled report with the Run Report option, it might not generate data and display the following error message:
image 7304

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

  1. SSH into the QRadar Console as the root user.
  2. 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 countmin, 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 

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)"}]

Document Information

Modified date:
03 December 2020

UID

ibm16343351