IBM QRadar

IBM QRadar

Join this online user group to communicate across Security product users and IBM experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Back to discussions
Expand all | Collapse all

Report / Dashboard of open and closed offenses

  • 1.  Report / Dashboard of open and closed offenses

    Posted Wed February 01, 2023 08:46 AM
    Hello,

    I'm trying to create a dashboard (Pulse) or report (via Log Activity) to show open and closed offenses per week.
    I've tried to use AQL search in Dashboard but was unable to find. way how to filter open and closed offenses.
    Via "offense" filter (Pulse Dashboard) I can filter status, but have to manually update time each week.
    Same goes for API, status, start / close time, id is fine but not sure how to set time, for example "last 7 days" etc.

    Dashboard / Report just needs to show how many open or closed offenses there were past week.

    Thanks in advance for any advice
    b.r
    Petr

    ------------------------------
    Petr Kavalik
    ------------------------------


  • 2.  RE: Report / Dashboard of open and closed offenses

    Posted Wed February 01, 2023 11:59 AM
    Petr,
    I have used offenses by user and modified it like this:

    SELECT "userName" AS 'Username', UniqueCount("CRE Name") AS 'CRE Name (custom) (Unique Count)', UniqueCount(qid) AS 'Event Name (Unique Count)', UniqueCount(category) AS 'Low Level Category (Unique Count)', UniqueCount("sourceIP") AS 'Source IP (Unique Count)', UniqueCount("destinationIP") AS 'Destination IP (Unique Count)', UniqueCount("destinationPort") AS 'Destination Port (Unique Count)', MIN("magnitude") AS 'Magnitude (Minimum)', SUM("eventCount") AS 'Event Count (Sum)', COUNT(*) AS 'Count' from events where ( ( "deviceType"='18' AND "hasOffense"='true' AND "Offense Closed Reason" != 'null') AND "userName" != 'null' ) GROUP BY "userName" order by "Event Count (Sum)" desc last 7 DAYS

    when defining your custom dashboard inside pulse just copy your log activity AQL into your AQL statement window
    all other parameters are default

    Of course you can skip group by username if you like.
    Hope this helps


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

    Original Message


  • 3.  RE: Report / Dashboard of open and closed offenses

    Posted Thu February 02, 2023 04:39 AM
    Edited by Petr Kavalik Thu February 02, 2023 06:18 AM

    Hi Karl,

    This looks great, however seems like I'm getting error while using this search as AQL doesn't recognize "Offense Closed Reason" field:

    The query_expression contains invalid AQL syntax.
    The query_expression contains invalid AQL syntax. - Field "Offense Closed Reason" does not exist in catalog "events"

    Could you please advice how to define / extract Status or Close Reason field from Offense so I can use it in AQL?

    Thanks a lot for your help.
    Best regards
    Petr


    ------------------------------
    Petr Kavalik
    ------------------------------

    Original Message


  • 4.  RE: Report / Dashboard of open and closed offenses

    Posted Thu February 02, 2023 08:44 AM
      |   view attached
    Hi Petr,
    I used <AND "Offense Closed Reason" != 'null'> as criteria as whenever closing an offense you must give a reason. You should be able to further structure your report when differentiating the close reasons numbers, while reprorting over the different close reasons given. The search listed in here does work on 7.5 demo instance at IBM cloud. It does work as log activity search just as well. Please use the search given here or use standard search for user offenses and modify date and condition.
    AQL code sample based on standard search (see screenshot)

    SELECT "userName" AS 'Username', UniqueCount("CRE Name") AS 'CRE Name (custom) (Unique Count)', UniqueCount(qid) AS 'Event Name (Unique Count)', UniqueCount(category) AS 'Low Level Category (Unique Count)', UniqueCount("sourceIP") AS 'Source IP (Unique Count)', UniqueCount("destinationIP") AS 'Destination IP (Unique Count)', UniqueCount("destinationPort") AS 'Destination Port (Unique Count)', MIN("magnitude") AS 'Magnitude (Minimum)', SUM("eventCount") AS 'Event Count (Sum)', COUNT(*) AS 'Count' from events where ( ( "deviceType"='18' AND "hasOffense"='true' AND "Offense Closed Reason" != 'null') AND "userName" != 'null' ) GROUP BY "userName" order by "Event Count (Sum)" desc last 7 days

    Regards
    Karl

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

    Original Message


  • 5.  RE: Report / Dashboard of open and closed offenses

    Posted Thu February 02, 2023 09:44 AM
    I've tried to use AQL in Log Activity Search but got same error. Customer is using 7.5.0 UpdatePackage 2 (Build 20220527130137) with following Installed Oprtions / Extensions:

    It's possible that some extension can add new filters to AQL?
    Could be that Cloud version has a bit different AQL filter than non cloud one?
    We always close offenses with reasons, however not quite sure why is customer missing that AQL filter "Offense Closed Reason"

    I have found following post:
    "this can be done by performing a log search using the SIM Audit log source and QID 28250021 (Offense Closed). You will have to extract the Closed Reason using Regex. Once this has been done, you can add the Custom Property as an additional column in your search, save it and then use this search when creating your report."

    Thanks,
    Best regards
    Petr

    ------------------------------
    Petr Kavalik
    ------------------------------

    Original Message


  • 6.  RE: Report / Dashboard of open and closed offenses

    Posted Thu February 02, 2023 09:57 AM

    Hi Petr

    I believe what you are looking for is the IBM QRadar Security Analytics Self Monitoring content extension.
    The extension contains the property Offense Closed Reason as well as a report on Offenses (QRadar Audit - Offense Closure Report).

    If you are interested to know more about this content pack, I wrote 2 blogs to explain its content:
    Did you think of monitoring QRadar ?
    How are you checking your QRadar deployment ?

    I hope this helps



    ------------------------------
    Gladys Koskas
    ------------------------------

    Original Message


  • 7.  RE: Report / Dashboard of open and closed offenses

    Posted Fri February 03, 2023 02:24 AM
    Hi Gladys,

    Looks like it. I've forwarded this info to customer as they have to decided about that extension along with links to your blogs. Very interesting blogs.
    Hope they will install that extension, otherwise I don't see doable creating dashboard / reports as they requested.

    Thanks a lot for your help
    Petr

    ------------------------------
    Petr Kavalik
    ------------------------------

    Original Message


  • 8.  RE: Report / Dashboard of open and closed offenses

    Posted Thu February 02, 2023 11:54 AM
    Hi Petr,

    this sounds like you're looking for something like this:

    Maybe this AQL will help you:

    SELECT QIDNAME(qid) AS 'Event Name', "Offense ID" AS 'Offense ID', dateformat(MIN("startTime"),'dd.MM.yy hh:mm:ss') AS 'Start Time (Minimum)', UNIQUECOUNT("userName") AS 'Username', COUNT(*) AS 'Count' from events where ( ( category='16003' AND logSourceId='64' ) AND (qid='28250021') or (qid='28250180') or (qid='28250369') ) GROUP BY qid, "Offense ID" order by "Count" desc LIMIT 1000 last 7 days

    Regards,
    Ralph

    ------------------------------
    Ralph Belfiore
    SIEM Expert
    pro4bizz GmbH
    Karlsruhe
    +4972190981727
    ------------------------------

    Original Message


  • 9.  RE: Report / Dashboard of open and closed offenses

    Posted Fri February 03, 2023 02:29 AM
    Hi Ralph,

    Thats another great AQL search. However with this one, customers QRadar doesn't recognize field "Offense ID".
    Could you please confirm or deny that you have extension Gladys linked installed?
    Or did you add those extra fields like "Offense ID" or "Offense Closed Reason" by any other way?

    Thanks a lot for your help
    Petr

    ------------------------------
    Petr Kavalik
    ------------------------------

    Original Message


  • 10.  RE: Report / Dashboard of open and closed offenses

    Posted Fri February 03, 2023 05:25 AM
    Hi Petr,

    yes, i am using the "IBM QRadar Analytics Self Monitoring Content Extension". I rolled out this content package in particular, right after it was released :)
    With this Content Package those Custom Event Properties and some more will be made available.

    Regards,
    Ralph

    ------------------------------
    Ralph Belfiore
    SIEM Expert
    pro4bizz GmbH
    Karlsruhe
    +4972190981727
    ------------------------------

    Original Message


  • 11.  RE: Report / Dashboard of open and closed offenses

    Posted Mon February 06, 2023 08:47 AM
    Edited by Petr Kavalik Tue February 07, 2023 06:01 AM
    Hi Ralph,

    Extension was installed a I can get quite nice "open vs closed offense" pie graph.
    Would like to also ask about another dashboard. Do you have any idea how to make dashboard (widget) for specific log source group to list closed and open offenses.

    I've tried various combinations but cant get proper results while using any of: logsourceid, sourceip, dstip.
    Do you know how to create AQL to list all offenses by single log source?
    I can get this number from Offense search, but not by using AQL.
    Is this even doable by AQL?

    Thanks,
    Regards
    Petr

    ------------------------------
    Petr Kavalik
    ------------------------------

    Original Message


Related Content