How to split up field containing CSV like data

New Contributor

I have a field that contains the folliwng data

 

#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=

 

How Can I expand this data and create additional fields separately.

 

Thanks.

 

 

2 Replies

@LA1976 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

@LA1976 

 

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, dst