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. 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies