Archive

need to split message field into different fields

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

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

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

New Member

Thank you it worked.

0 Karma

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

New Member

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

0 Karma

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

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