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

[shikhanshu]

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!)

[somesoni2 · Accepted solution]

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

[shikhanshu]

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

[ppablo]

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

[shikhanshu]

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)