Dashboards & Visualizations

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

Explorer

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.

Labels (1)
• other

Tags (5)
1 Solution
Champion

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.

Champion

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.

Explorer

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?

Champion

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.

Explorer

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?

Tags (1)
Champion

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.

SplunkTrust

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"
| eval _time=strptime(time, "%F %T.%Q")
| table _time Direction Transaction_ID
| streamstats range(_time) as diff by Direction``````

Explorer

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

SplunkTrust

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.

Explorer

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?

Explorer

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

SplunkTrust

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

Explorer

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

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...