Inbuilt function to extract nested JSON in AuditData field in M365 UAL logs

[blueteam0ps]

This is a feature request

• UAL CSV log export generated from M365 portal contains a column named "AuditData". It contains nested JSON and has multiple dicts. This column contains all the valuable data for investigations. It would aid analysts to have data within this column expanded so that it can easily be manipulated to answer questions during investigations.
• In a scenario where API connectivity is not possible, then the log exports are used for carrying out analysis
• Proposing a feature request so that msticpy's existing QueryProvider LocalData can be further improved to resolve this.

Uploaded a sample export from M365 UAL (https://github.com/invictus-ir/o365_dataset)
auditrecords.csv

[ianhelle]

We do have some functions like this in msticp.init.pivot_core.pivot_pd_accessor.py. (see the parse_json function)
Sounds like we could extend this kind of thing (or move the accessor functions to something more generic that pivots) to do this elementary dict-in-a-dataframe extractor.
Something like:
df.mp.dict_to_columns(column="AuditData")

I realize that the AuditData can be a mix of lists and columns so it might need to do a combo of column expansion and explosion of rows but it seems doable.

[blueteam0ps]

We do have some functions like this in msticp.init.pivot_core.pivot_pd_accessor.py. (see the parse_json function) Sounds like we could extend this kind of thing (or move the accessor functions to something more generic that pivots) to do this elementary dict-in-a-dataframe extractor. Something like: df.mp.dict_to_columns(column="AuditData")

I realize that the AuditData can be a mix of lists and columns so it might need to do a combo of column expansion and explosion of rows but it seems doable.

Sounds great!

[pioneerHitesh]

@ianhelle I am interested in contributing to this, can this be assigned to me?

[ianhelle]

Hey @pioneerHitesh,
This is a long time ago but if you're still interested in this, it would be very cool to have.

[pioneerHitesh]

@ianhelle i am interested. Please do assign this to me

[pioneerHitesh]

@ianhelle i would like to give this a try, can this be assigned to me.