Calculate the delta based on the time for 2 fields - end of day value minus start of day value

[HattrickNZ]

I have the following table from my search:

index=core ... |
timechart span=5m
sum(deliverySucceeded) as deliverySucceeded
sum(sentMessage) as sentMessage |

gives this table:

    _time   deliverySucceeded   sentMessage
801 20/01/2018 18:40    262719  329507
… … … …
863 20/01/2018 23:50    264437  331450
864 20/01/2018 23:55    264454  331467
865 21/01/2018 0:00 264464  331477
… … … …

1151    21/01/2018 23:50    271316  339177
1152    21/01/2018 23:55    271328  339189
1153    22/01/2018 0:00 271339  339201
1154    22/01/2018 0:05 271354  339221
1155    22/01/2018 0:10 271365  339232
1156    22/01/2018 0:15 271373  339240

What I want is:(I want it to this for every day in the time range)

_time   deliverySucceeded   sentMessage
21/01/2018  6875    7724

Which are the midnight values subtracted from each other

e.g. 271339 - 264464 = 6875, 339201 - 331477=7724

1153    22/01/2018 0:00 271339  339201
865 21/01/2018 0:00 264464  331477
        6875    7724

Is there a way to do this? Do I have to sue delta or is there a better way?

note: you would think that the max value would always be at midnight but there are instances where this is not the case

these are accumulated values every 5mins. What I want is the daily total of delivery and sent messages.

[mayurr98 · Accepted solution]

I think you want something like this

| makeresults |   eval data = "
  801    20/01/2018 18:40    262719    329507;
  863    20/01/2018 23:50    264437    331450;
  864    20/01/2018 23:55    264454    331467;
  865    21/01/2018 0:00    264464    331477;
  1151    21/01/2018 23:50    271316    339177;
 1152    21/01/2018 23:55    271328    339189;
 1153    22/01/2018 0:00    271339    339201;
 1154    22/01/2018 0:05    271354    339221;
 1155    22/01/2018 0:10    271365    339232;
 1156    22/01/2018 0:15    271373    339240" | makemv delim=";" data | mvexpand data  | rex field=data "(?<serial>[\d]*)\s+(?<_time>\d{2}\/\d{2}\/\d{4}\s+\d{1,2}\:\d{1,2})\s+(?<delivery>\d+)\s+(?<sent>\d+)" | rex field=_time "(?<condition>\d{1,2}:\d{1,2})" | where condition="0:00" OR condition="00:00" | delta sent  | delta delivery

let me know if this helps!

[HattrickNZ]

wow, tks

 | makeresults |   eval data = "
   801    20/01/2018 18:40    262719    329507;
   863    20/01/2018 23:50    264437    331450;
   864    20/01/2018 23:55    264454    331467;
   865    21/01/2018 0:00    264464    331477;
   1151    21/01/2018 23:50    271316    339177;
  1152    21/01/2018 23:55    271328    339189;
  1153    22/01/2018 0:00    271339    339201;
  1154    22/01/2018 0:05    271354    339221;
  1155    22/01/2018 0:10    271365    339232;
  1156    22/01/2018 0:15    271373    339240" | 
  makemv delim=";" data | 
  mvexpand data  | 
  rex field=data "(?<serial>[\d]*)\s+(?<_time>\d{2}\/\d{2}\/\d{4}\s+\d{1,2}\:\d{1,2})\s+(?<delivery>\d+)\s+(?<sent>\d+)" | 
  fields - data serial | 
  rex field=_time "(?<condition>\d{1,2}:\d{1,2})" |
  where condition="0:00" OR condition="00:00" | 
  delta delivery as delivery | 
  delta sent as sent | 
  fields - condition

Tidied it up a bit and get the following:

    _time   delivery    sent
1   21/01/2018 0:00 264464  331477
2   22/01/2018 0:00 6875    7724

But how do I get the values 6875 and 7724 to be in the same _time row as 21/01/2018 0:00??

[mayurr98]

hey, I think it is pretty complex but I found a workaround to do it !

| makeresults 
| eval data = "
   801    20/01/2018 18:40    262719    329507;
   863    20/01/2018 23:50    264437    331450;
   864    20/01/2018 23:55    264454    331467;
   865    21/01/2018 0:00    264464    331477;
   1151    21/01/2018 23:50    271316    339177;
  1152    21/01/2018 23:55    271328    339189;
  1153    22/01/2018 0:00    271339    339201;
  1154    22/01/2018 0:05    271354    339221;
  1155    22/01/2018 0:10    271365    339232;
  1156    22/01/2018 0:15    271373    339240" 
| makemv delim=";" data 
| mvexpand data 
| rex field=data "(?<serial>[\d]*)\s+(?<_time>\d{2}\/\d{2}\/\d{4}\s+\d{1,2}\:\d{1,2})\s+(?<delivery>\d+)\s+(?<sent>\d+)" 
| rex field=_time "(?<condition>\d{1,2}:\d{1,2})" 
| where condition="0:00" OR condition="00:00" 
| delta delivery as delivery_diff 
| delta sent as sent_diff 
| search delivery_diff=* 
| eval _time=strftime((strptime(_time,"%d/%m/%Y %H:%M")-86400),"%d/%m/%Y") 
| fields _time delivery_diff sent_diff

let me know if this helps!

[HattrickNZ]

tks, great thinking.A beautiful mind!

note for my reference: the 2nd last line I have changed as in your example the _time is in a string format, whereas with my real data _time is in date format. tks again.

  | eval _time=relative_time(_time,"-1d")
  | fields + _time delivery_diff sent_diff

[nabeel652]

Try something like this:

  | stats count | eval data = "
 801    20/01/2018 18:40    262719    329507;
 863    20/01/2018 23:50    264437    331450;
 864    20/01/2018 23:55    264454    331467;
 865    21/01/2018 0:00    264464    331477;
 1151    21/01/2018 23:50    271316    339177;
1152    21/01/2018 23:55    271328    339189;
1153    22/01/2018 0:00    271339    339201;
1154    22/01/2018 0:05    271354    339221;
1155    22/01/2018 0:10    271365    339232;
1156    22/01/2018 0:15    271373    339240" | makemv delim=";" data | mvexpand data | rex field=data "(?<serial>[\d]*)\s+(?<time>\d{2}\/\d{2}\/\d{4}\s+\d{1,2}\:\d{1,2})\s+(?<delivery>\d+)\s+(?<sent>\d+)"  | eval _time=strptime(time,"%d/%m/%Y %H:%M") | table serial time _time delivery, sent | timechart span=1d min(serial) as serial min(delivery) as delivered min(sent) as sent | eval result=sent - delivered | where isnotnull(result)

Or something like:

...| bin span=1d _time | stats  min(_time) as time  min(delivery) as delivered min(sent) as sent by  _time  | eval result=sent - delivered | eval time =strftime(time,"%d/%m/%Y %H:%M")