Not able to create a table from fields extracted from a JSON using regex

[nikhil108]

Hi All,

I wrote a regular expression to extract fields from an event containing data in the JSON format. The regular expression seems to be working fine on https://regex101.com/ but I am not able to transform extracted fields in the tabular format.

Below is the query with regular expression:

index="index_name" "<search term>" | rex field=_raw "\"(errorId)\":(?<errorId>.*),\"(errorMessage)\":(?<errorMessage>.*),\"(exceptionStackTrace)\":(?<exceptionStackTrace>.*),\"(userId)\":(?<userId>.*),\"(requestUri)\":(?<requestUri>.*)}"

Below is the extended query to transform it into a table:

index="index_name" "[search term]" | rex field=_raw "\"(errorId)\":(?<errorId>.*),\"(errorMessage)\":(?<errorMessage>.*),\"(userId)\":(?<userId>.*),\"(requestUri)\":(?<requestUri>.*)}" | table _time errorId errorMessage userId requestUri

Not able to see data in the columns except for _time. Below is the log data:

14321 <14>1 2021-07-07T09:39:53.222524+00:00 service-name 3d5c6a75-9e10-4fad-85bc-9ab8460a2a36 [APP/PROC/WEB/0] - - 2021-07-07 09:39:53,222 [http-nio-8080-exec-7] [ERROR] [Trace: Span: ] [searchTerm] {"errorId":"c9fb515d-5e63-4d30-ae0a-3aea707eea18","errorMessage":"custom error message","userId":"test id 100","requestUri":"uri=/employee/list"}

This is a sample log data but the actual data could be quite complex.

Can anyone please help?

[ITWhisperer]

Do you need the double quotes included in the field values? If not, try something like this

index="index_name" "[search term]" | rex field=_raw "\"errorId\":\"(?<errorId>[^\"]+)\",\"errorMessage\":\"(?<errorMessage>[^\"]+)\",\"userId\":\"(?<userId>[^\"]+)\",\"requestUri\":\"(?<requestUri>[^\"]+)\"}" | table _time errorId errorMessage userId requestUri

[nikhil108]

Hi @ITWhisperer 

Thanks for replying. The regular expression you have shared is working fine on  https://regex101.com/ and returns the fields without double quotes. But not able to see data in the table. The same issue still persists. 

FYI, I am using Splunk Enterprise 7.1.4.

Thanks.

Thanks.

[ITWhisperer]

Are you saying that the rex works in search and returns data in the fields but the table command removes all the events, or that the expression works in regex101 and extracts the fields correctly there but not with your real data in splunk search? Does your real data perhaps include white spaces which are not present in your example?