One of the great features with Azure Sentinel is that you can ingest any type of data and take care of parsing it later on at query time. Specifically, events originating from cloud sources often include JSON compound elements that provide wealthy information about the event. This is a far cry from traditional SIEM systems that support a rigid event format and, in many cases, require reducing events to fit in the schema. Another common source of JSON data in Azure Sentinel would be enrichment data collected using playbooks as demonstrated by Tiander Turpin here.
This brings us to the question of how to write a query to use JSON fields. Sentinel’s query language, KQL, uses the parse_json function to provide access to JSON field elements. However, when the JSON structure becomes deeper, using this function can become cumbersome. Luckily, Sentinel provides new tools to help you write those queries.
Let’s use Microsoft Cloud App Security (MCAS) alerts as an example. Using the following query, we get MCAS alerts on impossible travel:
SecurityAlert
| where ProviderName == "MCAS"
| where DisplayName == "Impossible travel activity"
| limit 10
Expanding one of the events, we can see that the countries Alex visited are located in the Extended Properties JSON compound field:
To use the countries just visited value, we can use the ellipsis on the left side of it automatically add a JSON parsing function to our query. Selecting the include option updates the query automatically to the one below:
SecurityAlert
| where ProviderName == "MCAS"
| where DisplayName == "Impossible travel activity"
| limit 10
| where parse_json(ExtendedProperties).Countries == "AU, DE, FR, GB, JP, US"
However, since the Counties field is a list, include and exclude might not be particularly useful as they add a condition that would require the entire list of counties to be identical. You can change the “==” or “!=” operators automatically generated to a contains or !contains operators:
SecurityAlert
| where ProviderName == "MCAS"
| where DisplayName == "Impossible travel activity"
| limit 10
| where parse_json(ExtendedProperties).Countries contains "AU"
A better option would be to use the extend column option, which allows to filter further and process the new field and also ensures it is presented as a field in the results. Using extend column instead of include, the query is automatically updated as follows:
SecurityAlert
| where ProviderName == "MCAS"
| where DisplayName == "Impossible travel activity"
| limit 10
| extend Countries_ = tostring(parse_json(ExtendedProperties).Countries)
Will result in the following output, adding a Countries_ field to the result set for easy viewing:
