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

rparadinha — Thu, 30 Aug 2018 16:13:18 GMT

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.

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

DalJeanis — Fri, 31 Aug 2018 02:46:16 GMT

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.

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

rparadinha — Fri, 31 Aug 2018 09:45:10 GMT

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

topic Re: How do you calculate average time between transaction groups by two fields? in Splunk Search

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

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

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