KQL Syntax Parsing dynamic list of json tuples

%3CLINGO-SUB%20id%3D%22lingo-sub-1075299%22%20slang%3D%22en-US%22%3EKQL%20Syntax%20Parsing%20dynamic%20list%20of%20json%20tuples%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1075299%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20guidance%20on%20how%20I%20would%20go%20about%20parsing%20a%20dynamic%20list%20of%20tuples%20within%20a%20single%20event%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20example%20I%20have%20attached%20is%20a%20AWS%20CloudTrail%20log%20event%20with%20a%20dynamic%20list%20of%20Security%20Group%20policies.%20I%20need%26nbsp%3Bto%20parse%20the%20toPort%2C%20fromPort%2C%20and%20cidr%20from%20each%20entry.%20E%3CSPAN%3Each%20log%20event%20will%20have%20a%20dynamic%20number%20of%20policies.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20goal%20was%20to%20break%20these%20tuplets%20out%20into%20their%20own%20rows%20and%20a%20join%20(inner)%20to%20the%20parent%20log.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1075866%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Syntax%20Parsing%20dynamic%20list%20of%20json%20tuples%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1075866%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EHave%20you%20checked%20out%20MVExpand%3F%3CBR%20%2F%3EThat%20might%20be%20what%20you%20are%20looking%20for%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fmvexpandoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fmvexpandoperator%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1076639%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Syntax%20Parsing%20dynamic%20list%20of%20json%20tuples%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F186539%22%20target%3D%22_blank%22%3E%40Thijs%20Lecomte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked!%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAWSCloudTrail%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Policies%20%3D%20parse_json%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ERequestParameters%3C%2FSPAN%3E%3CSPAN%3E).%3C%2FSPAN%3E%3CSPAN%3EipPermissions%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3Eitems%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Emvexpand%3C%2FSPAN%3E%3CSPAN%3E%20Policies%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

Any guidance on how I would go about parsing a dynamic list of tuples within a single event?

 

The example I have attached is a AWS CloudTrail log event with a dynamic list of Security Group policies. I need to parse the toPort, fromPort, and cidr from each entry. Each log event will have a dynamic number of policies.

 

The goal was to break these tuplets out into their own rows and a join (inner) to the parent log.

 

Thank you in advance.

2 Replies
Hi

Have you checked out MVExpand?
That might be what you are looking for: https://docs.microsoft.com/en-us/azure/kusto/query/mvexpandoperator

@Thijs Lecomte 

 

That worked! Thank you!

 

AWSCloudTrail

| extend Policies = parse_json(RequestParameters).ipPermissions.items
| mvexpand Policies