How to calculate time difference between first, fourth event and second, third event?

[manidandu]

Hi All,

I am trying to create a dashboard with response time between the transactions.  For example, let's i have data output as below:

_time                                                  Direction                ABCD                             Transaction_ID      

2021-07-13 18:56:58.487            in                abcd.008.001.08                      123456789

2021-07-13 18:56:58.603          out               abcd.008.001.08                     123456789

2021-07-13 18:56:59.981            in                abcd.002.001.10                     123456789

2021-07-13 18:57:00.062          out               abcd.002.001.10                      123456789

2021-07-13 18:57:00.565          out               abcd.002.001.10                     123456789

From above output I would like to calculate time difference between first and fourth event (i.e 2021-07-13 18:57:00.062 - 2021-07-13 18:56:58.487) and time difference between second and third event ( i.e 2021-07-13 18:56:59.981 - 2021-07-13 18:56:58.603).

Can someone help me with this query. Highly appreciate your help in this context.

[manjunathmeti]

hi @manidandu,

You can use the delta command to find differences. Try this,

 index=abc sourcetype=def | table _time direction transaction_id | delta p=1 _time as p1 | delta p=3 _time as p3 | streamstats count by transaction_id | where count IN(3, 4) | eval diff_2_3=if(count=3, p1, ""), diff_4_1=if(count=4, p3, "") | fields - count, p1, p3 | stats latest(_time) as _time, max(*) as * by transaction_id

If this reply helps you, a like would be appreciated.

[manidandu]

Hi @manjunathmeti ,

Thanks for your time.  I combined both commands and it worked when i try for one transaction id's:

index=abc sourcetype=def transaction_id=123456789
| stats count by _time, direction, pacs, transaction_id
| streamstats range(_time) as TotalDiff
| streamstats window=2 range(_time) as SingleDiff
| streamstats count by transaction_id
| where count IN(3, 4)
| eval diff_2_3=if(count=3, SingleDiff, ""), diff_4_1=if(count=4, TotalDiff, "")
| fields - count SingleDiff, TotalDiff
| stats latest(_time) as _time max(*) as * by transaction_id  

For single transaction_id above command is working as expected But when I give transaction_id=*, I am getting wrong difference.. Any suggestions how to fix it? 

[manjunathmeti · Accepted solution]

hi @manidandu,

My query works with sample data, check this:

| makeresults | eval _raw="time, direction,transaction_id,count
2021-08-04 06:18:19.178,in,123456789,1             
2021-08-04 06:18:19.230,out,123456789,1             
2021-08-04 06:18:34.525,in,123456789,1            
2021-08-04 06:18:34.546,out,123456789,1            
2021-08-04 06:18:48.005,out,123456789,1"  | multikv forceheader=1 | eval _time=strptime(time, "%F %T.%Q") | delta p=1 _time as p1 | delta p=3 _time as p3 | streamstats count by transaction_id | where count IN(3, 4) | eval SingleDiff=if(count=3, p1, ""), TotalDiff=if(count=4, p3, "") | fields - count, p1, p3 | stats latest(_time) as _time, max(*) as * by transaction_id

I've modified your query too. It also works but it has lot of stats so you can go for above one.

| makeresults | eval _raw="time, direction,transaction_id,count
2021-08-04 06:18:19.178,in,123456789,1             
2021-08-04 06:18:19.230,out,123456789,1             
2021-08-04 06:18:34.525,in,123456789,1            
2021-08-04 06:18:34.546,out,123456789,1            
2021-08-04 06:18:48.005,out,123456789,1"  | multikv forceheader=1 | eval _time=strptime(time, "%F %T.%Q")
| stats count by _time, direction, transaction_id
| streamstats range(_time) as TotalDiff
| streamstats window=2 range(_time) as SingleDiff | streamstats count by transaction_id | where count IN(3, 4) | eval SingleDiff=if(count=3, SingleDiff, ""), TotalDiff=if(count=4, TotalDiff, "") | fields - count | stats latest(_time) as _time, max(*) as * by transaction_id

If this reply helps you, a like would be appreciated.

[manidandu]

Hi @manjunathmeti ,

For single transaction_id above commands are working as expected But when I give transaction_id=*, I am getting wrong difference.. Any suggestions how to fix it? 

[manjunathmeti]

You should get correct results:

| makeresults | eval _raw="time, direction,transaction_id,count
2021-08-04 06:18:19.178,in,123456789,1             
2021-08-04 06:18:19.230,out,123456789,1             
2021-08-04 06:18:34.525,in,123456789,1            
2021-08-04 06:18:34.546,out,123456789,1            
2021-08-04 06:18:48.005,out,123456789,1
2021-08-04 06:18:19.178,in,1234567891,1             
2021-08-04 06:18:19.230,out,1234567891,1             
2021-08-04 06:18:34.525,in,1234567891,1            
2021-08-04 06:18:34.546,out,1234567891,1    
2021-08-04 06:18:19.178,in,1234567892,1             
2021-08-04 06:18:19.230,out,1234567892,1             
2021-08-04 06:18:34.525,in,1234567892,1            
2021-08-04 06:18:34.546,out,1234567892,1            
2021-08-04 06:18:48.005,out,1234567892,1"  | multikv forceheader=1 | eval _time=strptime(time, "%F %T.%Q") | delta p=1 _time as p1 | delta p=3 _time as p3 | streamstats count by transaction_id | where count IN(3, 4) | eval SingleDiff=if(count=3, p1, ""), TotalDiff=if(count=4, p3, "") | fields - count, p1, p3 | stats latest(_time) as _time, max(*) as * by transaction_id

