Splunk Search

How to extract fields from JSON data in a CSV file?

jarjoh42
Path Finder

Fellow Splunkers

I have a report that is sent from an outside vendor. The file is in the form of a CSV file but the last 2 columns the data is in JSON. I have used a props. to pull the rest of the fields out including the JSON field but need to find a way to display the JSON data in a readable way.

Single event data

infected,1,9/24/2014 11:20,s4test16A1,machineID1,100.100.1.100,TRUE,11/1/2011 15:30,Busid1|userid1,yourorganizationhere.1.20100101-060000,7/5/2012 9:04,Zeus 2,high,MFF.Zeus_2,infected_and_blocked_and_removal_initiated,"{""Zeus 2"":{""last"":""2012-05-07 09:04:12"",""first"":""2012-05-07 09:04:12"",""removal_started"":""2012-05-07 09:04:12"",""severity"":""high""}}",

regex to pull last field

EXTRACT-ExistingInfections = (?i)^(?:[^,]*,){15}(?P[^,]+)

EXTRACT-CorruptedSystemFiles = (?i)^(?:[^,]*,){16}(?P[^,]+)

The goal is to display the data in the field as

Existing Infections

Zeus 2

last 2012-05-07 09:04:12

First 2012-05-07 09:04:12

0 Karma

sowings
Splunk Employee
Splunk Employee
  1. You might consider DELIMS in transforms.conf for extracting fields from the CSV data, rather than your EXTRACT statements.
  2. Once you have a field containing your JSON data, you can do | spath input=json_field to extract the structured data.

http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Spath

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...