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