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.
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.
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.
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?
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.
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?
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.
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
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
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.
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?
Hi @bowesmana, yeah I edited my original post. Sorry to confuse you..
Hi @manidandu
Does your transaction_id change between 1 & 3rd ? they shall be common to find that difference. Any other common field?
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