topic Re: Need help on removing the double quotes and reading a substring in Splunk Search

Help on removing the double quotes and reading a substring

manojchacko78 — Thu, 29 Sep 2022 14:52:12 GMT

I have the below string in my error log

{"@odata.context":"https://apistaging.payspace.com/odata/v1.1/11846/$metadata#EmployeePosition/$entity","Message":"Invalid value for field Directly reports to Employee Number.","Details":[{"Message":"Invalid value for field Directly reports to Employee Number."}],"Success":false}

I have the code as shown below

| makeresults
| eval test = "{"@odata.context":"https://apistaging.payspace.com/odata/v1.1/11846/$metadata#EmployeePosition/$entity","Message":"Invalid value for field Directly reports to Employee Number.","Details":[{"Message":"Invalid value for field Directly reports to Employee Number."}],"Success":false}"
| rex field=test max_match=0 "(?<test>\w+)"
| eval test = mvjoin (test, "-")

Now the code works by removing all the wild characters, but throws an error as I have double quotes.

So need to know how i can ignore the quotes or replace it and then only need to get the string message which i have made in bold.

Re: Need help on removing the double quotes and reading a substring

gcusello — Thu, 29 Sep 2022 06:29:46 GMT

Hi @manojchacko78,

this log seems to be a json format, did you tried with the spath command?

Another question, what do you want to extract, the Message field?

if you want to extract the Message field you have to use a different regex:

| rex field=test "\"Message\":\"(?<message>[^\"]+)"

you can test this regex at https://regex101.com/r/zk7QCa/1

ciao.

Giuseppe

Re: Need help on removing the double quotes and reading a substring

manojchacko78 — Thu, 29 Sep 2022 09:30:14 GMT

Thanks @gcusello

Yes this is JSON log. I was able to remove the wild characters from the error log and got this now

odata context https apistaging payspace com odata v1 1 12082 metadata EmployeeEmploymentStatus entity Message Committed transactions exist you may not change the employment date to a future date Committed transactions exist in the current records tax year you may not change the employment date Employment Date may not be after pay rate effective date or position effective date Details Message Committed transactions exist you may not change the employment date to a future date Message Committed transactions exist in the current records tax year you may not change the employment date Message Employment Date may not be after pay rate effective date or position effective date Success false

Now i want to extract texts between Message and Details and Message and Success. These are fixed keywords, so need to extract the error message which are between these words.

Thanks in advance.

Re: Need help on removing the double quotes and reading a substring

manojchacko78 — Thu, 29 Sep 2022 09:32:34 GMT

Also indicate the keyword Message will repeat more than once in log as you see in the sample pasted

Re: Need help on removing the double quotes and reading a substring

gcusello — Thu, 29 Sep 2022 09:52:38 GMT

Hi @manojchacko78,

please try this regex

Message\s+(?<Message1>.*)\s+Details\s+Message\s+(?<message2>.*)\s+Success

that you can test at https://regex101.com/r/V015Wc/1

Ciao.

Giuseppe

Re: Need help on removing the double quotes and reading a substring

manojchacko78 — Thu, 29 Sep 2022 10:36:44 GMT

Thanks @gcusello

It worked, Perfect thanks a lot. 🙂

Re: Need help on removing the double quotes and reading a substring

SinghK — Thu, 29 Sep 2022 10:46:03 GMT

you need to correct the sourcetype for the json above it should json_no_timestamp once you do that Splunk will auto put that message under message field.

Re: Need help on removing the double quotes and reading a substring

gcusello — Thu, 29 Sep 2022 10:58:31 GMT

Hi @manojchacko78,

if one answer solves your need, please accept one answer for the other people of Community or tell us how we can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors;-)