IBM Security QRadar

 View Only
  • 1.  Rule Logic to get the sum of ByteSent by a user within one hour

    Posted Tue June 15, 2021 09:31 AM
    Hi All,

    I am not able to use the below AQL query in rule test. I am using QRadar 7.4.0 and earlier I was able to do so in QRadar 7.3.3 

    SUM("BytesSent") > 1111111111 group by sourceip

    Desc: I am trying to get the sourceips which are sending more than 100 MB of data.
    Please let me know how can achieve the same using some alternate method.



    ------------------------------
    Manzar Alam
    ------------------------------


  • 2.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    IBM Champion
    Posted Wed June 16, 2021 05:26 AM
    Hi,
    the restrictions of SUM function usage in your incomplete AQL sample is not dependent on 7.3.3 release, but depends on the context of function usage being restricted in general.
    Please use type ahead function in AQL to see what is allowed in your specific release environment. That is part of our QA process as QRadar consultants. If not you receive strange parse errors as shown. If your context usage is correct it will work for you as shown in my sample, which BTW is a demo search for your use case. Unfortunately I have no historic flow usecase available using SUM and flows. Tested on 7.3.3 and 7.4.3. Unfortunately there is no guarantee that AQL functions used in customer use cases will work forever :-)

    error sample


    ------------------------------
    [Karl] [Jaeger] [Business Partner]
    [QRadar Specialist]
    [pro4bizz]
    [Karlsruhe] [Germany]
    [4972190981722]
    ------------------------------



  • 3.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    Posted Wed June 16, 2021 07:21 AM
    Hi Karl,

    Thank you for your response.

    I would like to clarify here that in rule test you only need the apply the query which is coming after where, that is why i have mentioned the half query.
    Also the query written by me works fine in Log Activity Advance search but it does not work on rule test.
     
    Can you please run the query in rule test and let me know if i am making any mistake.

    ------------------------------
    Manzar Alam
    ------------------------------



  • 4.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    IBM Champion
    Posted Wed June 16, 2021 11:42 AM
    Hi,
    as outlined before: best practice and test reference is AQL search. Your search does not work. Thats bad. Log activity AQL search using "select BytesSent, sourceip from events group by sum("BytesSent") > 111111111 group by sourceip" fails as shown below. Not sure what you are trying to achieve but your assumptions are wrong. See screenshot below. Our best practice is AQL search first than rule test.


    ------------------------------
    [Karl] [Jaeger] [Business Partner]
    [QRadar Specialist]
    [pro4bizz]
    [Karlsruhe] [Germany]
    [4972190981722]
    ------------------------------



  • 5.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    Posted Wed June 16, 2021 09:14 AM
    Hi,

    I don't think that you can do this with an AQL test.
    This kind of usecase should be monitored by a thresold rule ( https://www.ibm.com/docs/en/qsip/7.4?topic=rules-anomaly-detection )

    Regards

    ------------------------------
    Thibaut Stauder
    ------------------------------



  • 6.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    IBM Champion
    Posted Wed June 16, 2021 11:44 AM
    correct!

    ------------------------------
    [Karl] [Jaeger] [Business Partner]
    [QRadar Specialist]
    [pro4bizz]
    [Karlsruhe] [Germany]
    [4972190981722]
    ------------------------------



  • 7.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    IBM Champion
    Posted Wed June 16, 2021 12:15 PM
    BTW if "rule test" refers to use case manager test definition you are definitely on the wrong track. If not pls sent screenshot
    all other readers: pls dont use this as an example!


    ------------------------------
    [Karl] [Jaeger] [Business Partner]
    [QRadar Specialist]
    [pro4bizz]
    [Karlsruhe] [Germany]
    [4972190981722]
    ------------------------------



  • 8.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    Posted Mon June 21, 2021 01:43 AM
    Hi Karl and Thibaut,

    Thank you for your response.

    This is a limitation of Qradar, And this introduced in Qradar 7.3.2 with APAR IJ13437: AQL QUERY CONTAINING AGGREGATE FUNCTIONS IN THE 'WHERE' CLAUSE GENERATES AN AQLPARSEREXCEPTION (ibm.com) and IJ13446: INVALID AQL SAVED SEARCHES CAN CAUSE SEVERAL USER INTERFACE SCREENS TO FAIL TO LOAD (ibm.com)

    No aggregated function allowed to be used in where clause in AQL.
    This is why we see the error in rule wizard, since it is a AQL filter, which is a where clause, and aggregated function like SUM is not allowed.

    Thank You.


    ------------------------------
    Manzar Alam
    ------------------------------



  • 9.  RE: Rule Logic to get the sum of ByteSent by a user within one hour

    IBM Champion
    Posted Mon June 21, 2021 03:52 AM
    Manzar! Thanks for clearing this up.
    the integrated semantic checks do not work in every case but are raising exceptions quite often as seen in your case dependant on version and patchlevel used. That's why we focus so mutch on testing in our procedures and training.
    just for curiosity: did you browse thru all APAR reports or did you receive feedback from IBM support?

    ------------------------------
    [Karl] [Jaeger] [Business Partner]
    [QRadar Specialist]
    [pro4bizz]
    [Karlsruhe] [Germany]
    [4972190981722]
    ------------------------------