Splunk Search
Highlighted

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

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.

Highlighted

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

SplunkTrust
SplunkTrust

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
Highlighted

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

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
Highlighted

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

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

Highlighted

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

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
Highlighted

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

Path Finder

Maybe the same thing, but with a Table.

0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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"!