IBM Support

APAR IJ25142: Scheduled reports and time series data can display incorrect output when certain AQL functions are used in accumulated data

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 valueHelp
  • 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:
image 4067

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.
image 4065
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. 
image 4066
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"}}]

Document Information

Modified date:
24 June 2020

UID

ibm16221278

Page Feedback