Splunk Search

How do you rename chart columns and add a dynamic count in brackets?

VexenCrabtree
Path Finder

I've got an average session duration (gotten via | Transaction) broken down by EndStatus. EndStatus is the cause of the end of the session (i.e. a a Reset from the client (RST-O), a FIN, etc). This is charted into a barchart. And, as an overlay, I have the count of how many sessions terminated in that way (blue line, fieldname "Q"). So, you can see (for example), if sessions that end in a client-reset are on average much shorter than sessions ended due server sending a RST, and, how many of each ("Q") are occurring.

I was going to attach the chart as it'd be easier to understand, but I don't have enough karma to add attachments O_o.

What I want is to remove the need for the 2nd axis (showing count), by stating the count of events /in the column titles. In other words, to rename “FIN-O” Status to “FIN-O (8)” and RST-O to “RST-O (95)”.

Here's the SPL:

index=firewall <SNIP SEARCH DETAILS>
| transaction src maxevents=2 startswith=built endswith=teardown 
| eval EndStatus=case(msg LIKE "%Tunnel has been torn down%","Torn Down",msg LIKE "%SYN Timeout%","SYN Timeout",msg LIKE "%TCP Reset-O from OUTSIDE%", "RST-O",msg LIKE "%TCP Reset%","RST",msg LIKE "%TCP FINs from OUTSIDE%","FIN-O",msg LIKE "%TCP FIN%","FIN",msg LIKE "%Connection timeout%","Connection Timeout",true(),"Other")
| eval session_length_in_minutes=duration/60
| chart count AS Q, avg(session_length_in_minutes) by EndStatus

I’ve been trying to use "AS" to rename the field using various brackets and tricks, but I don't think it evaluates any dynamic values.

| chart count AS Q,avg(session_length_in_minutes) AS [EndStatus+Q] by EndStatus

or

| avg(session_length_in_minutes) AS "EndStatus"+count by EndStatus

There are some similar (ish) questions on here already with answers (I've gone through the suggested answers), but they've not hit the situation where they want an aggregate data in the (new) column names. I've also been trying starting off with STATS, renaming, and piping into RENAME and then CHART.

1 Solution

knielsen
Contributor

Can't you just add a final eval to your first search?

 index=firewall <SNIP SEARCH DETAILS>
 | transaction src maxevents=2 startswith=built endswith=teardown 
 | eval EndStatus=case(msg LIKE "%Tunnel has been torn down%","Torn Down",msg LIKE "%SYN Timeout%","SYN Timeout",msg LIKE "%TCP Reset-O from OUTSIDE%", "RST-O",msg LIKE "%TCP Reset%","RST",msg LIKE "%TCP FINs from OUTSIDE%","FIN-O",msg LIKE "%TCP FIN%","FIN",msg LIKE "%Connection timeout%","Connection Timeout",true(),"Other")
 | eval session_length_in_minutes=duration/60
 | chart count AS Q  avg(session_length_in_minutes) as avg by EndStatus
 | eval EndStatus=EndStatus+" ("+avg+")"

and maybe "fields - avg" after that?

View solution in original post

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...