topic Re: How do I split an array in a .json file into multiple new events? in Getting Data In

How do I split an array in a .json file into multiple new events?

dgutekunst — Fri, 18 Jul 2014 22:29:07 GMT

Right now I have a json file that's formatted like:

{
    "Log Files":[
        {"Date":"2014-07-18 21:22:51", "Available Bytes(kb)":3960078, ...},
        {"Date":"2014-07-18 21:24:01", "Available Bytes(kb)":4001231, ...},
        {"Date":"2014-07-18 21:25:14", "Available Bytes(kb)":3872959, ...}]}

Right now it's showing up in Splunk as:

timestamp                Date                Available Bytes(kb)
2014-07-18 21:22:51:000  2014-07-18 21:22:51 3960078
                         2014-07-18 21:24:01 4001231
                         2014-07-18 21:25:14 3872959

How can I split these up into individual events when I load the data? I can get the timestamp to correctly match the Date field, but it will still only give one date for the whole file, even though there are several lines that are each individual logs.

Re: How do I split an array in a .json file into multiple new events?

pwmcintyre — Mon, 22 Aug 2016 02:31:08 GMT

When you say "... when I load the data" do you mean at search time or index time?

Re: How do I split an array in a .json file into multiple new events?

s2_splunk — Mon, 22 Aug 2016 18:38:11 GMT

Is there anything in the envelope of this array that you want to keep or are you just interested in keeping the events inside the "Log Files" array?
If the latter, setup props/transforms for your sourcetype to:

Get rid of the root '{' line
Get rid of the array root line that contains "Log Files"
Use SEDCMD to change "}," to "}" at the end of the lines
Use SEDCMD to remove "]}"
Set TIMESTAMP_PREFIX to \{\"Date\":\"

That should result in individual, valid JSON events that should render fine in the UI.

Re: How do I split an array in a .json file into multiple new events?

vasanthmss — Mon, 22 Aug 2016 18:55:01 GMT

Try something like this,

<your base search...> | table  timestamp, Date, Available | eval temp=mvzip(timestamp, mvzip(Date, Available,"###"), "###") | mvexpand temp | rex field=temp "(?<timestamp>.*)###(?<Date>.*)###(?<Available>.*)" | fields - temp

I have used the sample fields, you can try with your actual fields. Concept here is, you need to zip it with a delimiter (here ###) and expand it and extract it. This do the magic