Complex Query question

[rcolby]

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

[BobM]

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.

[Lowell]

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

[araitz · Accepted solution]

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

[Marinus]

Care to annotate your search Alex?