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
Super Champion

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
Super Champion

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!

0 Karma

hippe21
Explorer

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

0 Karma
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...