Splunk Search

Extracting data from JSON log event

hippe21
Explorer

I currently have the below event. I'm trying to extract the field name, and value. I'm unable to edit config for this app, so will either be doing manual extraction using Splunk's extract Field option, or doing it via search. I would rather do this via search as I'd also like to better understand rex, or whichever command will work for this. Thank you in advance!

Here's my sample event:

2018-03-27T15:30:51.744+0000  line:5  [{"date":"2018-03-27","stats":[{"metrics":{"blocks":2,"bounce_drops":9,"bounces":14,"clicks":0,"deferred":0,"delivered":10341,"invalid_emails":1,"opens":6128,"processed":0,"requests":10367,"spam_report_drops":1,"spam_reports":0,"unique_clicks":0,"unique_opens":2147,"unsubscribe_drops":1,"unsubscribes":0}}]}]

From here, I would like to run stats against different fields (ie: blocks, bounces, etc).

0 Karma
1 Solution

mayurr98
SplunkTrust
SplunkTrust

Try this run anywhere search

| makeresults 
| eval _raw="2018-03-27T15:30:51.744+0000  line:5  [{\"date\":\"2018-03-27\",\"stats\":[{\"metrics\":{\"blocks\":2,\"bounce_drops\":9,\"bounces\":14,\"clicks\":0,\"deferred\":0,\"delivered\":10341,\"invalid_emails\":1,\"opens\":6128,\"processed\":0,\"requests\":10367,\"spam_report_drops\":1,\"spam_reports\":0,\"unique_clicks\":0,\"unique_opens\":2147,\"unsubscribe_drops\":1,\"unsubscribes\":0}}]}]" 
| rex field=_raw "blocks\":(?<blocks>[^\,]+)\,\"bounce_drops\":(?<bounce_drops>[^\,]+)\,\"bounces\":(?<bounces>[^\,]+)\,\"clicks\":(?<clicks>[^\,]+)\,\"deferred\":(?<deferred>[^\,]+)\,\"delivered\":(?<delivered>[^\,]+)\,\"invalid_emails\":(?<invalid_emails>[^\,]+)\,\"opens\":(?<opens>[^\,]+)\,\"processed\":(?<processed>[^\,]+)\,\"requests\":(?<requests>[^\,]+)\,\"spam_report_drops\":(?<spam_report_drops>[^\,]+)\,\"spam_reports\":(?<spam_reports>[^\,]+)\,\"unique_clicks\":(?<unique_clicks>[^\,]+)\,\"unique_opens\":(?<unique_opens>[^\,]+)\,\"unsubscribe_drops\":(?<unsubscribe_drops>[^\,]+)\,\"unsubscribes\":(?<unsubscribes>\d+)"

To automate it, go to Settings » Fields » Field extractions » Add new

Name: All
Apply to : write the name of the sourcetype/host/source
Extraction/Transform :

blocks\":(?<blocks>[^\,]+)\,\"bounce_drops\":(?<bounce_drops>[^\,]+)\,\"bounces\":(?<bounces>[^\,]+)\,\"clicks\":(?<clicks>[^\,]+)\,\"deferred\":(?<deferred>[^\,]+)\,\"delivered\":(?<delivered>[^\,]+)\,\"invalid_emails\":(?<invalid_emails>[^\,]+)\,\"opens\":(?<opens>[^\,]+)\,\"processed\":(?<processed>[^\,]+)\,\"requests\":(?<requests>[^\,]+)\,\"spam_report_drops\":(?<spam_report_drops>[^\,]+)\,\"spam_reports\":(?<spam_reports>[^\,]+)\,\"unique_clicks\":(?<unique_clicks>[^\,]+)\,\"unique_opens\":(?<unique_opens>[^\,]+)\,\"unsubscribe_drops\":(?<unsubscribe_drops>[^\,]+)\,\"unsubscribes\":(?<unsubscribes>\d+)

let me know if this helps!

View solution in original post

0 Karma

mayurr98
SplunkTrust
SplunkTrust

Try this run anywhere search

| makeresults 
| eval _raw="2018-03-27T15:30:51.744+0000  line:5  [{\"date\":\"2018-03-27\",\"stats\":[{\"metrics\":{\"blocks\":2,\"bounce_drops\":9,\"bounces\":14,\"clicks\":0,\"deferred\":0,\"delivered\":10341,\"invalid_emails\":1,\"opens\":6128,\"processed\":0,\"requests\":10367,\"spam_report_drops\":1,\"spam_reports\":0,\"unique_clicks\":0,\"unique_opens\":2147,\"unsubscribe_drops\":1,\"unsubscribes\":0}}]}]" 
| rex field=_raw "blocks\":(?<blocks>[^\,]+)\,\"bounce_drops\":(?<bounce_drops>[^\,]+)\,\"bounces\":(?<bounces>[^\,]+)\,\"clicks\":(?<clicks>[^\,]+)\,\"deferred\":(?<deferred>[^\,]+)\,\"delivered\":(?<delivered>[^\,]+)\,\"invalid_emails\":(?<invalid_emails>[^\,]+)\,\"opens\":(?<opens>[^\,]+)\,\"processed\":(?<processed>[^\,]+)\,\"requests\":(?<requests>[^\,]+)\,\"spam_report_drops\":(?<spam_report_drops>[^\,]+)\,\"spam_reports\":(?<spam_reports>[^\,]+)\,\"unique_clicks\":(?<unique_clicks>[^\,]+)\,\"unique_opens\":(?<unique_opens>[^\,]+)\,\"unsubscribe_drops\":(?<unsubscribe_drops>[^\,]+)\,\"unsubscribes\":(?<unsubscribes>\d+)"

To automate it, go to Settings » Fields » Field extractions » Add new

Name: All
Apply to : write the name of the sourcetype/host/source
Extraction/Transform :

blocks\":(?<blocks>[^\,]+)\,\"bounce_drops\":(?<bounce_drops>[^\,]+)\,\"bounces\":(?<bounces>[^\,]+)\,\"clicks\":(?<clicks>[^\,]+)\,\"deferred\":(?<deferred>[^\,]+)\,\"delivered\":(?<delivered>[^\,]+)\,\"invalid_emails\":(?<invalid_emails>[^\,]+)\,\"opens\":(?<opens>[^\,]+)\,\"processed\":(?<processed>[^\,]+)\,\"requests\":(?<requests>[^\,]+)\,\"spam_report_drops\":(?<spam_report_drops>[^\,]+)\,\"spam_reports\":(?<spam_reports>[^\,]+)\,\"unique_clicks\":(?<unique_clicks>[^\,]+)\,\"unique_opens\":(?<unique_opens>[^\,]+)\,\"unsubscribe_drops\":(?<unsubscribe_drops>[^\,]+)\,\"unsubscribes\":(?<unsubscribes>\d+)

let me know if this helps!

View solution in original post

0 Karma

hippe21
Explorer

Your rex command works perfectly, thank you! This is exactly what I was looking for!

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.