Troubleshooting
Problem
Administrators who create scheduled reports that include AQL lookups or mathematical functions can experience issues where reports do not display column data correctly or duplicate or incorrect data. This issue is caused by AQL functions where accumulated data in the report would require a lookup of data, instead of displaying a static value. The accumulator, which is used to draw graphs and reports for charts references static data. This article is intended to advise administrators on AQL functions that ought to be excluded from reports or time series graphs and is associated to APAR IJ25142.
Symptom
When AQL issue occurs, administrators might experience the following issues when reports are generated:
- Scheduled reports on AQL searches can show incorrect column name and column data
- AQL-based reports does not show calculated value
- Reports generating with duplicated column and incorrect column data
Cause
The following AQL functions ought be removed from reports or time series graph data as it can cause display issues:
-
(AVG(magnitude) - MIN(magnitude)) AS MAGDIFF
-
LONG(SUM(eventcount) / ((MAX(endtime) -min(starttime) +1)
-
REFERENCEMAP('Refname',username) as 'qradaruser',
-
REFERENCETABLE('reftable','user', LOWER(username)) as 'qradaruser',
-
GLOBALVIEW
-
GEO::LOOKUP
-
GEO::DISTANCE
-
MATCHESASSETSEARCH
-
PARAMETERS EXCLUDESERVERS
-
PARAMETERS REMOTESERVERS
-
REFERENCEMAP
-
REFERENCEMAPSETCONTAINS
-
REFERENCETABLE
-
REFERENCESETCONTAINS
-
Sourceip, destionationip that contains superflows
Environment
Daily, weekly, monthly reports, or time series graphs that use calculated AQL functions or lookup features in QRadar® 7.3.x or 7.4.x.
Diagnosing The Problem
Administrators can review for reports or time series graphs to determine whether functions that are calculated display as unique columns or display in drop-down lists as a filterable selections.
Example query:
Example query:

User interface issues
Users who attempt to configure a Time Series graph for search can see calculated values treated as individual data entries in the user interface.

Figure 1: Administrators who use AQL calculations in a graph might see values listed in the graph configuration that are portions of their calculations.
Search results incorrect
The Time Series graph cannot calculate the AQL used in the query, so the data displays 0.0 EPS, even though the table displays the correct EPS. Graph data is based on accumulations, which cannot be calculations or lookup functions by design.

Search results incorrect
The Time Series graph cannot calculate the AQL used in the query, so the data displays 0.0 EPS, even though the table displays the correct EPS. Graph data is based on accumulations, which cannot be calculations or lookup functions by design.

Figure 2: Time series graphs do not display correct data as the min and max values are calculated properties.
Resolving The Problem
Administrators can review the parameters of the AQL query that is used in the search or report when a graph does not display properly. The AQL parameters listed in this article must be excluded from the AQL query to display in reports and time series graphs in the user interface. The accumulator was not designed to calculate and aggregate data from static functions.
To subscribe to this issue, review APAR IJ25142.
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000GnblAAC","label":"QRadar->Search"}],"ARM Case Number":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"All Version(s)","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Was this topic helpful?
Document Information
Modified date:
24 June 2020
UID
ibm16221278