Home

Parsing comma separated values

%3CLINGO-SUB%20id%3D%22lingo-sub-218426%22%20slang%3D%22en-US%22%3EParsing%20comma%20separated%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-218426%22%20slang%3D%22en-US%22%3E%3CP%3EWe're%20using%20a%20Syslog%20to%20get%20data%20to%20Log%20Analytics%20from%20some%20devices.%20This%20works%20perfectly%20and%20places%20the%20relevant%20data%20we%20need%20in%20the%20%22Syslogmessage%22%20column%20in%20%22Syslog%22.%20I'm%20trying%20to%20use%20the%26nbsp%3B%3CSTRONG%3Eparse%26nbsp%3B%3C%2FSTRONG%3Ecommand%20to%20extract%20that%20data%20into%20new%20columns%20but%20cannot%20figure%20out%20how%20to%20do%20it.%20Here%20is%20a%20sample%20of%20the%20value%20in%20that%20column%3A%3CBR%20%2F%3E%3CBR%20%2F%3E37%3A00%2C0008C101547%2CSYSTEM%2Cuserid%2C0%2C2018%2F07%2F23%2011%3A36%3A58%2C%2Cconnect-ldap-sever-failure%2CSERVER1.DOM1.DOMAIN1%2C0%2C0%2Cgeneral%2Cmedium%2C%22ldap%20cfg%20CT%20Group%20Mapping%20failed%20to%20connect%20to%20server%20SERVER1.DOM1.DOMAINNAME.net%3A389%3A%20Error%3A%20Failed%20to%20get%20address%20info%20for%20SERVER1.DOM1.DOMAINNAME.net.%22%2C38678840%2C0x8000000000000000%2C0%2C0%2C0%2C0%2C%2CDOM1FHS01.LAB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20figured%20I%20could%20use%20regex%20to%20extract%20what%20I%20need%2C%20but%20cannot%20seem%20to%20get%20it%20to%20work%20correctly.%20What's%20the%20best%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20of%20something%20I've%20tried%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ESyslog%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3Ekind%3D%3C%2FSPAN%3E%3CSPAN%3Eregex%3C%2FSPAN%3E%3CSPAN%3ESyslogMessage%20with%20foo%20%3C%2FSPAN%3E%3CSPAN%3E%22%5E37%3A00%2C%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Efoo2%20%3C%2FSPAN%3E%3CSPAN%3E%22(%3Fms)%2C%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Efoo3%20%3C%2FSPAN%3E%3CSPAN%3E%22.*%2C%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Efoo4%20%3C%2FSPAN%3E%3CSPAN%3E%22%2C%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Efoo5%20%3C%2FSPAN%3E%3CSPAN%3E%22%2C%22%3C%2FSPAN%3E%3CSPAN%3E*%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3Efoo%20%2C%20foo2%20%2C%20foo3%20%2C%20foo4%20%2C%20foo5%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-218426%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-218757%22%20slang%3D%22en-US%22%3ERe%3A%20Parsing%20comma%20separated%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-218757%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20a%20way%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3ESyslog%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3EmsgArr%3Dsplit(SyslogMessage%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22%2C%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3EReceive_Time%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESerial_Number%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EType%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESubtype%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EFUTURE_USE1%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E4%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EGenerated_Time%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EVirtual_System%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E6%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EEvent_ID%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EObject%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E8%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EFUTURE_USE2%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E9%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EFUTURE_USE3%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EModule%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E11%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESeverity%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E12%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDescription%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E13%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESequence_Number%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E14%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EAction_Flags%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E15%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDevice_Group_Hierarchy_Level_1%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E16%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDevice_Group_Hierarchy_Level_2%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E17%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDevice_Group_Hierarchy_Level_3%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E18%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDevice_Group_Hierarchy_Level_4%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E19%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EVirtual_System_Name%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E20%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDevice_Name%3DmsgArr%5B%3C%2FSPAN%3E%3CSPAN%3E21%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3BHowever%2C%20occasionally%20I%20have%20a%20Syslog%20message%20that%20comes%20in%20that%20has%20an%20extra%20comma%20in%20a%20field%2C%20which%20pushes%20the%20other%20columns%20over.%20Still%20trying%20to%20work%20through%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Scott Allison
Contributor

We're using a Syslog to get data to Log Analytics from some devices. This works perfectly and places the relevant data we need in the "Syslogmessage" column in "Syslog". I'm trying to use the parse command to extract that data into new columns but cannot figure out how to do it. Here is a sample of the value in that column:

37:00,0008C101547,SYSTEM,userid,0,2018/07/23 11:36:58,,connect-ldap-sever-failure,SERVER1.DOM1.DOMAIN1,0,0,general,medium,"ldap cfg CT Group Mapping failed to connect to server SERVER1.DOM1.DOMAINNAME.net:389: Error: Failed to get address info for SERVER1.DOM1.DOMAINNAME.net.",38678840,0x8000000000000000,0,0,0,0,,DOM1FHS01.LAB

 

I figured I could use regex to extract what I need, but cannot seem to get it to work correctly. What's the best way to do this?

 

Example of something I've tried:

 

Syslog
| parse kind=regex SyslogMessage with foo "^37:00,"
foo2 "(?ms),"
foo3 ".*,"
foo4 ","
foo5 "," *
| project foo , foo2 , foo3 , foo4 , foo5

 

1 Reply

I found a way:

 

Syslog

| extend msgArr=split(SyslogMessage, ",")
| project Receive_Time=msgArr[0],
Serial_Number=msgArr[1],
Type=msgArr[2],
Subtype=msgArr[3],
FUTURE_USE1=msgArr[4],
Generated_Time=msgArr[5],
Virtual_System=msgArr[6],
Event_ID=msgArr[7],
Object=msgArr[8],
FUTURE_USE2=msgArr[9],
FUTURE_USE3=msgArr[10],
Module=msgArr[11],
Severity=msgArr[12],
Description=msgArr[13],
Sequence_Number=msgArr[14],
Action_Flags=msgArr[15],
Device_Group_Hierarchy_Level_1=msgArr[16],
Device_Group_Hierarchy_Level_2=msgArr[17],
Device_Group_Hierarchy_Level_3=msgArr[18],
Device_Group_Hierarchy_Level_4=msgArr[19],
Virtual_System_Name=msgArr[20],
Device_Name=msgArr[21]

 However, occasionally I have a Syslog message that comes in that has an extra comma in a field, which pushes the other columns over. Still trying to work through that.