How do you calculate average time between transaction groups by two fields?

[rparadinha]

I have logs from a SIP proxy server and I'm trying to get metrics from SIP transactions metrics from a SIP proxy server logs.
I have the following events:

Peer AAA events:

Time, call id A,  message A.1,  peer_name "AAA", resource "111"
Time, call id A,  message A.2,  peer_name "AAA", resource "111"
Time, call id A,  message A.3,  peer_name "AAA", resource "111"

Time, call id C,  message C.1,  peer_name "AAA", resource "112"
Time, call id C,  message C.2,  peer_name "AAA", resource "112"
Time, call id C,  message C.3,  peer_name "AAA", resource "112"

Time, call id I,  message I.1,  peer_name "AAA", resource "111"
Time, call id I,  message I.2,  peer_name "AAA", resource "111"
Time, call id I,  message I.3,  peer_name "AAA", resource "111"

Time, call id J,  message J.1,  peer_name "AAA", resource "112"
Time, call id J,  message J.2,  peer_name "AAA", resource "112"
Time, call id J,  message J.3,  peer_name "AAA", resource "112"

(...)

---

Peer BBB events:

Time, call id B,  message B.1,  peer_name "BBB", resource "111"
Time, call id B,  message B.2,  peer_name "BBB", resource "111"
Time, call id B,  message B.3,  peer_name "BBB", resource "111"

Time, call id D,  message D.1,  peer_name "BBB", resource "112"
Time, call id D,  message D.2,  peer_name "BBB", resource "112"
Time, call id D,  message D.3,  peer_name "BBB", resource "112"

Time, call id F,  message F.1,  peer_name "BBB", resource "111"
Time, call id F,  message F.2,  peer_name "BBB", resource "111"
Time, call id F,  message F.3,  peer_name "BBB", resource "111"

(...)

---

Peer CCC events:

Time, call id E,  message E.1,  peer_name "CCC", resource "113"
Time, call id E,  message E.2,  peer_name "CCC", resource "113"
Time, call id E,  message E.3,  peer_name "CCC", resource "113"

Time, call id G,  message G.1,  peer_name "CCC", resource "114"
Time, call id G, message G.2,  peer_name "CCC", resource "114"
Time, call id G, message G.3,  peer_name "CCC", resource "114"

Time, call id H,  message H.1,  peer_name "CCC", resource "113"
Time, call id H,  message H.2,  peer_name "CCC", resource "113"
Time, call id H,  message H.3,  peer_name "CCC", resource "113"

(...)

---

Notes:
- All peer can have N resources.
- Different peers can have the same name resource
- Exists N different peers.
- In the timeline, messages from different peers may be mixed.

Order in Timeline (only show AAA and BBB messages to simplify):

1. Time, call id A,  message A.1,  peer_name "AAA", resource "111"
2. Time, call id B,  message B.1,  peer_name "BBB", resource "111"
3. Time, call id C,  message C.1,  peer_name "AAA", resource "112"
4. Time, call id A,  message A.2,  peer_name "AAA", resource "111"
5. 7. Time, call id A,  message A.3,  peer_name "AAA", resource "111"
6. Time, call id D,  message D.1,  peer_name "BBB", resource "112"
7. Time, call id I,  message I.1,  peer_name "AAA", resource "111"
8. Time, call id B,  message B.2,  peer_name "BBB", resource "111"
9. Time, call id I,  message I.2,  peer_name "AAA", resource "111"
10. Time, call id C,  message C.2,  peer_name "AAA", resource "112"
11. Time, call id C,  message C.3,  peer_name "AAA", resource "112"
12. Time, call id J,  message J.1,  peer_name "AAA", resource "112"
13. Time, call id B,  message B.3,  peer_name "BBB", resource "111"
14. 4. Time, call id F,  message F.1,  peer_name "BBB", resource "111"
15. Time, call id F,  message F.2,  peer_name "BBB", resource "111"
16. Time, call id I,  message I.3,  peer_name "AAA", resource "111"
17. Time, call id J,  message J.2,  peer_name "AAA", resource "112"
18. Time, call id D,  message D.2,  peer_name "BBB", resource "112"
19. Time, call id D,  message D.3,  peer_name "BBB", resource "112"
20. Time, call id J,  message J.3,  peer_name "AAA", resource "112"

My goal is to know the average time between transactions from the same peer / resource.
Peer AAA and resource 111:
- Call id A, peer AAA, resource 111
- Call id I, peer AAA, resource 111
- Call id ..., peer AAA, resource 111

Peer AAA and resource 112:
- Call id C, peer AAA, resource 112
- Call id J, peer AAA, resource 112
- Call id ..., peer AAA, resource 112

Peer BBB and resource 112:
- Call id B, peer BBB, resource 111
- Call id F, peer BBB, resource 111
(...)

At the end I would like to get a table with:
|| Peer || Resource || Avg (time) bettween different transactions) ||
|| AAA || 111 || 2s ||
|| AAA || 112 || 3,5s ||
|| BBB || 111 || 1s ||
|| BBB || 112 || 5s . ||
|| CCC || 113 || 1s ||
|| CCC || 114 || 5s . ||

I created a query that give almost what I want but only if I limit to a specific peer and resource. Otherwise the query does not pay attention to transactions per peer and resource and calculates the difference between all transactions.

index="index" sourcetype="sourcetype" ("SUBSCRIBE" OR "NOTIFY")
| transaction call_id maxspan=3s
| eval success=if(searchmatch("404"),1,0)
| where success=1
| <extract peer_name>
| extract resource>
| where peer_name="ABC"
| where resource="123"
| eval initial_time=_time
| autoregress _time AS previous_time 
| delta previous_time AS difference
| chart avg(difference) AS ratio BY peer_name resource

|| field1 || flied 2 || avg time ||
| ABC | 123 | -5.031163865546219 |

Any ideas?
Using Splunk 7.0.3.4 version.

Thanks in advance.

[DalJeanis · Accepted solution]

Try something like this...

 index="index" sourcetype="sourcetype" ("SUBSCRIBE" OR "NOTIFY")
| rename COMMENT as "sort into ascending _time order"
| sort 0 _time 

| rename COMMENT as "copy down the prior _time value for the same peer_name and resource."
| streamstats current=f last(_time) as prevtime by peer_name resource

| rename COMMENT as "calculate the difference, then calculate the average difference."
| eval stepduration = _time - prevtime
| stats avg(stepduration) as ratio by peer_name resource

---

You might also do time trials using this sort instead and see if it makes it faster or slower. I would bet on marginally faster, but the result can be highly data dependent.

| rename COMMENT as "sort into ascending _time order"
| sort 0 _time peer_name resource

| rename COMMENT as "copy down the prior _time value for the same peer_name and resource."
| streamstats reset_on_change=t current=f last(_time) as prevtime by peer_name resource

| rename COMMENT as "calculate the difference, then calculate the average difference."
| eval stepduration = _time - prevtime
| stats avg(stepduration) as ratio by peer_name resource

---

In both cases above, the first record of each set will have prevtime as null, thus there will be no difference to calculate. The average will thus be correct.

[rparadinha]

@DalJeanis It was exactly what I was looking for.
Thank you.