difference between specific field from two seperate events with time difference of 28sec

[sawgata12345]

Hi,
I have a following data:

_time                 rtIO     sid      combination                           diffval
2018-06-13 13:30:59 7497707 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7543374 <-row with 0x8b0122           
2018-06-13 13:30:59 7497757 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   50      <-row with 0x8b0120
2018-06-13 13:30:59 175999  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7321758
2018-06-13 13:30:59 334641  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   158642
2018-06-13 13:30:59 333123  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1518
2018-06-13 13:30:59 176163  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   156960

2018-06-13 13:31:27 7583623 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7543374  <-row with 0x8b0122
2018-06-13 13:31:27 7583682 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   59       <-row with 0x8b0120
2018-06-13 13:31:27 176183  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7407499
2018-06-13 13:31:27 334954  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   158771
2018-06-13 13:31:27 333477  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1477
2018-06-13 13:31:27 176332  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157145

2018-06-13 13:31:56 7669908 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7629658  <-row with 0x8b0122
2018-06-13 13:31:56 7669971 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   63       <-row with 0x8b0120
2018-06-13 13:31:56 176338  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7493633
2018-06-13 13:31:56 335299  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   158961
2018-06-13 13:31:56 333828  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1471
2018-06-13 13:31:56 176486  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157342

2018-06-13 13:32:24 7755837 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7715575 <-row with 0x8b0122
2018-06-13 13:32:24 7755910 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   73      <-row with 0x8b0120
2018-06-13 13:32:24 176492  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7579418
2018-06-13 13:32:24 335660  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   159168
2018-06-13 13:32:24 334189  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1471
2018-06-13 13:32:24 176626  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157563

2018-06-13 13:32:52 7841663 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7801388 <-row with 0x8b0122
2018-06-13 13:32:52 7841674 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   11      <-row with 0x8b0120
2018-06-13 13:32:52 176751  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7664923
2018-06-13 13:32:52 336023  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   159272
2018-06-13 13:32:52 334526  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1497
2018-06-13 13:32:52 176910  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157616

In the above rows, each bunch of rows occured at a same time. I need the difference of 2nd column for rows marked <-row with 0x8b0122 as above with time diff of 28 sec.Similarly for <-row with 0x8b0120. Here the number of rows are 6 but it may differ.

I used a query below but need to specify p=6 with delta.

index="test101" |fields sid,did,lun,rtIO,rtIOt|sort _time |eval combination=sid."-".did."-".lun|delta rtIO as diffval p=6|delta _time as timediff|eval diffval=abs(diffval) |table _time,rtIOt,sid,combination,diffval

Instead of using p=6 as above how to get the difference of rows with time diff of 28sec for the unique values for column combination?
Also I need to display the diffval with time in a chart, instead of the table command above.

[woodcock]

I do not see any way to do this but for anybody interested in trying, here is a way to generate the sample events:

| makeresults 
| eval raw="2018-06-13 13:30:59 7497707 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000 7543374
2018-06-13 13:30:59 7497757 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   50
2018-06-13 13:30:59 175999  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7321758
2018-06-13 13:30:59 334641  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   158642
2018-06-13 13:30:59 333123  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1518
2018-06-13 13:30:59 176163  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   156960
2018-06-13 13:31:27 7583623 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7543374
2018-06-13 13:31:27 7583682 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   59
2018-06-13 13:31:27 176183  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7407499
2018-06-13 13:31:27 334954  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   158771
2018-06-13 13:31:27 333477  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1477
2018-06-13 13:31:27 176332  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157145
2018-06-13 13:31:56 7669908 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7629658
2018-06-13 13:31:56 7669971 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   63
2018-06-13 13:31:56 176338  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7493633
2018-06-13 13:31:56 335299  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   158961
2018-06-13 13:31:56 333828  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1471
2018-06-13 13:31:56 176486  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157342
2018-06-13 13:32:24 7755837 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7715575
2018-06-13 13:32:24 7755910 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   73
2018-06-13 13:32:24 176492  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7579418
2018-06-13 13:32:24 335660  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   159168
2018-06-13 13:32:24 334189  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1471
2018-06-13 13:32:24 176626  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157563
2018-06-13 13:32:52 7841663 0x8b0122    0x8b0122-0xef0101-0000-0000-0000-0000   7801388
2018-06-13 13:32:52 7841674 0x8b0120    0x8b0120-0xef0081-0000-0000-0000-0000   11
2018-06-13 13:32:52 176751  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   7664923
2018-06-13 13:32:52 336023  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   159272
2018-06-13 13:32:52 334526  0x8b0081    0x8b0081-0xef00ef-0001-0000-0000-0000   1497
2018-06-13 13:32:52 176910  0x8b0081    0x8b0081-0xef00ef-0000-0000-0000-0000   157616" 
| makemv raw delim="
" 
| mvexpand raw 
| rename raw AS _raw 
| rex "^(?<time>\S+\s\S+)\s+(?<rtIO>\S+)\s+(?<sid>\S+)\s+(?<combination>\S+)\s+(?<diffval>\S+)$" 
| eval _time=strptime(time, "%Y-%m-%d %H:%M:%S") 
| fields - _raw 

