How to Pass Other Value from a Single Value Trellis Visualization?
Hei, today we are back with another interesting topic of Splunk. Without wasting any time let’s get to the business.
Use Case:
We have this search query which is basically giving them all distinct counts of bytes for each “file”.
index=_internal sourcetype="splunkd_ui_access"
|stats dc(bytes) as count by file
As you can see we have more than 250 “file” values, now our job is to find the top 10 “file” names which have the highest count, and the rest of the “file” name will be merged in an “Other” value.
Let’s start,
Step 1:
index=_internal sourcetype="splunkd_ui_access"
| stats dc(bytes) as count by file
| eventstats sum(count) as totalusers
| sort 10 -"count"
| appendpipe
[ stats sum(count) as topusers avg(totalusers) as totalusers
| eval "file" = "Others"
| eval "count" = totalusers - topusers]
| chart values(count) as count by file
Explanation:
We took the data from the index “_internal” and “splunkd_ui_access” source type. Then we have used the “stats” command to bring all the distincit count of bytes for each “file”. Then we have used “eventstats” command and “sum” function to calculate the total summation of the “count” filed and listed in a field called “totalusers”. After that using the “sort” command we have sorted the top 10 files. Then we have used a sub search within “appendpipe” command, where we have calculated the total summation of the rest of the “file” count except the top 10 in a field value called “Other”. After that using the chart command, we charted all counts with their respective files.
Now as you can see from the above figure it is giving a total of 11 results (top 10 file and “Others”).
Now we will save this in a dashboard called “Drilldown Tricks” by clicking “Save As” > “Dashboard Panel”. We have used a “Single value visualization” in the form of Trellis here.
Use Case:
Our next job is to create a drill-down for this, if we click on any file other than “Others” then it should show a detailed description of that “file” but if we click on “Others” it should show all the details of all the “file” values are merged into the “Others” except top 10 “file”.
Step 2:
First, add the drill-down panel below the main panel. With the main query.
index=_internal sourcetype="splunkd_ui_access"
|stats dc(bytes) as count by file
Step 3:
Now we will try to build a query with top 10 file values returned in a single line, concatenated with NOT, thus whenever we will select “Others” from the drill-down this query will pass and it will eliminate the top 10 files, which means it will only fetch those “file” values which are merged into the “Others”.
index=_internal sourcetype="splunkd_ui_access"
| stats dc(bytes) as count by file
| eventstats sum(count) as totalusers
| sort 10 - "count"
| rename file as aa
| return 10 aa
| eval search=replace('search',"OR","NOT") , search=replace('search',"aa","\"file\"") , search="NOT ".search
Explanation:
We took the data from the index “_internal” and “splunkd_ui_access” source type. Then we have used the “stats” command to bring all the distincit count of bytes for each “file”. Then we have used “eventstats” command and “sum” function to calculate the total summation of the “count” filed and listed in a field called “totalusers”. After that using the “sort” command we have sorted the top 10 files. Then we have renamed the file field as “aa” . After that, we have used “return” command to return all 10 values of “aa” field. Then we have replaced “OR” with “NOT”, “aa” with “file” from the newly generated search field and we concatenated “NOT” at the beginning.
Step 4:
Now, let’s make conditions for the drill-downs. Go to the dashboard click on Edit and switch to the “source code”.
Now go through the below picture and do it as mentioned.
Here within the drill-down tag, we have used two conditions.
1st Condition: whenever we will select any file value other than “Others”, 1st condition will work. If we select any “file” value that will store within the “new” token and that will carry in the next eval tag where we have a token named as “extcomv”.
2nd Condition: whenever we will select “Others”, 2nd condition will work. Where the query we created in step 3 will replace the “extcomv” token in the drill-down query.
After that, we have added “new” token in the drill-down panel title. Then in the drill-down search, we added |search $extcomv$.
Save the source code and refresh the dashboard.
Result:
When we will select “Others” it will show all the file values except the top 10.
I hope you all have enjoyed this blog “How to Pass Other Value from a Single Value Trellis Visualization?“. See you all on to the next one.
Download the complete source code, from the below link.
