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

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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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