- Find Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How do you rename chart columns and add a dynamic ...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You were first by a bit, so you deserve it! I UpVoted you, too.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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>
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe the same thing, but with a Table.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
New Case Study Shows the Value of Partnering with Splunk Academic Alliance
How to Monitor Google Kubernetes Engine (GKE)
Index This | How can you make 45 using only 4?
