Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
SOLVED

String to Column KQL

Copper Contributor

I want to extend DetectionMethods which is string data type in emailevents table. But this may apply to other tables and situations,

 

 

EmailEvents
| take 1000
| extend kqlt=parse_json(DetectionMethods) 
| extend DM_Phish=kqlt.Phish, DM_Spam=kqlt.Spam

 

 

 

Above results in adding this, 

Rajtoor_0-1674059475136.png

 

Values are still displayed as ["Value"] and not Value

 

Is there a better way to do this. How can I bring the value out of [""]

--------------------

If the string had more keys , is there a way to dynamically create columns. Seems like bag_unpack does it but I cannot use those in query such as filtering with where.

 

 

 

{"Phish":["Spoof external domain"],"Spam":["Mixed analysis detection"]}

 

 

 

 

1 Reply
best response confirmed by Rajtoor (Copper Contributor)
Solution
Have you tried to use "mv-expand" on the columns?
1 best response

Accepted Solutions
best response confirmed by Rajtoor (Copper Contributor)
Solution
Have you tried to use "mv-expand" on the columns?

View solution in original post