Internal fields have multiple values in Parsers

%3CLINGO-SUB%20id%3D%22lingo-sub-2668258%22%20slang%3D%22en-US%22%3EInternal%20fields%20have%20multiple%20values%20in%20Parsers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2668258%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20working%20on%20creating%20a%20custom%20connector%20to%20ingest%20the%20data%20in%20Azure%20Sentinel.%20We%20are%20now%20working%20on%20parsers%2C%20and%20we%20are%20trying%20to%20replicate%20the%20data%20fields%20as%20in%20Splunk.%20In%20Splunk%2C%20the%20inner%20fields%20of%20a%20List%20of%20Dictionaries%20can%20be%20individually%20represented.%20say%20for%20eg.%20a%20is%20a%20list%20of%20dictionaries%3C%2FP%3E%3CP%3Ea%20%3D%20%5B%3C%2FP%3E%3CP%3E%7Bb%3A%20null%2C%20c%3A%20%22string1%22%7D%2C%3C%2FP%3E%3CP%3E%7Bb%3A%20%22string2%22%2C%20c%3A%20%22string3%22%7D%3C%2FP%3E%3CP%3E%5D%3C%2FP%3E%3CP%3EIn%20Splunk%20we%20can%20represent%20a%7B%7D.b%20%3D%20null%2C%20a%7B%7D.b%3D%22string2%22%3C%2FP%3E%3CP%3EBut%20we%20are%20trying%20to%20do%20the%20same%20in%20Azure%2C%20and%20we%20actually%20are%20not%20able%20to%20do%20it%2C%20we%20can%20give%20column%20a%7B%7D.b%20only%20one%20value%2C%20from%20the%20list%20of%20values%2C%20say%20a%7B%7D.b%3Dnull%20or%20string2%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20someone%20please%20help%20me%20determine%20how%20to%20access%20inner%20of%20fields%20of%20a%20multi%20valued%20fields%2C%20or%20how%20to%20represent%20the%20multiple%20values%20under%20one%20name%20field%2C%20say%20a%7B%7D.b%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2670165%22%20slang%3D%22en-US%22%3ERe%3A%20Internal%20fields%20have%20multiple%20values%20in%20Parsers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1106811%22%20target%3D%22_blank%22%3E%40Ronak_Shah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20write%20parser%20similar%20to%20this%2C%26nbsp%3B%3C%2FP%3E%3CP%3Ee.g.%3C%2FP%3E%3CP%3ESigninLogs%20%7BTable%20Name%7D%3CBR%20%2F%3EAuthenticationDetails%20%7BField%20Name%7D%3CBR%20%2F%3E%5B0%3AauthenticationMethod%3CBR%20%2F%3E0%3A%20authenticationStepRequirement%5D%3CBR%20%2F%3E%5B1%3A%20authenticationStepRequirement%5D%3CBR%20%2F%3E%5B2%3A%20authenticationMethod%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EParser%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESigninLogs%0A%7C%20extend%20%20AuthenticationMethod%20%3D%20tostring(parse_json(AuthenticationDetails)%5B0%5D.authenticationMethod)%0A%7C%20extend%20authenticationStepRequirement%20%3D%20tostring(parse_json(AuthenticationDetails)%5B0%5D.authenticationStepRequirement)%0A%7C%20extend%20authenticationStepRequirement1%20%3D%20tostring%20(parse_json(AuthenticationDetails)%5B1%5D.authenticationStepRequirement)%0A%7C%20extend%20authenticationMethod%20%3D%20tostring(parse_json(AuthenticationDetails)%5B2%5D.authenticationMethod)%0A%7C%20project%20AuthenticationMethod%2C%20authenticationStepRequirement%2CauthenticationStepRequirement1%2CauthenticationMethod%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20you%20can%20share%20sample%20logs%20for%20better%20help.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2677793%22%20slang%3D%22en-US%22%3ERe%3A%20Internal%20fields%20have%20multiple%20values%20in%20Parsers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2677793%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407706%22%20target%3D%22_blank%22%3E%40deshantshukla%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20Thanks%20for%20your%20response%3C%2FP%3E%3CP%3EActually%20in%20the%20above%20method%20suggested%2C%20it%20will%20be%20only%20possible%20if%20we%20know%20the%20length%20of%20the%20list%2C%20or%20we%20know%20how%20many%20dictionary%20items%20are%20present%20in%20the%20list.%3CBR%20%2F%3EIf%20in%20the%20example%20I%20provided%3C%2FP%3E%3CP%3Ea%20%3D%20%5B%3C%2FP%3E%3CP%3E%7Bb%3A%20null%2C%20c%3A%20%22string1%22%7D%2C%3C%2FP%3E%3CP%3E%7Bb%3A%20%22string2%22%2C%20c%3A%20%22string3%22%7D%3C%2FP%3E%3CP%3E.....%3CBR%20%2F%3E%7Bb%3A%20%22string3%22%2C%20c%3A%20%22null%22%7D%5D%3CBR%20%2F%3Ewe%20don't%20know%20the%20length%2C%20then%20how%20should%20I%20implement%20the%20logic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2682976%22%20slang%3D%22en-US%22%3ERe%3A%20Internal%20fields%20have%20multiple%20values%20in%20Parsers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2682976%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1106811%22%20target%3D%22_blank%22%3E%40Ronak_Shah%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20code%2C%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESigninLogs%0A%7C%20project%20DeviceDetail%0A%7C%20evaluate%20bag_unpack(DeviceDetail)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebag_unpack%20will%20Parse%20all%20the%20values%20inside%20it%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20this%20Blog%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.cloudsma.com%2F2020%2F01%2Fextracting-nested-fields-kusto%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.cloudsma.com%2F2020%2F01%2Fextracting-nested-fields-kusto%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

