Reporting

need to split message field into different fields

adityanischal
New Member

Hi Guys,

I am new in creating reports in splunk.
I have data log set where one of the field is message and i need your assistance spiting it into different fields.
Below is an example of the logs
{"message": "2019-04-05\t12:51:24\t89.885.664.006\tPOST\t/is.efg.loud/i/api/v1/meetings/131/status/confirm\t200\t1148\t1\t\"https://so.efg.com/i/dl/\"\t\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.3683.86 Safari/537.36\"\t\"auth-user={\"\"sub\"\":\"\"auth0|599\"\",\"\"nickname\"\":\"\"mi_te\"\",\"\"name\"\":\"\"e_svie\"\",\"\"iss\"\":\"\"https://tenant1.efg.com/\"\",\"\"iat\"\":155,\"\"exp\"\":1554,\"\"email_verified\"\":true,\"\"email\...}; auth-token=eyJoV...\"\n"}

The fields are separated by "\t"
I need to get the values to below fields:
date time cs-ip cs-method cs-uri sc-status sc-bytes time-taken cs(Referer) cs(User-Agent) cs(Cookie)

I tried to run the query:
My index|| rex field=message "(?)\t(?)\t(?)\t(?)\t(?)\t(?)\t(?)\t(?)\t(?)\t(?)\t(?)"|table date,time,cs-ip,cs-method,cs-uri,sc-status,sc-bytes,time-taken,cs-Referer,cs-User-Agent,cs-Cookie

but didn't work.

Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

OK, so the raw log contains literal \t.

I now realize that automatic key-value extraction for json actually translates those \t and \n into tabs and newlines by the look of it. So perhaps that is where the confusion came from 🙂

This seems to work:

| makeresults | eval _raw="{\"message\": \"2019-04-05\t12:51:24\t89.885.664.006\tPOST\t/is.efg.loud/i/api/v1/meetings/131/status/confirm\t200\t1148\t1\t\\\"https://so.efg.com/i/dl/\\\"\t\\\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.3683.86 Safari/537.36\\\"\t\\\"auth-user={\\\"\\\"sub\\\"\\\":\\\"\\\"auth0|599\\\"\\\",\\\"\\\"nickname\\\"\\\":\\\"\\\"mi_te\\\"\\\",\\\"\\\"name\\\"\\\":\\\"\\\"e_svie\\\"\\\",\\\"\\\"iss\\\"\\\":\\\"\\\"https://tenant1.efg.com/\\\"\\\",\\\"\\\"iat\\\"\\\":155,\\\"\\\"exp\\\"\\\":1554,\\\"\\\"email_verified\\\"\\\":true,\\\"\\\"email\\\"\\\":\\\"\\\"xyz@abc.com\\\"\\\",\\\"\\\"aud\\\"\\\":\\\"\\\"xApat6P\\\"\\\",\\\"\\\"amr\\\"\\\":[\\\"\\\"mfa\\\"\\\"],\\\"\\\"acr\\\"\\\":\\\"\\\"http://schemas.openid.net/pape/policies/2007/06/multi-factor\\\"\\\"}; auth-token=eyJoV...\\\"\n\"}"
| kv
| rex field=message "(?<date>\S+)\t(?<time>\S+)\t(?<cs_ip>\S+)\t(?<cs_method>\S+)\t(?<cs_uri>\S+)\t(?<sc_status>\d+)\t(?<sc_bytes>\d+)\t(?<time_taken>\d+)\t\"(?<cs_Referer>[^\"]+)\"\t\"(?<cs_User_Agent>[^\"]+)\"\t\"(?<cs_Cookie>.+)\""

Note: the makeresults and eval are just there to generate your sample event. The kv command performs the automatic extraction of the json message field. You have all that already, so just need the rex part.

View solution in original post

0 Karma

FrankVl
Ultra Champion

OK, so the raw log contains literal \t.

I now realize that automatic key-value extraction for json actually translates those \t and \n into tabs and newlines by the look of it. So perhaps that is where the confusion came from 🙂

This seems to work:

| makeresults | eval _raw="{\"message\": \"2019-04-05\t12:51:24\t89.885.664.006\tPOST\t/is.efg.loud/i/api/v1/meetings/131/status/confirm\t200\t1148\t1\t\\\"https://so.efg.com/i/dl/\\\"\t\\\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.3683.86 Safari/537.36\\\"\t\\\"auth-user={\\\"\\\"sub\\\"\\\":\\\"\\\"auth0|599\\\"\\\",\\\"\\\"nickname\\\"\\\":\\\"\\\"mi_te\\\"\\\",\\\"\\\"name\\\"\\\":\\\"\\\"e_svie\\\"\\\",\\\"\\\"iss\\\"\\\":\\\"\\\"https://tenant1.efg.com/\\\"\\\",\\\"\\\"iat\\\"\\\":155,\\\"\\\"exp\\\"\\\":1554,\\\"\\\"email_verified\\\"\\\":true,\\\"\\\"email\\\"\\\":\\\"\\\"xyz@abc.com\\\"\\\",\\\"\\\"aud\\\"\\\":\\\"\\\"xApat6P\\\"\\\",\\\"\\\"amr\\\"\\\":[\\\"\\\"mfa\\\"\\\"],\\\"\\\"acr\\\"\\\":\\\"\\\"http://schemas.openid.net/pape/policies/2007/06/multi-factor\\\"\\\"}; auth-token=eyJoV...\\\"\n\"}"
| kv
| rex field=message "(?<date>\S+)\t(?<time>\S+)\t(?<cs_ip>\S+)\t(?<cs_method>\S+)\t(?<cs_uri>\S+)\t(?<sc_status>\d+)\t(?<sc_bytes>\d+)\t(?<time_taken>\d+)\t\"(?<cs_Referer>[^\"]+)\"\t\"(?<cs_User_Agent>[^\"]+)\"\t\"(?<cs_Cookie>.+)\""

Note: the makeresults and eval are just there to generate your sample event. The kv command performs the automatic extraction of the json message field. You have all that already, so just need the rex part.

0 Karma

adityanischal
New Member

Thank you it worked.

0 Karma

swagner1965
Path Finder

when you say the fields are separated by \t do you mean TAB and you have substitued the regex \t or is the log literally like you present it here?

0 Karma

adityanischal
New Member

yes, Logs are separated by TAB and yes the log present about is actual log from the application.

0 Karma

FrankVl
Ultra Champion

It can't be both. Either the log you present here is the literal log you are working with (incl. literal \t string) or your log is TAB separated (\t represents TAB in regex language).

Can you perhaps upload a screenshot somewhere and post the link here?

Also: please post your current regex (and ideally also the log samples) as code, using the 101010 button in the message editor). Now all kinds of special characters disappear, making it impossible to help you improve the regex.

0 Karma

adityanischal
New Member

Actual Log is this

{"message": "2019-04-05\t12:51:24\t89.885.664.006\tPOST\t/is.efg.loud/i/api/v1/meetings/131/status/confirm\t200\t1148\t1\t\"https://so.efg.com/i/dl/\"\t\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.3683.86 Safari/537.36\"\t\"auth-user={\"\"sub\"\":\"\"auth0|599\"\",\"\"nickname\"\":\"\"mi_te\"\",\"\"name\"\":\"\"e_svie\"\",\"\"iss\"\":\"\"https://tenant1.efg.com/\"\",\"\"iat\"\":155,\"\"exp\"\":1554,\"\"email_verified\"\":true,\"\"email\...}; auth-token=eyJoV...\"\n"}

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...