Dashboards & Visualizations

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

manidandu
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)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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.

0 Karma

manidandu
Explorer

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? 

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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.

0 Karma

manidandu
Explorer

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? 

Tags (1)
0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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.

0 Karma

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

 

0 Karma

manidandu
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

0 Karma

bowesmana
SplunkTrust
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.

 

0 Karma

manidandu
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?

0 Karma

manidandu
Explorer

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

0 Karma

venkatasri
SplunkTrust
SplunkTrust

Hi @manidandu 

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

0 Karma

manidandu
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

0 Karma
Get Updates on the Splunk Community!

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...

Want to Reduce Costs, Mitigate Risk, Improve Performance, or Increase Efficiencies? ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...