Parsing out message

Answered

Follow

Vishal Chitrala

• December 09, 2015 17:30

I'm working with a source data stored in Hadoop, Is there any good way to parse out this message like breaking up into meaningful chunks :
t.HEAD.S.olbProviders.00c1.findByUnit - |1|1.114|404|java|api-client|api-views|10.28.124.113|X|na|lucky=true&advertiserUrl=/advertisers/0020/2c211837-2c8f-42c4-9452-268559a7cd55

I tried JSON_VALUE(#avrologs_daily_logEvents!message;"advertiserUrl")
it gives me error.

1

• Official comment

  

  Pablo Redondo

  • December 09, 2015 17:53

  if you need to parse it through the spreadsheet I would use tokenizelist and then use listelement to extract each value from the list. FYI, noticed that the character "|" will need a backward slash for it to not be confused with a special character. So if you enter it in the formula builder use "|". Basically your formulas will look like this:

  TOKENIZELIST(#yourcolumn;"\|")
  LISTELEMENT(#yourcolumn_Tokenized;0)
  LISTELEMENT(#yourcolumn_Tokenized;1) ...

  Comment actions Permalink
• 

  Pablo Redondo

  • December 09, 2015 17:59

  FYI: Some how my backward slashes get removed from my comments so make sure the formula bar there is two backward slashes ("\") before the ("|").

  0

  Comment actions Permalink
• 

  Vishal Chitrala

  • December 09, 2015 18:39

  Thank you for your help , I was able to parse out above message into something like :
  [t.GET.E.druids.0060.olbMetricGroups - , 5, 1.114, 200, java, api-client, api-views, 10.28.116.117, X, a7467804-c120-413f-a7a4-4ee0dd7ad3df, na, application/xml, na, na, , ssl, ]

  can you let me know if I can get individual elements from it.. I could not make a proper conclusion from listelement.. thanks

  0

  Comment actions Permalink
• 

  Pablo Redondo

  • December 09, 2015 19:08

  Sure, so what you have now is a list object. Basically all your elements in a list. The next step is to extract each element into its own column. That is where the LISTELEMENT function comes in handy. So the input of the functions are as follows LISTELEMENT(<your list object>;<the position of the element in your list, starting on 0>). So take for example your value: [t.GET.E.druids.0060.olbMetricGroups - , 5, 1.114, 200, java, api-client, api-views, 10.28.116.117, X, a7467804-c120-413f-a7a4-4ee0dd7ad3df, na, application/xml, na, na, , ssl, ]. IF you set LISTELEMENT(#value;0) will return "t.GET.E.druids.0060.olbMetricGroups -", then LISTELEMENT(#VALUE;2) will return "1.114". Let me know if this makes sense..

  1

  Comment actions Permalink
• 

  Vishal Chitrala

  • December 09, 2015 22:06

  Thank you Pablo for good explanation and help!

  0

  Comment actions Permalink

Please sign in to leave a comment.