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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...