QRadar: How to generate a list of rules that contributed to an offense in a specific time frame

Resolving The Problem

1. Log in to the QRadar Console.
2. Click the Admin tab.
3. Click the Custom Event Property button.
4. Click the Add button.
5. For the Property Type Selection, keep it as Extraction Based
6. Enable "Enable for use in Rules, Forwarding Profiles and Search Indexing"
7. Set the Log Source Type to SIM Audit
8. Search for the Event Name "Offense Created", with a QID of 28250369.
9. Add in the following for Regex:
   ruleId="([0-9]*)"\s
10. Add in the following payload as the Test Field:
    Feb 9 05:27:29 127.0.0.1 [parent=[hostname]] | [Action] [Offense] [OffenseCreated] OffenseProperties ( id="111", type="BY_REG_LOGSOURCE_ANOMALYTRIGGER", direction="OffenseDirectionType: Remote to Remote", createdDate="1675938449333", ruleId="121196" )
11. Click the Test button to verify that it captures the values after ruleId.
12. Click the Save button.
    
    Result
    You created the "ruleId" CEP with the following configurations:
    

1. Click the Log Activity tab.
2. Change the filter to Advanced Search.
3. Modify this AQL query to the time frame as needed. In this example, it is searching for the last 2 days:
   SELECT rulename("ruleId") AS 'Custom Rule', QIDNAME(qid) as 'Event Name',LONG(COUNT()) as "Count" FROM events WHERE qid = 28250369 GROUP BY "Custom Rule" order by "Count" desc LAST 2 DAYS
4. Click the Search button.
   
   Result
   You conduct a search that lists the Rules that generated an offense in the last 2 days:
   

1. SSH to the Console
2. Run the following command, modify the time frame in the interval 'x' section as needed. More details on how that syntax works can be found in this documentation - Date/Time Functions and Operators :
   psql -U qradar -c "select rule_name,  count(id) from offense_cre_agg where to_timestamp(start_time/1000) > now() - interval 'x' group by rule_name order by 1 desc;"
   
   Results
   You generated a list of Rules that made an offense in the last 2 days through the backend:
   # psql -U qradar -c "select rule_name, count(id) from offense_cre_agg where to_timestamp(start_time/1000) > now() - interval '2 days' group by rule_name order by 1 desc;"
     rule_name     | count
-------------------+-------
 offense test rule |     8
(1 row)