[DalJeanis]

Try something like this

(your search that gets the records you want, including unique sid that links the multiple records)
| rex field=_raw "(?<CareAboutFlag>0x8b0122|0x8b0122>)"
| where isnotnull(CareAboutFlag)
| stats min(_time) as _time 
    max(_time) as end_time 
    range(_time) as duration 
    list(_raw) as stufftokeepjustincase 
    by sid 

If you need the stuff from the other records, then do this

(your search that gets the records you want, including unique sid that links the multiple records)
| rex field=_raw "(?<CareAboutFlag>0x8b0122|0x8b0122>)"
| stats min(eval(case(isnotnull(CareAboutFlag),_time))) as _time 
    max(eval(case(isnotnull(CareAboutFlag),_time))) as end_time 
    range(eval(case(isnotnull(CareAboutFlag),_time))) as duration 
    list(_time) as timestokeepjustincase 
    list(_raw) as stufftokeepjustincase 
    by sid 

[renjith_nair]

Hi @sawgata12345,

If you are always looking for difference between same SID and only for two rowa, then try this

your search | fields _time rtIO sid combination
|eventstats list(rtIO) as rtioList by combination|eval difference=abs(tonumber(mvindex(rtioList,0))-tonumber(mvindex(rtioList,1)))

[sawgata12345]

hi,
Thanks renjith.

I need the diff between first row
2018-06-13 13:30:59 7497707 0x8b0122 0x8b0122-0xef0101-0000-0000-0000-0000 >>row with 0x8b0122

and seventh row
2018-06-13 13:31:27 7583623 0x8b0122 0x8b0122-0xef0101-0000-0000-0000-0000 7543374 >>row with 0x8b0122

then 7th row
2018-06-13 13:31:27 7583623 0x8b0122 0x8b0122-0xef0101-0000-0000-0000-0000 7543374 >>row with 0x8b0122
and 13th row
2018-06-13 13:31:56 7669908 0x8b0122 0x8b0122-0xef0101-0000-0000-0000-0000 7629658 >>row with 0x8b0122

then 19th row - 13throw
2018-06-13 13:32:24 7755837 0x8b0122 0x8b0122-0xef0101-0000-0000-0000-0000 7715575 >>row with 0x8b0122
2018-06-13 13:31:56 7669908 0x8b0122 0x8b0122-0xef0101-0000-0000-0000-0000 7629658 >>row with 0x8b0122

similarly for the 2nd row and 8th row
2018-06-13 13:30:59 7497757 0x8b0120 0x8b0120-0xef0081-0000-0000-0000-0000 50 >>row with 0x8b0120
2018-06-13 13:31:27 7583682 0x8b0120 0x8b0120-0xef0081-0000-0000-0000-0000 59 >>row with 0x8b0120

then 8th row and 14th row
2018-06-13 13:31:27 7583682 0x8b0120 0x8b0120-0xef0081-0000-0000-0000-0000 59 >>row with 0x8b0120
2018-06-13 13:31:56 7669971 0x8b0120 0x8b0120-0xef0081-0000-0000-0000-0000 63 >>row with 0x8b0120

Similarly
9throw -3rd row
15th - 9th row

I combined my query with yours ,i think 'delta rtIO as diffval' is not required from my query as you have used "eventstats" function

