Need assistance in parsing fields

[rahulharidas7514]

I have a table named ReconDarknetDetectionAlerts_CL with a field "Table_s" .It is not parsed properly 

 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"]]}]

Expected Output

Email                              Breach Date                      Password Length

email address removed for privacy reasons             2023-07-21 00:00:00                    8

email address removed for privacy reasons             2023-07-21 00:00:00                    9

email address removed for privacy reasons                2023-07-21 00:00:00                9

Query Tried so far is

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]),

Challenge seen is only the first set of values is captured by above query.

Output

Email                              Breach Date                      Password Length

email address removed for privacy reasons             2023-07-21 00:00:00                    8

[#2]

Hi rahulharidas7514,

Please include sample data so that I can help you further.

Thanks

[#3]

Table Name : ReconDarknetDetectionAlerts_CL
Field which is not parsed properly is tables_s

Sample data
tables_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"]]}]

Expected Output

Email                              Breach Date                      Password Length

Email 1                          2023-07-21 00:00:00                   8

Email 2                           2023-07-21 00:00:00                  9

Email 3                           2023-07-21 00:00:00                  9

[#4]

@rahulharidas7514 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.

Thanks

[#5]

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

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])

@camc