Getting Data In

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

shikhanshu
Path Finder

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

1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Path Finder

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

ppablo
Retired

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

0 Karma

shikhanshu
Path Finder

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)

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...