index="test101" |fields sid,did,lun,rtIO,rtIOt|sort _time |eval combination=sid."-".did."-".lun| fields _time rtIO sid combination|eventstats list(rtIO) as rtioList by combination|eval difference=abs(tonumber(mvindex(rtioList,0))-tonumber(mvindex(rtioList,1))) |table _time,sid,did,combination,,difference

The output is having in "difference" column always the diff of the 7th -1st (7583623 -7497707 ), even when the "difference" should contain values of 13th-7th(7669908 - 7583623 ) row/19th -13th(7755837 -7669908 ) row etc

The output from running the combined query is below. This having the fixed difference repeated for each time interval of 28 seconds.

Blockquote
2018-06-13 13:30:59 0x8b0122 0xef0101 0000-0000-0000-0000 0x8b0122-0xef0101-0000-0000-0000-0000 85916
2018-06-13 13:30:59 0x8b0120 0xef0081 0000-0000-0000-0000 0x8b0120-0xef0081-0000-0000-0000-0000 85925
2018-06-13 13:30:59 0x8b0081 0xef00ef 0000-0000-0000-0000 0x8b0081-0xef00ef-0000-0000-0000-0000 164
2018-06-13 13:30:59 0x8b0081 0xef00ef 0001-0000-0000-0000 0x8b0081-0xef00ef-0001-0000-0000-0000 1518
2018-06-13 13:30:59 0x8b0081 0xef00ef 0001-0000-0000-0000 0x8b0081-0xef00ef-0001-0000-0000-0000 1518
2018-06-13 13:30:59 0x8b0081 0xef00ef 0000-0000-0000-0000 0x8b0081-0xef00ef-0000-0000-0000-0000 164

2018-06-13 13:31:27 0x8b0122 0xef0101 0000-0000-0000-0000 0x8b0122-0xef0101-0000-0000-0000-0000 85916
2018-06-13 13:31:27 0x8b0120 0xef0081 0000-0000-0000-0000 0x8b0120-0xef0081-0000-0000-0000-0000 85925
2018-06-13 13:31:27 0x8b0081 0xef00ef 0000-0000-0000-0000 0x8b0081-0xef00ef-0000-0000-0000-0000 164
2018-06-13 13:31:27 0x8b0081 0xef00ef 0001-0000-0000-0000 0x8b0081-0xef00ef-0001-0000-0000-0000 1518
2018-06-13 13:31:27 0x8b0081 0xef00ef 0001-0000-0000-0000 0x8b0081-0xef00ef-0001-0000-0000-0000 1518
2018-06-13 13:31:27 0x8b0081 0xef00ef 0000-0000-0000-0000 0x8b0081-0xef00ef-0000-0000-0000-0000 164

2018-06-13 13:31:56 0x8b0122 0xef0101 0000-0000-0000-0000 0x8b0122-0xef0101-0000-0000-0000-0000 85916
2018-06-13 13:31:56 0x8b0120 0xef0081 0000-0000-0000-0000 0x8b0120-0xef0081-0000-0000-0000-0000 85925
2018-06-13 13:31:56 0x8b0081 0xef00ef 0000-0000-0000-0000 0x8b0081-0xef00ef-0000-0000-0000-0000 164
2018-06-13 13:31:56 0x8b0081 0xef00ef 0001-0000-0000-0000 0x8b0081-0xef00ef-0001-0000-0000-0000 1518
2018-06-13 13:31:56 0x8b0081 0xef00ef 0001-0000-0000-0000 0x8b0081-0xef00ef-0001-0000-0000-0000 1518
2018-06-13 13:31:56 0x8b0081 0xef00ef 0000-0000-0000-0000 0x8b0081-0xef00ef-0000-0000-0000-0000 164

Blockquote

[renjith_nair]

Hi @sawgata12345,

Ok so its not just between 1st and 7th and 2nd and 8th. Do you have any unique field to distinguish between each row? For e.g. from your data, if we take 4th and 5th rows from first section, there is nothing which could distinguish those rows and if you sort by _time it might not be correct because all the rows in a section have same time

[sawgata12345]

Hi,
the time part in the output is fixed now from device end, Now the output is coming like this. For a bunch of rows with
same time combination column is different for each row.

