Timechart with count of open connections per protocol (TCP/UDP/ICMP) ?

[rhornung]

Hi, i'm getting stuck an weird using Splunk to show me am Timechart for the last 30 days with open connection per protocol.

Input looks like:
Jan 17 13:19:34 mydevice : %ASA-6-302013: Built outbound TCP connection
Jan 17 13:19:34 mydevice : %ASA-6-302014: Teardown TCP connection
Jan 17 13:19:34 mydevice : %ASA-6-302016: Teardown UDP connection
Jan 17 13:19:34 mydevice : %ASA-6-302015: Built outbound UDP connection
Jan 17 13:19:34 mydevice : %ASA-6-302021: Teardown ICMP connection
Jan 17 13:19:34 mydevice: %ASA-6-302020: Built outbound ICMP connection

my search statement:
%ASA-6-3020* NOT %ASA-6-302010 | timechart count by Cisco_ASA_message_id

brings up a wonderful timechart table with absolute values on how many connections were built and closed in a specific timeperiod.
it shows me the amount of built TCP connections , teardowned TCP connections built UDP connections, and so on.

Goal: My Goal ist a timechart on a statement like: Opened TCP connections = Built TCP connections - Teardowned TCP connection so i' receive three lines (one for each Protocol TCP,UDP and ICMP). Each Cisco_ASA_message_id stands for a specific event.

Any suggestions?

regards from an absolute beginner

[rhornung]

Hi Splunk-Community

After several tries and being inspired by above samples (many thanks to repliers) my search query looks like:

index=myindex AND sourcetype="my_sourcecode" AND %ASA-6-3020* AND NOT %ASA-6-302010
| rex "(?<protocol>UDP|TCP|ICMP)" 
| eval connection=case(Cisco_ASA_message_id=="302013" OR Cisco_ASA_message_id=="302015" OR Cisco_ASA_message_id=="302020",1,
                       Cisco_ASA_message_id=="302014" OR Cisco_ASA_message_id=="302016" OR Cisco_ASA_message_id=="302021",-1)
| timechart sum(connection) by protocol

This query creates a field connection with the values 1 if the field Cisco_ASA_message_id conforms to "Built" Events and a -1 if the field Cisco_ASA_message_id conforms to "Teardown" Events.

It works but takes very long (for a report period of 7 days) maybe there is an option or other way to accelerate it!???

Regards

[to4kawa]

index=myindex sourcetype="my_sourcecode" 
    AND (Cisco_ASA_message_id="302013" OR Cisco_ASA_message_id="302015" OR Cisco_ASA_message_id="302020" OR Cisco_ASA_message_id="302014" 
    OR Cisco_ASA_message_id="302016" OR Cisco_ASA_message_id="302021") 
| rex "(?<protocol>UDP|TCP|ICMP)" 
| eval connection=case(Cisco_ASA_message_id=="302013" OR Cisco_ASA_message_id=="302015" OR Cisco_ASA_message_id="302020",1,
    Cisco_ASA_message_id=="302014" OR Cisco_ASA_message_id=="302016" OR Cisco_ASA_message_id=="302021",-1)
| fields _time protocol connection
| timechart sum(connection) by protocol

a little faster. Of course you use fast mode search don't you?

[rhornung]

Fast Mode-> yes, Thank you for your support and happy splunking!

[woodcock]

Try this:

index="YouShouldAlwaysSpecifyAnIndex" AND sourcetype="AndSourcetypeToo" AND %ASA-6-3020* AND NOT %ASA-6-302010
| rex "(?<protocol>UDP|TCP|ICMP)"
| timechart count AS debugCount count(eval(searchmatch("Built"))) AS Built count(eval(searchmatch("Teardown"))) AS Teardown BY protocol Cisco_ASA_message_id
| eval Opened = Built - Teardown

[to4kawa · Accepted solution]

your_search
| rex  "(?<connection>Built|Teardown).*(?<protocol>(TCP|UDP|ICMP))\s+connection$" 
| table _time connection protocol 
| bin _time span=10m 
| stats count as Count by _time connection protocol 
| stats sum(eval(if(connection="Built",Count,NULL))) as Built sum(eval(if(connection="Teardown",Count,NULL))) as Teardown by _time protocol 
| fillnull 
| eval Open=Built-Teardown 
| xyseries _time protocol Open
| fillnull

bin _time span=10m Time span is as you like.

---

