How to split up field containing CSV like data

%3CLINGO-SUB%20id%3D%22lingo-sub-2187566%22%20slang%3D%22en-US%22%3EHow%20to%20split%20up%20field%20containing%20CSV%20like%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187566%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20field%20that%20contains%20the%20folliwng%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CDIV%20class%3D%22k-grid%20k-widget%20k-display-block%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CDIV%3E%23012rt%3DMar%2004%202021%2016%3A21%3A40%3BGMT%23012deviceExternalId%3D%3B6C2331FFBFD96B1%23012src%3D%3B10.1.16.4%23012dst%3D%3B89.238.73.97%23012sourceTranslatedAddress%3D%3B10.1.1.12%23012destinationTranslatedAddress%3D%3B89.238.73.97%23012cs1Label%3D%3BRule%23012cs1%3DAllow%20Internet%20from%20TrioFox%3B%23012suser%3D%3B%23012duser%3D%3B%23012app%3D%3Bweb-browsing%23012cs3Label%3D%3BVirtualSystem%23012cs3%3D%3Bvsys1%23012cs4Label%3DSource%3BZone%23012cs4%3D%3BTrusted%23012cs5Label%3DDestination%3BZone%23012cs5%3D%3BUntrusted%23012deviceInboundInterface%3D%3Bethernet1%2F2%23012deviceOutboundInterface%3D%3Bethernet1%2F1%23012cs6Label%3D%3BLogProfile%23012cs6%3DAzure%20Sentinel%3BSyslog%23012cn1Label%3D%3BSessionID%23012cn1%3D%3B629276%23012cnt%3D%3B1%23012spt%3D%3B52853%23012dpt%3D%3B80%23012sourceTranslatedPort%3D%3B54280%23012destinationTranslatedPort%3D%3B80%23012flexString1Label%3D%3BFlags%23012flexString1%3D%3B0x80402000%23012proto%3D%3Btcp%23012act%3D%3Breset-both%23012request%3D%3B%22eicar.com.txt%22%23012cs2Label%3DURL%3BCategory%23012cs2%3D%3Bcomputer-and-internet-info%23012flexString2Label%3D%3BDirection%23012flexString2%3D%3Bserver-to-client%23012PanOSActionFlags%3D%3B0x2000000000000000%23012externalId%3D%3B47502%23012cat%3DEicar%20File%3BDetected(39040)%23012fileId%3D%3B1152921504606847038%23012PanOSDGl1%3D%3B0%23012PanOSDGl2%3D%3B0%23012PanOSDGl3%3D%3B0%23012PanOSDGl4%3D%3B0%23012PanOSVsysName%3D%3B%23012dvchost%3D%3Bhnlpfw01%23012PanOSSrcUUID%3D%3B%23012PanOSDstUUID%3D%3B%23012PanOSTunnelID%3D%3B0%23012PanOSMonitorTag%3D%3B%23012PanOSParentSessionID%3D%3B0%23012PanOSParentStartTime%3D%3B%23012PanOSTunnelType%3D%3BN%2FA%23012PanOSThreatCategory%3D%3Bcode-execution%23012PanOSContentVer%3D%3BAppThreat-8381-6569%23012PanOSAssocID%3D%3B0%23012PanOSPPID%3D4294967295%3BPanOS%23012HTTPHeader%3D%3B%23012PanOSURLCatList%3D%3B%23012PanOSRuleUUID%3D%3Bbcbecb9b-17cf-48bd-b9eb-4685d5629da0%23012PanOSHTTP2Con%3D%3B0%23012PanDynamicUsrgrp%3D%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20Can%20I%20expand%20this%20data%20and%20create%20additional%20fields%20separately.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2187830%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20split%20up%20field%20containing%20CSV%20like%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511961%22%20target%3D%22_blank%22%3E%40LA1976%3C%2FA%3E%26nbsp%3BYou%20can%20use%20the%20split()%20command%20which%20will%20convert%20it%20into%20a%20string%20array%20which%20you%20can%20then%20index%20each%20field%20individually.%26nbsp%3B%20There%20is%20a%20parse_csv()%20command%20as%20well%20but%20it%20only%20works%20on%20comma%20separated%20fields%20and%20your%20string%20is%20semi-colon%20delimited.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fsplitfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fsplitfunction%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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