topic Re: Making a table from data with objects in an array in Splunk Search

Making a table from data with objects in an array

benobviate — Wed, 21 Nov 2012 08:57:46 GMT

I have data that looks like

{
event: "request",
timers: [
{
category : "serverA",
duration : 3,
host : abc
},
{
category: "serverB",
duration: 4,
host: abc
},
{
category: "serverA",
duration: 6,
host: xyz
}
]
}

The timers array can vary in the number of objects it has. I want to go through all the data pulling out information from each object in the timers array, and make a table that looks something like:

_time category duration host

8:30 serverA 3 abc

8:30 serverB 4 abc

8:30 serverA 6 xyz

I would then ALSO like to be able to make a timechart of this by host, so .... | timechart avg(duration) by host

The problem I am having is getting it into the table above AND being able to make an efficient timechart. Right now, I am using mvzip and mvexpand in order to get it to the format of the table above. I have a feeling that this is the main reason why the timechart currently takes forever to load. Is there any easy way to extract information from objects in an array and then put it in a table like above?

Without using mvzip and mvexpand, and trying to just show a table with those fields causes a table like this for me:

_time category duration host

8:30 serverA 3 abc

         serverB     4    abc

         serverA   6   xyz

That is, objects in the same timers array payload seem to clump together into the same row.

Re: Making a table from data with objects in an array

alacercogitatus — Wed, 21 Nov 2012 11:22:49 GMT

Could we see your search, or at least relevant parts of it? I'm curious to see how you used mvzip to achieve your results.

Re: Making a table from data with objects in an array

jkat54 — Wed, 21 Nov 2012 16:53:50 GMT

You need field extractions or rex.

sourcetype="your_source_type" | rex field=_raw "(?<category>(?<=category.:.")(.*)(?=",))" |rex field=_raw "(?<duration>(?<=duration.:.)(.*)(?=,))" | rex field=_raw "(?<hostname>(?<=host.:.)(.*))"| table _time,category,duration,hostname | rename hostname as host

If that doesnt work, try removing | rename hostname as host.

If that doesnt work, then you gave the wrong data. I "corrected" your data as follows (spacing between colons).

{
event: "request",
timers: [

  {

      category : "serverA",

       duration : 3,

      host : abc

   },

  {

     category : "serverB",

       duration : 4,

    host : abc

},

{

    category : "serverA",

    duration : 6,

   host : xyz

}

If this isn't how your data is formatted please let us know.

Re: Making a table from data with objects in an array

benobviate — Wed, 21 Nov 2012 22:15:49 GMT

Thanks. That works. Is there an easy way when doing this to only make rows for when category is serverB ? I currently do something like yoursolution | search category="serverB"

Just wondering if that was a way to do it while it was extracting with rex.