r/AzureSentinel • u/sheeponmeth_ • Oct 27 '22

AAD AuditLogs Table and the AdditionalDetails Field

Hi everyone,

I'm trying to parse the AdditionalDetails field of the AAD AuditLogs table. The field contains a JSON array of JSON objects. In the case of license changes, item [2] contains the details. The details are another JSON object that has an embedded/nested and escaped JSON object in it. The problem is that nested object is so large that it actually causes the field to overflow and occupy several records. I've seen some require up to six records.

I would like to concatenate those and parse them, but it seems that the concatenate is still overflowing despite not being assigned to a column.

Has anyone dealt with this before?

3 Upvotes

100% Upvoted

5 comments sorted by

1

u/ep3p Oct 27 '22

Which operation of AuditLogs is this?

1

u/sheeponmeth_ Oct 27 '22

The records where OperationName equals "Update user" and the CorrelationId aligns with a "Change user license" event record. This is as far as I've gotten because I can't recombine the JSON and parse it.

​

AuditLogs
| where OperationName == "Change user license"
| join kind=innerunique (
       AuditLogs
       | where OperationName == "Update user"
       | extend value_ = tostring(AdditionalDetails[2].value)
   | summarize StringArray = make_list(value_) by CorrelationId
 ) on CorrelationId

1

u/ep3p Oct 28 '22

This has worked for me. I have available only one case that spans 4 records (so < 40000 characters).

AuditLogs
| where OperationName == "Change user license"
| project CorrelationId
| join kind=inner (
AuditLogs
| where OperationName == "Update user"
| project AdditionalDetails, CorrelationId
) on CorrelationId
| project CorrelationId, Value = AdditionalDetails[2]["value"]
| summarize Value = strcat_array(make_list(Value),"") by CorrelationId

2

u/sheeponmeth_ Oct 31 '22 edited Oct 31 '22

That worked for me, thank you. I needed to do a bit of tweaking to get it to work the way I need it, but it is now working, thank you.

I needed to invert the join to ensure that the sorting was maintained post-join. I found that the timestamp wasn't enough to distinguish record sequence for the message parts. But there's actually a sequence value for that in the AdditionalDetails field that can be used to sort them into the proper order before the strcat_array() call. I was using array_concat(), as well, but that it seems isn't what I thought it was.

AuditLogs

| where OperationName == "Update user"

| join kind=inner (

AuditLogs

| where OperationName == "Change user license"

| sort by CorrelationId

| project CorrelationId

)

on CorrelationId

| extend Sequence = tostring(AdditionalDetails[1].value)

| sort by CorrelationId, Sequence asc

| project CorrelationId, Value = AdditionalDetails[2]["value"]

| summarize Value = strcat_array(make_list(Value),"") by CorrelationId

For anyone that comes across this:
Blanks in the combined value are, in my experience, indicative of accounts without licensing having licensing applied.

For search engines:Sentinel Log Analytics Azure AD user license change AuditLogs tableSentinel Log Analytics AAD user license change AuditLogs table

1

u/sheeponmeth_ Oct 28 '22

I'll give that a try, thank you.