topic Summing values inside mvfield in Splunk ITSI

Summing values inside mvfield

rvsroe — Thu, 18 Jun 2020 14:44:56 GMT

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

Re: Summing values inside mvfield

richgalloway — Thu, 18 Jun 2020 15:46:02 GMT

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

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

Re: Summing values inside mvfield

bowesmana — Fri, 19 Jun 2020 07:03:12 GMT

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.

Re: Summing values inside mvfield

rvsroe — Fri, 19 Jun 2020 11:35:09 GMT

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

Re: Summing values inside mvfield

rvsroe — Fri, 19 Jun 2020 11:36:28 GMT

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