topic Re: Complex Query question in Splunk Search

Complex Query question

rcolby — Fri, 05 Nov 2010 04:54:12 GMT

I am sending my sonic wall data to splunk via syslog. I am trying to get a report to show me how many open connections by src ip. However I am not sure how to do this.

The 2 event type I need to tie together are

Nov 4 17:42:38 192.168.150.1 id=firewall sn=xxxxxxxxx time="2010-11-04 17:42:42" fw=192.168.254.5 pri=6 c=1024 m=537 msg="Connection Closed" n=0 src=192.168.150.93:1637:X0 dst=192.168.100.10:4440:X2 proto=tcp/4440 sent=2505 rcvd=677
host=192.168.150.1

Nov 4 17:41:53 192.168.150.1 id=firewall sn=xxxxxxxxx time="2010-11-04 17:41:56" fw=192.168.254.5 pri=6 c=262144 m=98 msg="Connection Opened" n=0 src=192.168.150.93:1637:X0 dst=192.168.100.10:4440:X2 proto=tcp/4440

So in short I want to know how here src=192.168.150.93:1637:X0 has open a connection. Then it closed the connection. I want to find out how many have been opened and have yet to be closed. So current open connection count and sort it by just the src ip so strip of the 1637:XO when grouping.

Any help would be great

Re: Complex Query question

araitz — Fri, 05 Nov 2010 05:17:00 GMT

sourcetype=sonicwall msg="Connection Opened" OR msg="Connection Closed" 
| stats dc(msg) as dc values(msg) as msg by src dst 
| mvexpand msg  
| search dc=1 AND msg="Connection Opened"
| fields src dst

EDIT: Carasso wasn't happy with my search because it might miss instances where multiple connections were made. This search is better:

sourcetype=sonicwall msg="Connection Opened" OR msg="Connection Closed" 
| stats count(eval(msg="Connection Opened")) as open_count 
  count(eval(msg="Connection Closed")) as closed_count 
  first(msg) as last_msg
  values(msg) as msg by src dst 
| search last_msg="Connection Opened"
| where open_count > closed_count
| fields src dst

Re: Complex Query question

Marinus — Sat, 06 Nov 2010 19:31:16 GMT

Care to annotate your search Alex?

Re: Complex Query question

Lowell — Fri, 14 Jan 2011 05:37:28 GMT

It seems only right to the following blog page, since this question inspired it:

http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/comment-page-1

Re: Complex Query question

BobM — Fri, 22 Jul 2011 10:50:23 GMT

For this type of search dedup is your friend. If you search for all msg="Connection Opened" OR msg="Connection Closed" events, the most recent for any connection is it's current status.

sourcetype=sonicwall msg="Connection Opened" OR msg="Connection Closed"
| dedup src_ip dest_ip proto
| where msg="Connection Opened"

if you regularly run this search, or it takes a long time, it would be worth summary indexing it with

sourcetype=sonicwall msg="Connection Opened" OR msg="Connection Closed"
| dedup src_ip dest_ip proto
| sistats count by src_ip dest_ip proto msg

and to report, use

index=summary search_name=YourSearchName 
|  dedup src_ip dest_ip proto
| where msg="Connection Opened"

or you could use the blogged method.