IBM Support

IV85447: REPORTS AND DASHBOARDS BASED ON SOME ADVANCED (AQL) SEARCHES MIGHT NOT WORK AS EXPECTED

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Reports based on saved Advanced Searches (AQL) sometimes do not
    generate with expected data.
    The data returned can be different when run on schedule
    compared to when run on raw data and/or when manually using the
    saved search for a specified period of time.
    In the case of Dashboards, there can be misisng and/or extra
    columns.
    
    Example:
    A search similar to the following works in Log Activity, but
    the Global View created from is incorrect leading to reports
    and dashboards that are broken.
    
         SELECT
         ASSETUSER(sourceip, events.endtime) as 'UserName',
         sourceip AS 'Source IP',
         destinationip AS 'Disqus IP',
         DATEFORMAT(startTime,'YYYY-MM-dd HH:mm:ss') as StartTime,
         COUNT(*) as 'Total Events'
         FROM events
         WHERE 'UserName' IS NOT NULL
         GROUP BY 'UserName', 'Source IP', events.destinationip
         ORDER BY 'Total Events' DESC
         LAST 24 HOURS
    Dashboard items can show extra/missing columns.
    
    The issue is with using sourceip in a an Advanced Search (AQL)
    function, prior to it being defined as a field.
    

Local fix

  • For the example above:
    1) Ensure that the field in the Advanced Search (AQL) query is
    being defined before it is used.
         SELECT
         sourceip AS 'Source IP',
         destinationip AS 'Disqus IP',
         DATEFORMAT(startTime,'YYYY-MM-dd HH:mm:ss') as 'StartTime',
         ASSETUSER(sourceip, events.endtime) as 'UserName',
         COUNT(*) as 'Total Events'
         FROM events where "UserName" is not null
         GROUP BY "UserName", "Source IP", "Disqus IP"
         ORDER BY "Total Events" DESC
         last 1 hours
    2) Also ensure all quotes are correct.  Single quotes 'value'
    for defining a literal string or column name, and double quote
    "field" for accessing the fields like for GROUP BY and WHERE
    clauses.
    

Problem summary

  • This issue was resolved with QRadar 7.2.7 Patch 1.
    

Problem conclusion

  • This issue was resolved with QRadar 7.2.7 Patch 1.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IV85447

  • Reported component name

    QRADAR SOFTWARE

  • Reported component ID

    5725QRDSW

  • Reported release

    726

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-06-03

  • Closed date

    2016-08-04

  • Last modified date

    2016-08-04

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    QRADAR SOFTWARE

  • Fixed component ID

    5725QRDSW

Applicable component levels

  • R727 PSY

       UP



Document information

More support for: IBM QRadar SIEM

Software version: 726

Reference #: IV85447

Modified date: 04 August 2016