Forum Discussion

rahulharidas7514's avatar
rahulharidas7514
Copper Contributor
Jul 25, 2023

Need assistance in parsing fields

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"...
KQL
kusto
camc's avatar
camc
Copper Contributor
Jul 25, 2023
Hi rahulharidas7514,

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

Thanks
rahulharidas7514's avatar
rahulharidas7514
Copper Contributor
Jul 25, 2023

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

  • camc's avatar
    camc
    Copper Contributor
    Jul 25, 2023

    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

    • rahulharidas7514's avatar
      rahulharidas7514
      Copper Contributor
      Jul 27, 2023

      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 

Resources