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)
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)]
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)]
Perfect - Thank you
Hey, that's clever!