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.
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.
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.
Thank you it worked.
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?
yes, Logs are separated by TAB and yes the log present about is actual log from the application.
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.
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"}