Splunk Search

How can I calculate column differences when column names are unknown?

c_wsleem
New Member

My datasource is a json structure which will include the following on each record:

{

"metrics":
[
{"name":"MetricName1", "value":"1", "units": "s"},
{"name":"MetricName2", "value":"1", "units": "s"},
{"name":"MetricName3", "value":"2", "units": "s"}
]

}

The initial search will produce 2 records with different timestamps
My SPL is expanding the metrics field, then recombining the data to give the metric names as rows, the timestamps as columns so that I can chart how the vlaues change between the two events.
SPL:-

| eval name=strftime('_time',"%d/%m/%Y %H:%M:%S") 
|spath path=metrics{} output=X
|  mvexpand X
| fields name, X
|fields - _raw, _time 
| spath input=X  
| fields - X, units 
| rename type as Metric
| eval {Metric}='value'
| fields name Metric value 
| xyseries Metric, name, value

Output:-

| Metric                  | Event1Date | Event2Date|
| MetricName1     | 1                    | 2                  |
| MetricName2     | 2                    | 4                  |
| MetricName3     | 2                    | 5                  |

I would like to add a 3rd column containing the differences between the two values:

| Metric                  | Event1Date | Event2Date| Difference |
| MetricName1     | 1                    | 2                  | 1                 |
| MetricName2     | 2                    | 4                  | 2                 |
| MetricName3     | 2                    | 5                  | 3                 |

But I do not know what the column names "Event1Date" and "Event2Date" will be
Can I access the fields by an index number?
eg eval diff = columns[2] - columns[1]
or similar
NB The actual Metric Names are also unknown (ie they could be anything)

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try (note that format of timestamp has been changed)

| eval name=strftime('_time',"%Y/%m/%d %H:%M:%S") 
 |spath path=metrics{} output=X
 |  mvexpand X
 | fields name, X
 |fields - _raw, _time 
 | spath input=X  
 | fields - X, units 
 | rename type as Metric
 | eval {Metric}='value'
 | fields name Metric value 
 | xyseries Metric, name, value
| eval Difference=0 | foreach 2* [| eval Difference=if(Difference=0,'<<FIELD>>','<<FIELD>>"-Difference)]

View solution in original post

somesoni2
Revered Legend

Give this a try (note that format of timestamp has been changed)

| eval name=strftime('_time',"%Y/%m/%d %H:%M:%S") 
 |spath path=metrics{} output=X
 |  mvexpand X
 | fields name, X
 |fields - _raw, _time 
 | spath input=X  
 | fields - X, units 
 | rename type as Metric
 | eval {Metric}='value'
 | fields name Metric value 
 | xyseries Metric, name, value
| eval Difference=0 | foreach 2* [| eval Difference=if(Difference=0,'<<FIELD>>','<<FIELD>>"-Difference)]

c_wsleem
New Member

Perfect - Thank you

0 Karma

elliotproebstel
Champion

Hey, that's clever!

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...