Forum Discussion
How to split up field containing CSV like data
I have a field that contains the folliwng data
How Can I expand this data and create additional fields separately.
Thanks.
|
2 Replies
Copy and paste below into query and you will get an idea.
let test = print('#012rt=Mar 04 2021 16:21:40;GMT#012deviceExternalId=;6C2331FFBFD96B1#012src=;10.1.16.4#012dst=;89.238.73.97#012sourceTranslatedAddress=;10.1.1.12#012destinationTranslatedAddress=;89.238.73.97#012cs1Label=;Rule#012cs1=Allow Internet from TrioFox;#012suser=;#012duser=;#012app=;web-browsing#012cs3Label=;VirtualSystem#012cs3=;vsys1#012cs4Label=Source;Zone#012cs4=;Trusted#012cs5Label=Destination;Zone#012cs5=;Untrusted#012deviceInboundInterface=;ethernet1/2#012deviceOutboundInterface=;ethernet1/1#012cs6Label=;LogProfile#012cs6=Azure Sentinel;Syslog#012cn1Label=;SessionID#012cn1=;629276#012cnt=;1#012spt=;52853#012dpt=;80#012sourceTranslatedPort=;54280#012destinationTranslatedPort=;80#012flexString1Label=;Flags#012flexString1=;0x80402000#012proto=;tcp#012act=;reset-both#012request=;"eicar.com.txt"#012cs2Label=URL;Category#012cs2=;computer-and-internet-info#012flexString2Label=;Direction#012flexString2=;server-to-client#012PanOSActionFlags=;0x2000000000000000#012externalId=;47502#012cat=Eicar File;Detected(39040)#012fileId=;1152921504606847038#012PanOSDGl1=;0#012PanOSDGl2=;0#012PanOSDGl3=;0#012PanOSDGl4=;0#012PanOSVsysName=;#012dvchost=;hnlpfw01#012PanOSSrcUUID=;#012PanOSDstUUID=;#012PanOSTunnelID=;0#012PanOSMonitorTag=;#012PanOSParentSessionID=;0#012PanOSParentStartTime=;#012PanOSTunnelType=;N/A#012PanOSThreatCategory=;code-execution#012PanOSContentVer=;AppThreat-8381-6569#012PanOSAssocID=;0#012PanOSPPID=4294967295;PanOS#012HTTPHeader=;#012PanOSURLCatList=;#012PanOSRuleUUID=;bcbecb9b-17cf-48bd-b9eb-4685d5629da0#012PanOSHTTP2Con=;0#012PanDynamicUsrgrp='); test // replace the weird #012 that is showing up everywhere and make it empty | project data = replace(@'#012', @' ', print_0) // now lets pull some data and stick it into columms | parse kind=regex data with * "src=;" src "dst=;" dst "sourceTranslatedAddress" | project src, dstLA1976 You can use the split() command which will convert it into a string array which you can then index each field individually. There is a parse_csv() command as well but it only works on comma separated fields and your string is semi-colon delimited.
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/splitfunction
