topic Re: How to split columns based on timestamp and field value at the same time from JSON data? in Getting Data In

How to split columns based on timestamp and field value at the same time from JSON data?

shikhanshu — Wed, 17 Sep 2014 00:22:54 GMT

I have JSON data going into my Splunk index. Let's assume I am sending one JSON object array at a time through the REST API. The data looks like this:

[{'time':'timestamp1', 'block':'blockname1', type1_metric1:10, type1_metric2:20, type2_metric1:10, type2_metric2:20 },
{'time':'timestamp1', 'block':'blockname2', type1_metric1:15, type1_metric2:25, type2_metric1:15, type2_metric2:25 }]

[{'time':'timestamp2', 'block':'blockname1', type1_metric1:30, type1_metric2:40, type2_metric1:30, type2_metric2:40 },
{'time':'timestamp2', 'block':'blockname2', type1_metric1:35, type1_metric2:45, type2_metric1:35, type2_metric2:45 }]
...

For a given "metric type prefix" (like type1 or type2), I want to get a search result like this:

                   blockname1                    blockname2
             timestamp1   timestamp2      timestamp1    timestamp2
type1_metric1   10      30                 15           35
type1_metric2   20      40                 25           45

I am fairly new to Splunk Query language and this looks like its going to be a fairly complex query and I am at loss where to even begin.

Can someone help!? Cookies to anyone who can! (I will zip up my browser cookies and mail them to you, I swear!)

Re: How to split columns based on timestamp and field value at the same time from JSON data?

shikhanshu — Wed, 17 Sep 2014 15:00:50 GMT

I have tried "transpose" and that gives the metrics as rows, but I am unable to split a "block" based on "time". Each event shows up as a separate column (which is expected I guess)

Re: How to split columns based on timestamp and field value at the same time from JSON data?

somesoni2 — Mon, 28 Sep 2020 17:39:34 GMT

Are the field extraction configured? Means are you getting following fields - block time type1_metric1 type1_metric2 type2_metric1 type2_metric2

If you are getting fields like that then try this

your base search giving fields  block   time type1_metric1 type1_metric2 type2_metric1 type2_metric2 | eval column=time."-".block | fields - time block | untable column metrics value | chart sum(value) over metrics by column

Re: How to split columns based on timestamp and field value at the same time from JSON data?

shikhanshu — Mon, 22 Sep 2014 23:10:34 GMT

You are a genius! You gave me exactly what I needed. How can I make your comment as the "Answer"?

Re: How to split columns based on timestamp and field value at the same time from JSON data?

ppablo — Mon, 22 Sep 2014 23:42:23 GMT

Hi @shikhanshu

I just converted @somesoni2's comment to an answer 🙂 be sure to accept the answer by clicking on the Accept button. You'll both receive karma points. Glad you got a solution!

Patrick