How to extract json field if field name is same in json message log?

[Anu1184]

Hi , 

I am trying to extract aggregated errors from json message log coming from splunk event and categorising them basis on status code, status title , and error description. I am unable to extract all fields under same search as field name for status code and status title stands same. 

Current Query_1:

| rex field=message "errorStatus\":\{\"status\":(?<status>[0-9]+),"
| stats count by status

Current Output_1:

Status	Count
404	10
422	20
500	30

Current Query_2:

| rex field=message "title\":\"(?<title>[^\"]+)"
| rex field=message "status\":\"(?<status>[^\"]+)"
| spath input=title
| spath input=status
| stats count by status, title

Current Output_2:

Status	Title	Count
Service_A_Failed	Site error	10
Service_B_Failed	User Error	20
Service_C_Failed	Infra Error	30

Expected Output: want to merge above both outputs in single query.

Status Code	Component_Status	Title	Count
404	Service_A_Failed	Site error	10
422	Service_B_Failed	User Error	20
500	Service_C_Failed	Infra Error	30

[yuanliu]

You don't want to treat structured data like JSON as text. It only leads to instability and is difficult to maintain.  If the raw event is compliant JSON, Splunk should have given you all the fields.  If for some reason it doesn't, could you post sample raw events so we can help you extract the structure?

[Anu1184]

Sure here is the splunk search event. and I want to extract statistics from message like count basis on - status_code (400) , status_name(Service_Failure), and title.

Splunk _Event:


    level: ERROR
   logger_name: c.a.s.c.w.AsyncMessageHandler
   message: Marked request as failed. {"status":"Service_Failure","message":"can  not read frames","senseiStatus":{"status":400,"title":"failing in extracting frame: can not read  frames","type":""}}

[yuanliu]

If you want to extract the maximum amount of information, first use extract to get level and logger_name. (Or skip this step if they are not important.)  Then, separate the JSON portion from message, and use spath to extract JSON.  Like this

| extract kvdelim=": " pairdelim="\n" ``` not necessary if level and logger_name are not of interest ```
| rex "(.*\n)*\s*message: (?<action>[^{]*)\s*(?<details>.*)"
| spath input=details

The sample event gives this output:

action	level	logger_name	senseiStatus.status	senseiStatus.title	senseiStatus.type	status
Marked request as failed.	ERROR	c.a.s.c.w.AsyncMessageHandler	400	failing in extracting frame: can not read frames		Service_Failure

Here is an emulation that you can play with and compare with real data

| makeresults
| eval _raw = "    level: ERROR
   logger_name: c.a.s.c.w.AsyncMessageHandler
   message: Marked request as failed. {\"status\":\"Service_Failure\",\"message\":\"can  not read frames\",\"senseiStatus\":{\"status\":400,\"title\":\"failing in extracting frame: can not read  frames\",\"type\":\"\"}}"
``` data emulation above ```