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

[jarjoh42]

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

[sowings]

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