topic Re: How to calculate time difference between first, third event and second, fourth event? in Dashboards & Visualizations

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

manidandu — Wed, 14 Jul 2021 12:56:35 GMT

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.

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

venkatasri — Wed, 14 Jul 2021 00:52:17 GMT

Hi @manidandu

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

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

bowesmana — Wed, 14 Jul 2021 03:25:21 GMT

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

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

manidandu — Wed, 14 Jul 2021 13:01:11 GMT

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

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

manidandu — Wed, 14 Jul 2021 12:58:48 GMT

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

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

bowesmana — Thu, 15 Jul 2021 07:19:25 GMT

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.

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

manidandu — Fri, 16 Jul 2021 13:10:26 GMT

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

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

manidandu — Wed, 04 Aug 2021 14:48:58 GMT

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?

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

manjunathmeti — Wed, 04 Aug 2021 17:54:31 GMT

hi @manidandu,

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

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

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

manidandu — Thu, 05 Aug 2021 13:50:48 GMT

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?

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

manjunathmeti — Thu, 05 Aug 2021 04:48:44 GMT

hi @manidandu,

My query works with sample data, check this:

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.

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

manidandu — Thu, 05 Aug 2021 16:32:28 GMT

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?

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

manjunathmeti — Fri, 06 Aug 2021 07:20:03 GMT

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.