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

woodcock
Esteemed Legend

Here is a run-anywhere example:

index=_* 
| rename sourcetype AS EndStatus, date_hour AS session_length_in_minutes 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| chart count AS Q, avg(session_length_in_minutes) AS avg_session_length_in_minutes by EndStatus 
| eval EndStatus = EndStatus . "(" . avg_session_length_in_minutes . ")" 
| fields - avg_session_length_in_minutes

VexenCrabtree
Path Finder

I upvoted this because it's the same style as the solution that I implemented; it is a shame you can't mark answers as "equally correct"!

woodcock
Esteemed Legend

You were first by a bit, so you deserve it! I UpVoted you, too.

0 Karma

paramagurukarth
Builder

I have a workaround to your requirement,

you can used splunk token and CSS to achieve this..

<dashboard>
  <label>new</label>
  <row>
    <panel id="my_pannel">
      <table>
        <search>
          <query>your_search | fields column_one, count</query>
          <finalized>
             <set token="header_suffix">$result.count$</set>
           </finalized>            
        </search>
      </table>
    </panel>
  </row>
  <row id="hidden_row">
     <panel>
       <html>
         <style>
           #hidden_row{
               display:none !important;
           }
           #my_pannel th[data-sort-key='column_one'] a:after{
               content:"  $header_suffix$";
           }
           #my_pannel th[data-sort-key='column_one'] i{
            float:right;
           }
         </style>
       </html>
     </panel>
   </row>
</dashboard>
0 Karma

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?

VexenCrabtree
Path Finder

Thank you very much; using this method I got to a finished solution.

And I like the method of using an Eval to insert Comments into the search! You would think that there would a slightly-more efficient

  | Comment "anything goes here"

solution that could be stripped out at parse-time or before hitting the search head! Surely this is already a feature, or is an item in a requested-features list somewhere...

0 Karma

VexenCrabtree
Path Finder

I.... I had presumed that that'd be too-late (i.e., the chart would be generated before the eval was processed). I'll try this when I'm back in the office.

0 Karma

VexenCrabtree
Path Finder

Maybe the same thing, but with a Table.

0 Karma

mayurr98
Super Champion

you are asking a question which is logically not possible. In your query you would get o/p with 3 columns (EndStatus Q avg) and what you are trying is to rename that 3rd column on the basis of each row count. But there is only 1 column and n number of rows .how can you rename that 1 column with "EndStatus " and count(which has n number of records) logically and how it would look like?
can you give me the expected output ?

0 Karma

VexenCrabtree
Path Finder

After doing GROUP BY the EndStatus column, there is actually three sets of results, and its those results that I want to rename.

Something like this:

| stats count AS Q,avg(session_length_in_minutes) by EndStatus

This correctly gives me a table like this:

. --

EndStatus | Average Length | Q |

RST-O........| 45........................| 12 |
RST............| 45........................| 12 |

FIN-O........| 45........................| 12 |

(Sorry if this ASCII table doesn't come out well).

Then, a solution is something like this:

| stats count AS Q,avg(session_length_in_minutes) AS AvgLen by EndStatus
| eval EndStatusQ = EndStatus+Q
| chart AvgLen by EndStatusQ
0 Karma
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 ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...