IBM QRadar

IBM QRadar

Join this online topic 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.


#Security
#QRadar
#SecuringhybridcloudandAI
 View Only
Back to discussions
Expand all | Collapse all

Offenses Monthly report

  • 1.  Offenses Monthly report

    Posted 01/12/21 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 01/13/21 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





  • 3.  RE: Offenses Monthly report

    Posted 01/13/21 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
    ------------------------------



  • 4.  RE: Offenses Monthly report

    Posted 01/15/21 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 02/03/21 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 02/03/21 10:35 PM
    Hi Ralph,

    The above AQL query  is not providing Exact data.

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

    Original Message


  • 7.  RE: Offenses Monthly report

    Posted 02/08/21 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

    Posted 02/11/21 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]
    ------------------------------



  • 9.  RE: Offenses Monthly report

    Posted 02/12/21 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
    ------------------------------



  • 10.  RE: Offenses Monthly report

    Posted 02/19/21 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


Related Content