calculate top talkers by application

[jalfrey]

I would like to calculate the top talkers by application (name/ID) but I have run into a snag. The firewall returns "connection closed" messages which include the sent/received bytes but it does not include the application ID. It also sends other log messages that have the ID but no bytes sent. The common fields are src and dst. Here are some example logs.

Jul 1 13:40:44 10.203.14.34 id=firewall sn=0017C598FCCC time="2013-07-01 13:40:44" fw=10.203.14.34 pri=6 c=1024 m=537 msg="Connection Closed" n=24486 usr="admin" src=10.50.193.54:42521:X1 dst=10.203.14.34:80:X1 proto=tcp/http sent=846 rcvd=694 spkt=6 rpkt=5 cdur=2100

key fields:
- msg="Connection Closed"
- src=10.50.193.54:42521:X1
- dst=10.203.14.34:80:X1

Jul 1 13:03:53 10.197.1.254 id=firewall sn=0017C5C027C1 time="2013-07-01 20:03:53 UTC" bid=1 fw=67.115.118.49 pri=1 c=0 m=1154 msg="Application Control Detection Alert: MULTIMEDIA YouTube -- DNS YouTube.com" sid=7668 appcat=MULTIMEDIA appid=1 n=1309981 src=10.20.11.92:58632:X1 dst=10.200.0.52:53:X0

key fields:
- src=10.20.11.92:58632:X1
- dst=10.200.0.52:53:X0
- appid=1

I have a lookup table for appid which converts it to the variable app_name="YouTube". The IP addressed don't match in the example because I just grabbed some random message.

In theory if the src and dst are the same (which includes IP and Port number) and it's within a small time window (lets say same hour) then I know it's the same "stream".

Ultimately I would like to relate sent/rcvd (totals) by appid or app_name

[jalfrey · Accepted solution]

after some more reading/digging I found this function called "transaction". It allows you to group like items. You specify what fields must be identical and what the start and stop indication are. Here is my search:

index=sonicwall sent=* OR appid=* OR rcvd=* | transaction keepevicted=true src dst startswith="Connection Opened" endswith="Connection Closed" | stats sum(sent) as bits_sent sum(rcvd) as bits_rcvd by app_name

[MuS]

Hi jalfrey

well if you can match src and dst in both logs and already got a lookup table for appid, then this kind of search should give you the desired result:

 whateveryoursearchis | top limit=10 src, dst, appid, sent, rcvd | stats sum(sent) AS total_sent, sum(rcvd) AS total_rcvd, values(src) AS source by appid

I can not verify this to be correct, but I think it will get you started.

cheers,
MuS

[jalfrey]

if I use the rest of the command without the top I get the appid in the first column. The next two sent and received are empty and I get multiple IPs in the source column. So that's also not working.

[jalfrey]

ok my search is
index=sonicwall msg="Connection Closed" OR appid=*
If I have the top=limit=10 src, dst, appid, sent, rcvd
I get no results. If I limit the top to only two values or three I do get results.