Dynamic json keys in AuditLogs

%3CLINGO-SUB%20id%3D%22lingo-sub-1255880%22%20slang%3D%22en-US%22%3EDynamic%20json%20keys%20in%20AuditLogs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1255880%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20get%20consistent%20columns%20from%20some%20AuditLogs.%20The%20problem%20is%20that%20the%20json%20key%20values%20are%20dynamic%20and%20change%20constantly%20and%20even%20the%20number%20of%20json%20keys%20can%20vary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20I%20want%20to%20extract%20is%20in%20an%20array%20called%20modifiedProperties%20which%20is%20inside%20another%20array%20called%20TargetResources.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EModifiedProperties%20always%20has%20a%20json%20key%20value%20of%201%20which%20is%20fine%20but%20the%20items%20inside%20have%20varying%20json%20key%20values.%20Here%20is%20some%20scrubbed%20sample%20output%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EmodifiedProperties%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22WID%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22Active%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22WorkerID%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22UserID%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22BusinessTitle%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22Company%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E6%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22CountryReferenceFriendly%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22CountryReferenceNumeric%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E8%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22CountryReferenceTwoLetter%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E9%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22LocalReference%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22Municipality%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E11%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22PostalCode%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E12%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22PreferredFirstName%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22PreferredLastName%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E14%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22PreferredNameData%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E15%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22StatusHireDate%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E16%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22WorkerType%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E17%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22BusinessUnit%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E18%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22OfficeSite%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E19%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22ManagementLevel%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E20%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22CostCenter%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E21%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%7B%3C%2FSPAN%3E%3CSPAN%3E%22displayName%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22FormattedAddress%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22oldValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Enull%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22newValue%22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5C%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CSPAN%3ESay%20I%20want%20to%20get%20the%20newValue%20for%20the%20attribute%20UserID.%20I%20can%20do%20this%20with%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20UserIDfield%3Dparse_json(parse_json(TargetResources)%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D.modifiedProperties%5B3%3C%2FSPAN%3E%3CSPAN%3E%5D).%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThe%20issue%20I%20encounter%20is%20that%20UserID%20is%20not%20always%20the%20third%20item%2C%20it%20could%20be%20any%20one%20of%20them%20and%20the%20number%20of%20attributes%20also%20changes%20so%20it's%20not%20in%20a%20predictable%20range.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EIs%20there%20a%20way%20I%20can%20convert%20modifiedProperties%20into%20columns%20in%20a%20consistent%20way%20that%20is%20not%20hard%20coded%3F%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1255880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259129%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20json%20keys%20in%20AuditLogs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F449184%22%20target%3D%22_blank%22%3E%40endakelly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20used%20variants%20of%20this%20query%20for%20these.%26nbsp%3B%20I%20didn't%20have%20any%20%22UserID%22%20data%20so%20used%20%22Tech%20Reads%22%20to%20test.%26nbsp%3B%20You%20can%20certainly%20massively%20improve%20this%20query%2C%20but%20it%20shows%20a%20technique.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20srch%20%3D%20%22displayName%22%3B%0Asearch%20in%20(AuditLogs)%20srch%0A%7C%20evaluate%20narrow()%0A%7C%20where%20Value%20contains%20srch%0A%7C%20where%20Column%20%3D%3D%20%22TargetResources%22%0A%7C%20parse%20Value%20with%20*%20'%5B%7B%22displayName%22%3A%22'%20displayName%20'%22%2C%22modifiedProperties%22'%20*%0A%7C%20where%20displayName%20%3D%3D%20%22Tech%20Reads%22%0A%7C%20parse%20Value%20with%20*%20'%22newValue%22%3A%22%5B%5C%5C%22'%20newValue%20'%5C%5C%22%5D%22%7D%2C'%20*%0A%7C%20where%20isnotempty(newValue)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BThanks%20Clive%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I'm trying to get consistent columns from some AuditLogs. The problem is that the json key values are dynamic and change constantly and even the number of json keys can vary.

 

The data I want to extract is in an array called modifiedProperties which is inside another array called TargetResources.

 

ModifiedProperties always has a json key value of 1 which is fine but the items inside have varying json key values. Here is some scrubbed sample output:

modifiedProperties
[
0 {"displayName":"WID","oldValue":null,"newValue":"\"\""},
1 {"displayName":"Active","oldValue":null,"newValue":"\"\""},
2 {"displayName":"WorkerID","oldValue":null,"newValue":"\"\""},
3 {"displayName":"UserID","oldValue":null,"newValue":"\"\""},
4 {"displayName":"BusinessTitle","oldValue":null,"newValue":"\"\""},
5 {"displayName":"Company","oldValue":null,"newValue":"\"\""},
6 {"displayName":"CountryReferenceFriendly","oldValue":null,"newValue":"\"\""},
7 {"displayName":"CountryReferenceNumeric","oldValue":null,"newValue":"\"\""},
8 {"displayName":"CountryReferenceTwoLetter","oldValue":null,"newValue":"\"\""},
9 {"displayName":"LocalReference","oldValue":null,"newValue":"\"\""},
10 {"displayName":"Municipality","oldValue":null,"newValue":"\"\""},
11 {"displayName":"PostalCode","oldValue":null,"newValue":"\"\""},
12 {"displayName":"PreferredFirstName","oldValue":null,"newValue":"\"\""},
13 {"displayName":"PreferredLastName","oldValue":null,"newValue":"\"\""},
14 {"displayName":"PreferredNameData","oldValue":null,"newValue":"\"\""},
15 {"displayName":"StatusHireDate","oldValue":null,"newValue":"\"\""},
16 {"displayName":"WorkerType","oldValue":null,"newValue":"\"\""},
17 {"displayName":"BusinessUnit","oldValue":null,"newValue":"\"\""},
18 {"displayName":"OfficeSite","oldValue":null,"newValue":"\"\""},
19 {"displayName":"ManagementLevel","oldValue":null,"newValue":"\"\""},
20 {"displayName":"CostCenter","oldValue":null,"newValue":"\"\""},
21 {"displayName":"FormattedAddress","oldValue":null,"newValue":"\"\""}
]
 
Say I want to get the newValue for the attribute UserID. I can do this with 
| extend UserIDfield=parse_json(parse_json(TargetResources)[0].modifiedProperties[3]).
 
The issue I encounter is that UserID is not always the third item, it could be any one of them and the number of attributes also changes so it's not in a predictable range.
 
Is there a way I can convert modifiedProperties into columns in a consistent way that is not hard coded?
1 Reply
Highlighted

@endakelly 

 

I have used variants of this query for these.  I didn't have any "UserID" data so used "Tech Reads" to test.  You can certainly massively improve this query, but it shows a technique. 

 

let srch = "displayName";
search in (AuditLogs) srch
| evaluate narrow()
| where Value contains srch
| where Column == "TargetResources"
| parse Value with * '[{"displayName":"' displayName '","modifiedProperties"' *
| where displayName == "Tech Reads"
| parse Value with * '"newValue":"[\\"' newValue '\\"]"},' *
| where isnotempty(newValue)

 Thanks Clive