Parse/extract optional extra fields

Follow

O A

• December 14, 2015 16:48

I currently have a mixture of logs containing 4 and 6 comma separated fields; I need to parse them together on same query.
Messages of first type:

• p,10,myname1,mytype1,us,
• p,10,myname2,mytype2,br,

Messages of second type

• p,10,myname3,mytype3,au,mychannel1,mypid1,
• p,10,myname4,mytype4,us,mychannel2,mypid2,

The two parse statements that needs to be joined together:

1. parse "p,*,*,*,*," as samplerate,name,type,geo nodrop
2. parse "p,*,*,*,*,*,*," as samplerate,name,type,geo,channel,pid nodrop

I managed to come up with the following regexp parse, but it came out pretty intimidating :)

• parse regex "p,(?<samplerate>[^\,]+),(?<name>[^\,]+),(?<type>[^\,]+),(?<geo>[^\,]+)?,?(?<channel>[^\,]+)?,?(?<subid>[^\,]+)?,?" nodrop

Is there a simpler option? Perhaps something like this:

• parse "p,*,*,*,*," as samplerate,name,type,geo nodrop | 
  parse "p,*,*,*,*,*,*," as ,,,,channel,pid nodrop

0

• 

  Mario Sanchez

  • December 14, 2015 21:33
  • Edited

  Your parse regex expression seems to do the trick, I would go with that. I'm not sure you can use parse anchor (the simpler/cleaner way) in this case. However, if you do want to explore options, you could try the CSV parsing operator: https://service.sumologic.com/help/#CSV_Operator.htm

  Note the field name you would be extracting from is named _raw (i.e. your raw message). 

  Cheers,

  Mario

  0

  Comment actions Permalink
• 

  O A

  • December 14, 2015 22:11
  • Edited

  Thanks Mario

  I actually came up with another theoretical option:

  parse "p,*,*,*," as name,type,geo nodrop
  | parse "p,*,*,*,*,*," as unused1,unused2,unused3,channel,pid nodrop

  But in the end, to resolve this issue I have added several extra commas to the end of each message, like this:

  • p,10,myname1,mytype1,us,,,,,
  • p,10,myname2,mytype2,br,,,,,
  • p,10,myname3,mytype3,de,,,,,

  This way if ever I want to add more fields, all I have to do is add them to the parse string, and I'm good for both old and new message types. The following parse statement would work for both old format with extra commas, and new format with the two new fields:

  parse "p,*,*,*,*,*," as samplerate,name,type,geo,new_field1,new_field2 nodrop

   

  0

  Comment actions Permalink

Please sign in to leave a comment.