Search-UnifiedAuditLog ConvertFrom-Json AuditData nested data

%3CLINGO-SUB%20id%3D%22lingo-sub-1652047%22%20slang%3D%22en-US%22%3ESearch-UnifiedAuditLog%20ConvertFrom-Json%20AuditData%20nested%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1652047%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%3C%2FP%3E%3CP%3EI%E2%80%99m%20searching%20O365%20UnifiedAuditLog%20fro%20specific%26nbsp%3B%20event.%20Problem%20is%20hat%20there%20is%20nested%20object%20and%20when%20doing%20conversion%20from%20Jason%20not%20all%20data%20is%20parsed.%3C%2FP%3E%3CP%3EAuditData%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%20%7B%22CreationTime%22%3A%222020-09-07T11%3A34%3A11%22%2C%22Id%22%3A%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%22%2C%22Operation%22%3A%22FolderBind%22%2C%22OrganizationId%22%3A%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx%22%2C%22RecordType%22%3A2%2C%22ResultStatus%22%3A%22Succeeded%22%2C%22UserKey%22%3A%221003200047779776%22%2C%22UserType%22%3A0%2C%22Version%22%3A1%2C%22Workload%22%3A%22Exchange%22%2C%22ClientIP%22%3A%222603%3Axxxx%3Axxxx%3Axx%3Axxxx%3A%3A81%22%2C%22UserId%22%3A%22upn%40doamin.com%22%2C%22AppId%22%3A%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx%22%2C%22ClientAppId%22%3A%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx%22%2C%22ClientIPAddress%22%3A%222603%3Axxxx%3Axxxx%3Axx%3Axxxx%3A%3A81%22%2C%22ClientInfoString%22%3A%22Client%3DREST%3BClient%3DRESTSystem%3B%3B%22%2C%22ExternalAccess%22%3Afalse%2C%22InternalLogonType%22%3A2%2C%22LogonType%22%3A2%2C%22LogonUserSid%22%3A%22S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxx%22%2C%22MailboxGuid%22%3A%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%22%2C%22MailboxOwnerSid%22%3A%22S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxx%22%2C%22MailboxOwnerUPN%22%3A%22upn%40doamin.com%22%2C%22Organizat%20%22%3A%22domain.onmicrosoft.com%22%2C%22OriginatingServer%22%3A%22VI1P195MBXXXX%20(15.20.3348.019)%5Cu000d%5Cu000a%22%2C%22Item%22%3A%7B%22Id%22%3A%22YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY%22%2C%22ParentFolder%22%3A%7B%22Id%22%3A%22YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY%22%2C%22Path%22%3A%22%5C%5CSend%22%7D%7D%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%20%26nbsp%3Bstart%20with%20Item%22%3A%7B%22.%3C%2FP%3E%3CP%3EData%20that%20is%20returned%20Item%20%3A%20%40%7BId%3DYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY%3B%20ParentFolder%3D%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20easy%20solution%20for%20this.%20%26nbsp%3BI%20would%20like%20to%20pars%20output%20to%20CSV%3C%2FP%3E%3CP%3EBr%2C%3C%2FP%3E%3CP%3EStane%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1652047%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAudit%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExchange%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1708312%22%20slang%3D%22en-US%22%3ERe%3A%20Search-UnifiedAuditLog%20ConvertFrom-Json%20AuditData%20nested%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1708312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75719%22%20target%3D%22_blank%22%3E%40BlatniBPMCP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20if%20this%20will%20work%20or%20not%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETo%20Generate%20the%20Data%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%24ConvertAudit%20%3D%20Search-UnifiedAuditLog%26nbsp%3B%20-StartDate%20%2204-01-2020%22%20-EndDate%20%2204-10-2020%22%20-UserIds%20%22user%40domain.com%22%20-ResultSize%205000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETo%20produce%20the%20report%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%24ConvertAudit%20%7C%20Select-Object%20-ExpandProperty%20AuditData%20%7C%20ConvertFrom-Json%20%7C%20Select-Object%20CreationTime%2CUserId%2COperation%2CWorkload%2CObjectID%2CSiteUrl%2CSourceFileName%2CClientIP%2CUserAgent%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20above%20is%20what%20I%20use%20to%20convert%20the%20JSON%20data%20to%20something%20that%20readable.%20Now%20I%20have%20yet%20to%20figure%20out%20how%20to%20expand%20multiple%20nested%20values%20within%20the%20same%20JSON%20Data%20Structure.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20you%20might%20be%20able%20to%20use%20Excel%20and%20Select%20%22PowerQuery%22%20when%20importing%20data%2C%20using%20a%20transform.%20However%20its%20not%20easy%20to%20do%20and%20takes%20some%20effort%2C%20and%20doesnt%20always%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobert%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

HI,

I’m searching O365 UnifiedAuditLog fro specific  event. Problem is hat there is nested object and when doing conversion from Jason not all data is parsed.

AuditData    : {"CreationTime":"2020-09-07T11:34:11","Id":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx","Operation":"FolderBind","OrganizationId":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx","RecordType":2,"ResultStatus":"Succeeded","UserKey":"1003200047779776","UserType":0,"Version":1,"Workload":"Exchange","ClientIP":"2603:xxxx:xxxx:xx:xxxx::81","UserId":"upn@doamin.com","AppId":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx","ClientAppId":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx","ClientIPAddress":"2603:xxxx:xxxx:xx:xxxx::81","ClientInfoString":"Client=REST;Client=RESTSystem;;","ExternalAccess":false,"InternalLogonType":2,"LogonType":2,"LogonUserSid":"S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxx","MailboxGuid":"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx","MailboxOwnerSid":"S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxx","MailboxOwnerUPN":"upn@doamin.com","Organizat ":"domain.onmicrosoft.com","OriginatingServer":"VI1P195MBXXXX (15.20.3348.019)\u000d\u000a","Item":{"Id":"YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY","ParentFolder":{"Id":"YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY","Path":"\\Send"}}}

 

Problem  start with Item":{".

Data that is returned Item : @{Id=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY; ParentFolder=}

 

Is there any easy solution for this.  I would like to pars output to CSV

Br,

Stane

1 Reply

@BlatniBPMCP 

 

I don't know if this will work or not: 

 

To Generate the Data

$ConvertAudit = Search-UnifiedAuditLog  -StartDate "04-01-2020" -EndDate "04-10-2020" -UserIds "user@domain.com" -ResultSize 5000

 

To produce the report:

$ConvertAudit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent

 

But the above is what I use to convert the JSON data to something that readable. Now I have yet to figure out how to expand multiple nested values within the same JSON Data Structure. 

 

Also, you might be able to use Excel and Select "PowerQuery" when importing data, using a transform. However its not easy to do and takes some effort, and doesnt always work. 

 

Thanks, 

 

Robert