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