Expanding action_evtlog_data_fields

[NathanBradley]

Is it possible to create an arrayexpand the action_evtlog_data_fields

the below fails to run

dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG
| arrayexpand action_evtlog_data_fields
| alter Username=json_extract(action_evtlog_data_fields, "$.TargetUserName")
| alter IP_Address=json_extract(action_evtlog_data_fields, "$.IpAddress")
| fields IP_Address

[afurze · Accepted solution]

The field you are attempting to expand is not an array, it is a json field, so that won't work.  If you remove this line from your query like so:

dataset = xdr_data
| filter event_type = ENUM.EVENT_LOG
| alter Username=json_extract(action_evtlog_data_fields, "$.TargetUserName")
| alter IP_Address=json_extract(action_evtlog_data_fields, "$.IpAddress")
| fields IP_Address, Username

You should get values as you expect!

[NathanBradley]

Thanks, any documentation out there for items that are either arrays or json fields

[afurze]

There is no exhaustive list to my knowledge, however, you can easily tell by looking at the field data.  Well formatted JSON data is always surrounded by curly brackets ('{', '}'), and then uses key-value format, whereas arrays use square brackets ('[', ']') and are simple collections of values.  For example:

{

    "store_name": "Sears",

    "store_tags": ["home", "automotive", "defunct"]

}

The overall object here is a JSON object, and it contains two keys, one of which has a value which is an array.  If you see a comma separated list of values in square brackets, think array, if you see key-value data inside of curly braces, think JSON!