Forum Discussion
Create a report that contains Alerts and raw events
To get the raw events that triggered the alerts, you'd indeed need to run the KQL query from the "originalquery" field of each alert. However, to automate this in Sentinel, you can create a custom KQL query that retrieves the relevant raw events by joining the alerts with the event data. You can use tables like "SecurityAlert" and "SecurityEvent" or others that store the raw event data.
To automate the process, you can set up a playbook in Sentinel using Logic Apps. This playbook can trigger whenever a security alert or incident occurs, and within the playbook, you can run your query to fetch the raw events. Then, the playbook can send the results to your chosen location for archival purposes, such as Azure Blob Storage.
This method helps automate the entire process without the need for manual intervention.
Thanks luchete for the quick reply!
Do you maybe know what would the query look like to get alerts and their associated raw logs ?
You would typically write a KQL query that joins based on a common field such as the alert ID or the time range. Here's an example of what that query would be:
SecurityAlert | join kind=inner (SecurityEvent) on $left.AlertId == $right.AlertId | where TimeGenerated >= ago(1d) | project TimeGenerated, AlertName, EventData, AlertId, EventSourceNamein this case the query joins "SecurityAlert" with "SecurityEvent" on the "AlertId" field, filters events from the past day, and returns the alert name, event data, and related event details.
Of course you can always modify the query as needed based on the specific fields you want to retrieve or the time frame you're interested in your particular situation.
Regards
I tried to look into the SecurityEvent table using this query:
SecurityEvent
| where TimeGenerated > ago(1d)
| order by TimeGenerated descI got empty results, even though the SecurityAlert table contained results. Do you maybe know what is the reason behind that luchete ?
The issue might be that the "SecurityEvent" table doesn’t always have the same data as the "SecurityAlert" table. One possible reason could be that the events you're looking for aren't in the "SecurityEvent" table, especially if the alerts are coming from a different data source or aren't logged there.
Another reason could be the time range. While you're filtering for the past day, the alerts in the "SecurityAlert" table may be from a different time range or might not have corresponding events in "SecurityEvent".
You could try running the query on "SecurityEvent" without the time filter to see if any data comes up at all. If nothing shows up, it might be a good idea to check your data sources and confirm that events are being logged correctly.