| makeresults 
| eval _raw="Jan 17 13:19:34 mydevice : %ASA-6-302013: Built outbound TCP connection
Jan 17 13:19:35 mydevice : %ASA-6-302014: Teardown TCP connection
Jan 17 13:19:36 mydevice : %ASA-6-302016: Teardown UDP connection
Jan 17 13:19:37 mydevice : %ASA-6-302015: Built outbound UDP connection
Jan 17 13:19:38 mydevice : %ASA-6-302021: Teardown ICMP connection
Jan 17 13:19:39 mydevice : %ASA-6-302020: Built outbound ICMP connection
Jan 17 13:19:40 mydevice : %ASA-6-302013: Built outbound TCP connection
Jan 17 13:19:41 mydevice : %ASA-6-302014: Teardown TCP connection
Jan 17 13:19:42 mydevice : %ASA-6-302016: Teardown UDP connection
Jan 17 13:19:43 mydevice : %ASA-6-302015: Built outbound UDP connection
Jan 17 13:19:44 mydevice : %ASA-6-302021: Teardown ICMP connection
Jan 17 13:19:45 mydevice : %ASA-6-302020: Built outbound ICMP connection" 
| makemv delim="
" _raw 
| stats count by _raw 
| rex "(?<_time>\w+ \d\d \d\d:\d\d:\d\d) (?<device>\w+)\s*: (?<Cisco_ASA_message_id>\S+): (?<Cisco_ASA_message>.+)" 
| eval _time=strptime(_time,"%b %d %T") 
| table _time device Cisco_ASA_message_id Cisco_ASA_message _raw
    `comment("this is sample you provide")`
    `comment("From here, the logic")`
| rex field=Cisco_ASA_message "(?<protocol>\w+)\s+connection"
| streamstats count(eval(searchmatch("Built"))) as session by protocol
| stats min(_time) as starttime max(_time) as endtime range(_time) as duration by session protocol
| fieldformat starttime=strftime(starttime,"%F %T")
| fieldformat endtime=strftime(endtime,"%F %T")

Hi, @rhornung
I don't understand Goal: My Goal ist a timechart on a statement like: Opened TCP connections = Built TCP connections - Teardowned TCP connection
Do you want to create the chart? X-axis is time and Y-axis ...what?
Please tell me the details.

Above query, Each protocol's start and end time is listed. and duration(sec).
duration is "0" , because protocol does not start Built in this log.

Actually searching, please select right time range. and use reverse

Because, basically, the latest log is at the top of the list. This case, this query does not work.
Old logs need to be on top using reverse

How about this?

[rhornung]

Hi, to4kawa,

Finally your solution works fine for me!your_search
| rex "(?<connection>Built|Teardown).*(?<protocol>(TCP|UDP|ICMP))\s+connection$"
| table _time connection protocol
| bin _time span=10m
| stats count as Count by _time connection protocol
| stats sum(eval(if(connection="Built",Count,NULL))) as Built sum(eval(if(connection="Teardown",Count,NULL))) as Teardown by _time protocol
| fillnull
| eval Open=Built-Teardown
| xyseries _time protocol Open
| fillnull

Many thanks for your quick reply! Best regards
rhornung

[to4kawa]

I think your query is smarter.
search speed is OK?
I comment your query. please confirm.
Happy splunking.

[rhornung]

Hi to4kawa,

Thanks for your input and response.
To answer your question:

I want to create a timechart with 3 lines (each line stands for one protocol TCP, UDP an ICMP) where x -axis shows the timeline an the y -axis shows me the count of opened connections during the calculated span time.

The ASA Logfile only gives me Built and Teardown events, so the current opened connections for each moment of time hast to be calculated: opened connections = Created (->Built) - closed(->Teardown) connections.

I want to get a feeling on whats happening on the ASA, so the purpose for this timechart is to get a time-based baseline which shows me anomalies on the firewall-activities.

Hope this explanation helps.

Thx in advance

Regards

[jscraig2006]

something like this? I threw in to only show fields "Cisco_ASA_message_id" since I know ASA is intensive.

index=firewall sourcetype="cisco:asa" Cisco_ASA_message_id="3020*" NOT Cisco_ASA_message_id="302010"
| fields Cisco_ASA_message_id
| eval connection_type = case(Cisco_ASA_message_id==302013 OR Cisco_ASA_message_id==302014,"tcp", Cisco_ASA_message_id==302015 OR Cisco_ASA_message_id==302016,"upd", Cisco_ASA_message_id==302020 OR Cisco_ASA_message_id==302021,"icmp")
| timechart count by connection_type

[rhornung]

Hi jscraig2006

Thank you for your input and response on my question.

Your query summarizes the count of built and teardown events, so i get the total amount on events for each protocol.

As i wrote (and for baselining reasons) i'd prefer a timechart where open connections are calculated as Built - Teardown Events.

Regards