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
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?