Try sorting _time.

[bowesmana]

This will give you the results

| streamstats range(_time) as diff by Direction

but I expect it's not a complete solution as it will not work as you have more data or, as you see with row 5, a valid result

| makeresults
| eval _raw="_time                          Direction              Transaction_ID      
2021-07-13 18:56:58.487            in                            123456789
2021-07-13 18:56:58.603          out                            243613643
2021-07-13 18:56:59.981            in                             254271826
2021-07-13 18:57:00.062          out                           886352737
2021-07-13 18:57:00.565          out                           472734724
2021-07-13 18:57:00.999          in                           886362737"
| multikv forceheader=1
| eval _time=strptime(time, "%F %T.%Q")
| table _time Direction Transaction_ID
| streamstats range(_time) as diff by Direction

[manidandu]

Thanks for your time @bowesmana. What if my data looks like below:

_time                                                  Direction                ABCD                              Transaction_ID      

2021-07-13 18:56:58.487            in                abcd.008.001.08                     123456789

2021-07-13 18:56:58.603          out               abcd.008.001.08                     123456789

2021-07-13 18:56:59.981            in                abcd.002.001.10                     123456789

2021-07-13 18:57:00.062          out               abcd.002.001.10                     123456789

2021-07-13 18:57:00.565          out               abcd.002.001.10                    123456789

I want to calculate time difference between (1st and 4th event), (2nd and 3rd event). I don't want 5th event in the results.

                                   _time                                                         direction                     ABCD                                Transaction_ID

First event :         2021-07-13 18:56:58.487                      in                   abcd.008.001.08                   123456789

Second Event:   2021-07-13 18:56:58.603                     out                 abcd.008.001.08                   123456789

Third event:         2021-07-13 18:56:59.981                     in                   abcd.002.001.10                     123456789

Fourth event:     2021-07-13 18:57:00.062                     out                abcd.002.001.10                     123456789

Fifth event:        2021-07-13 18:57:00.565                      out                abcd.002.001.10                    123456789

[bowesmana]

That's strange - did you edit your original post?  I believe your original post said 1,3 and 2,4

However, if you want to calculate between 1,4 and 2,3 then this will do it

| streamstats range(_time) as TotalDiff
| streamstats window=2 range(_time) as SingleDiff

where TotalDiff in the 4th entry is the difference to the first entry and SingleDiff in the 3rd entry is the difference to the second.

[manidandu]

Thanks for your help @bowesmana. I want only TotalDiff in the 4th entry and SingleDiff in the 3rd entry in the table. I don't want anyother values.

For example, I have output as below:

_time                                                   direction           transaction_id     count        SingleDiff       TotalDiff
2021-08-04 06:18:19.178               in                     123456789             1             0.000000      0.000000
2021-08-04 06:18:19.230             out                    123456789             1             0.052000      0.052000
2021-08-04 06:18:34.525             out                    123456789             1            15.295000    15.347000
2021-08-04 06:18:34.546             out                    123456789             1            0.021000       15.368000
2021-08-04 06:18:48.005              in                       123456789            1           13.459000       28.827000

Instead of above output I want output as below:

transaction_id      SingleDiff       TotalDiff
123456789           15.295000    15.368000

Below is the search I am using:

index=abc sourcetype=def transaction_id=123456789
| stats count by _time, direction, transaction_id
| streamstats range(_time) as TotalDiff
| streamstats window=2 range(_time) as SingleDiff

Any suggestions?

[manidandu]

Hi @bowesmana, yeah I edited my original post. Sorry to confuse you..

[venkatasri]

Hi @manidandu 

Does your transaction_id change between 1 & 3rd ? they shall be common to find that difference. Any other common field?

[manidandu]

Hi @venkatasri ,

No.. My Transaction_Id is same for all five events and my data looks like below:

_time                                                  Direction                ABCD                              Transaction_ID      

2021-07-13 18:56:58.487            in                abcd.008.001.08                     123456789

2021-07-13 18:56:58.603          out               abcd.008.001.08                     123456789

2021-07-13 18:56:59.981            in                abcd.002.001.10                     123456789

2021-07-13 18:57:00.062          out               abcd.002.001.10                     123456789

2021-07-13 18:57:00.565          out               abcd.002.001.10                    123456789

I want to calculate time difference between (1st and 4th event), (2nd and 3rd event). I don't want 5th event in the results.

                                   _time                                                         direction                     ABCD                                Transaction_ID

First event :         2021-07-13 18:56:58.487                      in                   abcd.008.001.08                   123456789

Second Event:   2021-07-13 18:56:58.603                     out                 abcd.008.001.08                   123456789

Third event:         2021-07-13 18:56:59.981                     in                   abcd.002.001.10                     123456789

Fourth event:     2021-07-13 18:57:00.062                     out                abcd.002.001.10                     123456789

Fifth event:        2021-07-13 18:57:00.565                      out                abcd.002.001.10                    123456789