_time combination samStm samEtm rtIO timediff

2018-06-23 13:30:51 0x8b00e0-0xef0102-0007-0000-0000-0000 1529740851 1529740880 80726 29 >> this row 1

2018-06-23 13:30:51 0x8b00e0-0xef0102-0006-0000-0000-0000 1529740851 1529740880 80743 29 >> this row 2
2018-06-23 13:30:51 0x8b00e0-0xef0102-0004-0000-0000-0000 1529740851 1529740880 80873 29 >> this row 3
2018-06-23 13:30:51 0x8b00e0-0xef0102-0003-0000-0000-0000 1529740851 1529740880 80869 29 >> this row 4
2018-06-23 13:30:51 0x8b00e0-0xef0102-0002-0000-0000-0000 1529740851 1529740980 81285 28
....5 more different unique combinations between then
2018-06-23 13:31:20 0x8b00e0-0xef0102-0007-0000-0000-0000 1529740880 1529740908 81158 28 >> this row 1 repeated after 9 rows

2018-06-23 13:31:20 0x8b00e0-0xef0102-0006-0000-0000-0000 1529740880 1529740908 81172 28 >> this row 2 repeated after 9 rows
2018-06-23 13:31:20 0x8b00e0-0xef0102-0004-0000-0000-0000 1529740880 1529740908 81298 28 >> this row 3 repeated after 9 rows
2018-06-23 13:31:20 0x8b00e0-0xef0102-0003-0000-0000-0000 1529740880 1529740908 81293 28 >> this row 4 repeated after 9 rows
2018-06-23 13:31:20 0x8b00e0-0xef0102-0002-0000-0000-0000 1529740880 1529740908 81285 28 >> this row 5 repeated after 9 rows
... 2 more different unique combinations

2018-06-23 13:31:48 0x8b00e0-0xef0102-0007-0000-0000-0000 1529740908 1529740916 81281 28 >> this row 1 repeated after 6 rows
2018-06-23 13:31:48 0x8b00e0-0xef0102-0006-0000-0000-0000 1529740908 1529740916 81293 28 >> this row 2 repeated after 6 rows
2018-06-23 13:31:48 0x8b00e0-0xef0102-0004-0000-0000-0000 1529740908 1529740916 81417 28 >> this row 3 repeated after 6 rows

2018-06-23 13:32:26 0x8b00e0-0xef0102-0007-0000-0000-0000 1529740946 1529740965 81562 19 >> this row 1 repeated after 2 rows
2018-06-23 13:32:26 0x8b00e0-0xef0102-0006-0000-0000-0000 1529740946 1529740965 81572 19 >> this row 2 repeated after 2 rows
2018-06-23 13:32:26 0x8b00e0-0xef0102-0004-0000-0000-0000 1529740946 1529740965 81695 19 >> this row 3 repeated after 2 rows
After this the row 4 and row 5 is not sending any data.

index=test101 |sort _time|fields sid,did,lun,rtIO,wtIO,samEtm,samStm|fields _time,rt,IO,wtIO, samEtm,samStm, sid, did, lun|eval combination=sid."-".did."-".lun|
delta rtIO as rtiodiff p=2| eval timediff=samEtm-samStm|eval res=rtiodiff/timediff |table _time,combination,samStm,samEtm,rtIO,timediff

I am using the query above but the part p=2 in (delta rtIO as rtiodiff p=2) is fixed. how to put a random value for this 'p'.
The column header 'combination' is unique for each row. This unique combination for row 1 repeats after 9 rows first time, then again after 6 rows, then after 2 rows.
Similarly row 2 repeats first after 9 rows then after 6 rows then after 2 rows.

How to find the number of rows between the unique repeatation of row 1, row 2 etc. this needs to substitured in place of p=2 in delta part of the query.
First time for row1 should be 'delta rtIO as rtiodiff p= 9', 2nd time 'delta rtIO as rtiodiff p=6', and third time 'delta rtIO as rtiodiff p=2'.

This will not be always fixed. Later 100 rows may come between a unique repetition of row 1 again,sometimes only 5 rows after which row 1 comes again.
That's why i need to know how many rows occured between row1 again appearing so we can put this count in place of p=2