Mehmet Ergene

Microsoft Sentinel Internals: Hidden Gems in the SecurityAlert Table

Have you ever wondered how Microsoft Sentinel generates alerts and stores them in the SecurityAlert table? Recently, while working on a custom Workbook, I explored this table in depth to see what additional insights it might hold. What I found was more than just metadata about alerts - there are some lesser-known features that can significantly enhance investigations and automation. In this blog, I'll share my findings, particularly focusing on well-known Entities and the lesser-known ExtendedProperties columns.

Entities Column

When Microsoft Sentinel generates an alert, it includes a well-known column called Entities. This column contains information about the key objects involved in the alert, such as IP addresses, users, or devices. The Entities field is both populated by Sentinel analytic rules and other Microsoft Security products. It is useful for performing analysis on top of alerts.

ExtendedProperties: The Hidden Goldmine

While the Entities column is commonly used, the ExtendedProperties column contains additional information that might be often overlooked. There are two keys stand out in this JSON string field:

OriginalQuery

The OriginalQuery key within the ExtendedProperties column stores the exact KQL query that was run. It's useful for investigating changes to the query logic over time or troubleshooting analytic rule behavior.

Query

This key contains a cryptic piece of code that might not seem immediately useful at first glance. However, upon closer inspection, I found that it holds a unique treasure trove of data.

Unlocking the Raw Event

Curious about what the Query key contained, I copied its value and executed it. To my surprise, it revealed the raw event that caused the alert to be generated.

Why Is This Important?

The raw event provides the exact context of what triggered the alert. While the Entities column is great for summarizing impacted objects, the raw event can include additional details that might not be explicitly mapped to entities.

Although manually copying and pasting the Query key's value to view raw events is far from efficient, with a simple KQL trick, you can extend it in the query and include the raw event in the results. 
Here's how to do it:
SecurityAlert
// | where DisplayName == "Logon with NewCredentials" // Filter certain alerts or analyze all
| extend _alertedEvent = parse_json(ExtendedProperties).Query
| parse _alertedEvent with * "['" compressedRec "']" *
| extend OriginalEvent = todynamic(zlib_decompress_from_base64_string(compressedRec))
| project AlertGenerated = TimeGenerated, AlertName, Entities, OriginalEvent
| evaluate bag_unpack(OriginalEvent)
Empty space, drag to resize
The result? A clean and enriched alert view with raw event data seamlessly integrated.

Limitations

While this having the raw event inside a key is powerful, there are a few limitations to keep in mind:
  • Scope: This method applies only to alerts generated by Analytics Rules in Microsoft Sentinel. Alerts ingested from other Microsoft Security products (e.g., Defender for Endpoint or Defender for Office 365) do not include this information.
  • Size Limit: The Query key has a data limit of 10 KB (10,240 bytes). If your analytic rule query results exceed this size due to unnecessary data or verbose results, the raw event gets dropped, and you'll see a warning indicating the data size limit.

Conclusion

The Query key in the ExtendedProperties column holds an often-overlooked gem: the raw event that triggered the alert. By parsing this data, you can enhance your investigations, build richer Workbooks, and enable smarter automations.

While this feature is quite useful, it's important to manage analytic rule queries efficiently to stay within the size limit and avoid losing critical data. With proper usage, the raw event hidden inside the Query can become a valuable tool in your Microsoft Sentinel arsenal.
Share