Need assistance in parsing fields

Question

I have a table named&nbsp;ReconDarknetDetectionAlerts_CL with a field "Table_s" .It is not parsed properly&nbsp;&nbsp; Table_s  =  [{"headers":["Email","Hash Type","Password Length","Breach Name","Breach Date"],"table_description":"Leaked Credentials Info","values":[["email address removed for privacy reasons","plain",8,"leaked-credentials","2023-07-21 00:00:00"],["email address removed for privacy reasons","plain",9,"leaked-credentials","2023-07-21 00:00:00"]]}]&nbsp;&nbsp;Expected OutputEmail &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Breach Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Password Lengthemail address removed for privacy reasons&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2023-07-21 00:00:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8email address removed for privacy reasons&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2023-07-21 00:00:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9email address removed for privacy reasons&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2023-07-21 00:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp;Query Tried so far is&nbsp;ReconDarknetDetectionAlerts_CL

| mv-expand data = parse_json(tables_s)

| project Email = tostring(data.values[0][0]),

HashType = tostring(data.values[0][1]),

PasswordLength = toint(data.values[0][2]),

BreachName = tostring(data.values[0][3]),
&nbsp;Challenge seen is only the first set of values is captured by above query.OutputEmail &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Breach Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Password Lengthemail address removed for privacy reasons&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-07-21 00:00:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8

camc · Answer

Hi rahulharidas7514,Please include sample data so that I can help you further.Thanks

rahulharidas7514 · Answer

Table Name : ReconDarknetDetectionAlerts_CLField which is not parsed properly is tables_sSample datatables_s = [{"headers":["Email","Hash Type","Password Length","Breach Name","Breach Date"],"table_description":"Leaked Credentials Info","values":[["Email1","plain",8,"leaked-credentials","2023-07-21 00:00:00"],["Email2","plain",9,"leaked-credentials","2023-07-21 00:00:00"],["Email3","plain",9,"leaked-credentials","2023-07-21 00:00:00"]]}]&nbsp;&nbsp;Expected OutputEmail &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Breach Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Password LengthEmail 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2023-07-21 00:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8Email 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2023-07-21 00:00:00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9Email 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-07-21 00:00:00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9

camc · Answer

rahulharidas7514&nbsp;Assuming these are JSON arrays and not a string that looks like it should be (sometimes that issue does exist), for that we should use the parse operator, otherwise we can just use extend,datatable (tables_s: dynamic)
[
    dynamic({"headers":["Email","Hash Type","Password Length","Breach Name","Breach Date"],"table_description":"Leaked Credentials Info","values":[["Email1","plain",8,"leaked-credentials","2023-07-21 00:00:00"],["Email2","plain",9,"leaked-credentials","2023-07-21 00:00:00"],["Email3","plain",9,"leaked-credentials","2023-07-21 00:00:00"]]})
]
| extend impactedUser = tostring(parse_json(tostring(tables_s.values))[0][0]), 
    hashType = tostring(parse_json(tostring(tables_s.values))[0][1]),
    passwordLength = tostring(parse_json(tostring(tables_s.values))[0][2]),
    breachName = tostring(parse_json(tostring(tables_s.values))[0][3]),
    breachTime = todatetime(parse_json(tostring(tables_s.values))[0][4])Have a go with this and let me know if you get the expected results.&nbsp;Thanks

rahulharidas7514 · Answer

i did try the below query , but it was giving me just one value from the set values that is information about email is only getting captured . i want the parsing to capture all the values inside the unparsed field tables_s&nbsp;ReconDarknetDetectionAlerts_CL
| extend Email = tostring(parse_json(tostring(parse_json(tables_s)[0].values))[0][0])
| extend HashType = tostring(parse_json(tostring(parse_json(tables_s)[0].values))[0][1])
| extend PasswordLength = tostring(parse_json(tostring(parse_json(tables_s)[0].values))[0][2])
| extend BreachName = tostring(parse_json(tostring(parse_json(tables_s)[0].values))[0][3])
| extend BreachDate = tostring(parse_json(tostring(parse_json(tables_s)[0].values))[0][4])&nbsp;camc&nbsp;

Forum Discussion

Need assistance in parsing fields

Share

Resources