Summing values inside mvfield

[rvsroe]

Hi All,

I am trying to substract values (timestamps) of an mv field, but they are of differing lengths;

## example data:
sysmodtime,idnumber,epoch time
05/03/20 12:40 PM,1,1588502400
05/01/20 12:01 AM,1,1588284060
05/01/20 12:02 AM,1,1588284120
05/01/20 12:02 AM,1,1588284120

05/02/20 12:00 PM,2,1588413600
04/02/20 12:00 AM,2,1585778400
04/02/20 01:00 AM,2,1585782000

04/02/20 02:00 AM,3,1585785600

##desired outcome: = new field time-diff at the end:
sysmodtime,idnumber,epoch time,time_diff
05/03/20 12:40 PM,1,1588502400,218340
05/01/20 12:01 AM,1,1588284060,-60
05/01/20 12:02 AM,1,1588284120,0
05/01/20 12:02 AM,1,1588284120,empty

05/02/20 12:00 PM,2,1588413600,2635200
04/02/20 12:00 AM,2,1585778400,-3600
04/02/20 01:00 AM,2,1585782000,empty

04/02/20 02:00 AM,3,1585785600,empty

-------------------------------
The original data is about 200.000 rows long, so we are looking for a structural solution.

Is there a simple way to loop through the timestamp value inside the mvfield and substract it and place it in a new field
Any suggestions would be very welcome,

Cheers,
Roelof

[bowesmana · Accepted solution]

I believe you will have to mvexpand out the fields first to get the mv field into separate rows and then run streamstats. Take this for example

| makeresults
| eval _raw="sysmodtime,idnumber,epoch time
05/03/20 12:40 PM,1,1588502400
05/01/20 12:01 AM,1,1588284060
05/01/20 12:02 AM,1,1588284120
05/01/20 12:02 AM,1,1588284120
05/02/20 12:00 PM,2,1588413600
04/02/20 12:00 AM,2,1585778400
04/02/20 01:00 AM,2,1585782000
04/02/20 02:00 AM,3,1585785600"
| multikv
| stats list(*) as * by idnumber
| eval x="So, up to here is your original data"
| eval base=mvzip(sysmodtime,epoch_time)
| fields - _raw linecount sysmodtime epoch_time
| mvexpand base
| rex field=base "(?<sysmodtime>[^,]*),(?<epoch_time>.*)"
| fields - base
| streamstats window=2 range(epoch_time) as time_diff by idnumber
| stats list(*) as * by idnumber
| eval time_diff=mvindex(time_diff,1,-1)

That gives you what you want, but will be expensive to do the mvexpand and re-aggregations, but you can measure that.

As to calculating values inside MV fields, if you have Splunk 8 I think, then mvmap may be able to get you to where you want, I couldn't get it to work though - I've not used it before.

[rvsroe]

This worked perfectly, thank you very much, I've only added a strftime to convert epoch into hours and minutes:

 | eval time_diff=strftime(mvindex(time_diff,1,-1),"%H:%M:%S") 

[richgalloway]

If I understand your data correctly, you may be able to use streamstats.

... | streamstats window=2 range('epoch timestamp') as time_diff

[rvsroe]

Thanks for the reply, streamstats was indeed helpful, I've accepted the other reply as answer though for its completeness