Splunk Search

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

Anu1184
Explorer

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




Labels (2)
Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Explorer

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":""}}

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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:

actionlevellogger_namesenseiStatus.statussenseiStatus.titlesenseiStatus.typestatus
Marked request as failed.ERRORc.a.s.c.w.AsyncMessageHandler400failing 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 ```

 

 

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...