Parsing out message

2 followers
1
Avatar

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.

Vishal Chitrala Answered

Official comment

  • Avatar
    Pablo Redondo

    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) ...

    1

5 comments

  • Avatar
    Pablo Redondo

    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
  • Avatar
    Vishal Chitrala

    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
  • Avatar
    Pablo Redondo

    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
Please sign in to leave a comment.