We are working on creating a custom connector to ingest the data in Azure Sentinel. We are now working on parsers, and we are trying to replicate the data fields as in Splunk. In Splunk, the inner fields of a List of Dictionaries can be individually represented. say for eg. a is a list of dictionaries

a = [

{b: null, c: "string1"},

{b: "string2", c: "string3"}

]

In Splunk we can represent a{}.b = null, a{}.b="string2"

But we are trying to do the same in Azure, and we actually are not able to do it, we can give column a{}.b only one value, from the list of values, say a{}.b=null or string2

Can someone please help me determine how to access inner of fields of a multi valued fields, or how to represent the multiple values under one name field, say a{}.b?

3 Replies

@Ronak_Shah 

 

You can write parser similar to this, 

e.g.

SigninLogs {Table Name}
AuthenticationDetails {Field Name}
[0:authenticationMethod
0: authenticationStepRequirement]
[1: authenticationStepRequirement]
[2: authenticationMethod]

 

Parser:

SigninLogs
| extend  AuthenticationMethod = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)
| extend authenticationStepRequirement = tostring(parse_json(AuthenticationDetails)[0].authenticationStepRequirement)
| extend authenticationStepRequirement1 = tostring (parse_json(AuthenticationDetails)[1].authenticationStepRequirement)
| extend authenticationMethod = tostring(parse_json(AuthenticationDetails)[2].authenticationMethod)
| project AuthenticationMethod, authenticationStepRequirement,authenticationStepRequirement1,authenticationMethod

 

Or you can share sample logs for better help.  

@deshantshukla 

 

Hi, Thanks for your response

Actually in the above method suggested, it will be only possible if we know the length of the list, or we know how many dictionary items are present in the list.
If in the example I provided

a = [

{b: null, c: "string1"},

{b: "string2", c: "string3"}

.....
{b: "string3", c: "null"}]
we don't know the length, then how should I implement the logic.

Hi @Ronak_Shah,

 

Try this code,

SigninLogs
| project DeviceDetail
| evaluate bag_unpack(DeviceDetail)

 

 

bag_unpack will Parse all the values inside it .

 

Check this Blog: https://www.cloudsma.com/2020/01/extracting-nested-fields-kusto/