IBM Security QRadar

 View Only
Expand all | Collapse all

Offenses Monthly report

  • 1.  Offenses Monthly report

    Posted Tue January 12, 2021 04:08 PM
    HI team,

    Can anyone provide AQL query to get Monthly offense report like each day how many offenses generated for the customer .I need to show in chart format. I tried but i'm not getting exact data

    ------------------------------
    Abhishek Kakkireni
    ------------------------------


  • 2.  RE: Offenses Monthly report

    Posted Wed January 13, 2021 01:45 AM
    Dear Abhishek,

    Yes you can. There is an QID related to offense generation in IBM Qradar. You can check it via event name search in log activity. Then you can write AQL and import that in the pulse dashboard as well.

    Regards,
    Abdul Qudoos



    Original Message


  • 3.  RE: Offenses Monthly report

    IBM Champion
    Posted Wed January 13, 2021 05:12 PM
    Hi Abhishek,

    maybe this could be an idea or a matrix for your solution:

    SELECT QIDNAME(qid) AS 'Event Name', UniqueCount("sourceIP") AS 'Source IP (Unique Count)', UniqueCount("destinationIP") AS 'Destination IP (Unique Count)', UniqueCount("destinationPort") AS 'Destination Port (Unique Count)', UniqueCount(logSourceId) AS 'Log Source (Unique Count)', UniqueCount(category) AS 'Low Level Category (Unique Count)', UniqueCount("protocolId") AS 'Protocol (Unique Count)', UniqueCount("userName") AS 'Username (Unique Count)', MAX("magnitude") AS 'Magnitude (Maximum)', SUM("eventCount") AS 'Event Count (Sum)', COUNT(*) AS 'Count' from events where qid='28250369' GROUP BY qid order by "Count" desc last 30 DAYS

    Regards,
    Ralph

    ------------------------------
    Ralph Belfiore
    IT Security Senior Consulting
    pro4bizz GmbH
    Karlsruhe
    +49 721 90981720
    ------------------------------

    Original Message


  • 4.  RE: Offenses Monthly report

    Posted Fri January 15, 2021 05:11 AM
    You can also get the offenses through the API (/siem/offenses). That's how I'm doing my reporting.

    ------------------------------
    Raphaël Langella
    SIEM Architect
    IMS Networks
    ------------------------------

    Original Message


  • 5.  RE: Offenses Monthly report

    Posted Wed February 03, 2021 10:39 PM
    Hi Raphel,

    can you please let me know how get Offense report via API?It would be great if you can provide any docuemnt.

    ------------------------------
    Abhishek Kakkireni
    ------------------------------

    Original Message


  • 6.  RE: Offenses Monthly report

    Posted Wed February 03, 2021 10:35 PM
    Hi Ralph,

    The above AQL query  is not providing Exact data.

    ------------------------------
    Abhishek Kakkireni
    ------------------------------

    Original Message


  • 7.  RE: Offenses Monthly report

    Posted Mon February 08, 2021 07:57 AM
    You get the offenses using the '/siem/offenses' endpoint of the API. Basically, I'm grabbing this using Python/pandas, along with the offense types and closing reasons, then merge everything in the same dataframe. Which can then be used to generate excel or powerpoint reports.

    ------------------------------
    Raphaël Langella
    SIEM Architect
    IMS Networks
    ------------------------------

    Original Message


  • 8.  RE: Offenses Monthly report

    IBM Champion
    Posted Thu February 11, 2021 01:54 PM
    Abhishek,

    all answers given so far are helpful and correct. However if you are not familiar with AQL and API to produce your Monthly offense report, why not use the QRadar standard report template? Just adopt "Offense Source Summary" to your needs and schedule it on a monthly basis. It uses Offense searches by four different aspects,  rule name being one of them, see excerpt:
    monthly reportSource and Dest IP as well as Username are the other offense searches being included. Those searches are available from the quick search menu as well. You can switch to show AQL in edit search and select the search that best suits your requirements or combine all of them in one. Here is the AQL for the search result shown above:

    SELECT "Parent" AS 'Parent (custom)', AVG("Events per Second Coalesced - Peak 1 Sec") AS 'Events per Second Coalesced - Peak 1 Sec (custom) (Average)', AVG("Events per Second Raw - Peak 1 Sec") AS 'Events per Second Raw - Peak 1 Sec (custom) (Average)', AVG("Events per Second Coalesced - Average 1 Min") AS 'Events per Second Coalesced - Average 1 Min (custom) (Average)', AVG("Events per Second Raw - Average 1 Min") AS 'Events per Second Raw - Average 1 Min (custom) (Average)', COUNT(*) AS 'Count' from events where "deviceType"='147' AND ( icu4jsearch('Events per second', payload) != -1 AND icu4jsearch('StatFilter', payload) != -1 ) GROUP BY "Parent" order by "Count" desc last 30 DAYS

    corresponding result in search window is
    offense search

    Hope this helps
    Karl


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

    Original Message


  • 9.  RE: Offenses Monthly report

    Posted Fri February 12, 2021 07:16 AM
    HI Abhishek
    you can use below for reporting offense data:
    https://www.ibm.com/support/pages/qradar-creating-search-report-show-offense-data

    ------------------------------
    Rashid Iqbal
    ------------------------------

    Original Message


  • 10.  RE: Offenses Monthly report

    Posted Fri February 19, 2021 07:37 AM
    Here is a python library I wrote which contains some methods for extracting offenses.

    ------------------------------
    Raphaël Langella
    SIEM Architect
    IMS Networks
    ------------------------------

    Original Message