Splunk Search

calculate top talkers by application

jalfrey
Communicator

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

Tags (3)
0 Karma
1 Solution

jalfrey
Communicator

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

View solution in original post

0 Karma

jalfrey
Communicator

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

0 Karma

MuS
Legend

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

0 Karma

jalfrey
Communicator

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.

0 Karma

jalfrey
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...