The top command is very simple to use but is actually doing a fair amount of interesting work. I often start with top, then switch to stats count, but then wish for something that top provides automatically. This exercise will show you how to recreate all the elements so that you might pick and choose what you need.
Let's recreate the top command by using other commands.
Here is the query that we will replicate:
sourcetype="impl_splunk_gen" error | top useother=t limit=5 logger user
The output looks like this:
To build count, we can use stats like this:
sourcetype="impl_splunk_gen" error | stats count by logger user
This gets us most of the way towards our final goal:
To calculate the percentage that top includes, we will first need the total number of events. The eventstats command lets us add statistics to every row without replacing the rows:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount
The following adds our totalcount column in the result:
Now that we have our total, we can calculate the percentage for each row. While we're at it, let's sort the results in descending order by count:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount | eval percent=count/totalcount*100 | sort -count
This gives us the following output:
If not for useother=t, we can simply end our query with head 5, which would return the first five rows. To accomplish the other row, we will have to label everything beyond row 5 with a common value, and collapse the rows using stats. This will take a few steps.
First, we need to create a counter field, which we will call rownum:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount | eval percent=count/totalcount*100 | sort -count | eval rownum=1
This gives us the following result (only the first 10 rows are shown):
Next, using accum, we will increment the value of rownum:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount | eval percent=count/totalcount*100 | sort -count | eval rownum=1 | accum rownum
This gives us the following output (only the first 10 rows are shown):
Now, using eval, we can label everything beyond row 5 as OTHER and flatten rownum beyond 5:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount | eval percent=count/totalcount*100 | sort -count | eval rownum=1 | accum rownum | eval logger=if(rownum>5,"OTHER",logger) | eval user=if(rownum>5,"OTHER",user) | eval rownum=if(rownum>5,6,rownum)
We get the following output (only the first 10 rows are shown):
Next, we will recombine the values using stats. Events are sorted by the fields listed after by, which will maintain our original order:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount | eval percent=count/totalcount*100 | sort -count | eval rownum=1 | accum rownum | eval logger=if(rownum>5,"OTHER",logger) | eval user=if(rownum>5,"OTHER",user) | eval rownum=if(rownum>5,6,rownum) | stats sum(count) as count sum(percent) as percent by rownum logger user
This gives us the following:
We're almost done. All that's left to do is hide the rownum column. We can use fields for this purpose:
sourcetype="impl_splunk_gen" error | stats count by logger user | eventstats sum(count) as totalcount | eval percent=count/totalcount*100 | sort -count | eval rownum=1 | accum rownum | eval logger=if(rownum>5,"OTHER",logger) | eval user=if(rownum>5,"OTHER",user) | eval rownum=if(rownum>5,6,rownum) | stats sum(count) as count sum(percent) as percent by rownum logger user | fields - rownum
This finally gives us what we are after:
And we're done. Just a reminder of what we were reproducing:
top useother=t limit=5 logger user
That is a pretty long query to replicate a one-liner. While completely recreating top is not something that is practically needed, hopefully this example has shed some light on how to combine commands in interesting ways.