Forum Discussion

BlatniBPMCP's avatar
BlatniBPMCP
Copper Contributor
Sep 10, 2020

Search-UnifiedAuditLog ConvertFrom-Json AuditData nested data

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

Auditing
exchange
office 365
security
  • Robert Bollinger's avatar
    Robert Bollinger
    Iron Contributor
    Sep 24, 2020

    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 

Resources