topic How to add final total count of results without adding another column? in Splunk Search

How to add final total count of results without adding another column?

splunkin11 — Tue, 01 Nov 2016 20:13:44 GMT

I can't seem to figure out a way to add a bottom row for a total count of results (records) to the end of the results without adding another column for a count and then totaling that column. There must be an easier way.

I can't use |stats count which is the number I'm looking for because that suppresses the details of the results.
Using |stats count by ....(all my fields needed in output) works but it adds an unwanted column for the count. If I try to use |fields - count that breaks the total count.

I know there must be a way for this - please help!

Re: How to add final total count of results without adding another column?

cmerriman — Tue, 01 Nov 2016 20:17:05 GMT

can you just add |addcoltotals

Re: How to add final total count of results without adding another column?

splunkin11 — Tue, 01 Nov 2016 20:22:56 GMT

No, that only totals up columns. I have no column to total that will give a total count.

Re: How to add final total count of results without adding another column?

niketn — Tue, 01 Nov 2016 20:25:36 GMT

Try using Splunk commands addcoltotals or addtotals as per your need. Refer to following Splunk documentations:
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Addcoltotals
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Addtotals

Also with latest Splunk Enterprise 6.5 version this feature is inbuilt in UI for tables being printed. Once a table is saved as Dashaboard panel turning on Summary>Totals prints the column totals.

Re: How to add final total count of results without adding another column?

niketn — Tue, 01 Nov 2016 20:28:51 GMT

Oh I now understand. You might have to use $job.resultCount$ property for **** event and then assign it to a token to be used later in your Panel as HTML.

Check Search Result Setter in Splunk 6.x Dashboard Examples

      <chart>
        <title>Top sourcetypes ($sourcetype_count$ total sourcetypes)</title>
        <search>
          <query>index=_internal |  top sourcetype</query>
          <earliest>-60m</earliest>
          <latest>now</latest>
          <!-- Use the following search events to access job properties, and set tokens that can be accessible throughout the page -->
          <!-- Progress event has access to job properties only (not first result row) -->
          <progress>
            <set token="sourcetype_count">$job.resultCount$</set>
          </progress>
          <cancelled>
            <unset token="sourcetype_count"></unset>
          </cancelled>
        </search>
        <option name="charting.chart">bar</option>
        <option name="charting.legend.placement">none</option>
      </chart>

Re: How to add final total count of results without adding another column?

splunkin11 — Tue, 01 Nov 2016 20:31:52 GMT

ok ... I don't have a need to total columns of numbers so this wouldn't apply. I know I can add a column to my output for the sake of using one of these functions but I don't want to add a column that displays a '1' for every record just so I can get a grand total count at the bottom.

Re: How to add final total count of results without adding another column?

niketn — Tue, 01 Nov 2016 20:38:42 GMT

I have edited my answer, you can try using $job.resultCount$ and print anywhere in the panel you like. In Splunk 6.x Dashaborad Examples code, it has been added as Panel Title. Please check and see if it solves your problem.

Re: How to add final total count of results without adding another column?

splunkin11 — Tue, 01 Nov 2016 20:47:04 GMT

wowzers ... I'm not even sure how to incorporate that into my simple dashboard 🙂 There must be a simpler way to get the same result such as with an |appendpipe or some other feature.

Re: How to add final total count of results without adding another column?

lquinn — Tue, 01 Nov 2016 23:31:41 GMT

You could just append a stats count search to your current search. Its not as efficient as the above suggestions but might work as a quick fix if the above is too complicated. A little frustrating that you have to do the same search twice though ...

your_base_search | table field1, field2 | append [ your_base_search | stats count | eval field1="TOTAL EVENTS" | rename count AS field2 ]

An alternative would be to create a count column but then remove it by adding the total count and label to one of your other columns:

your_base_search | eval count=1 | addcoltotals labelfield=field1 label="TOTAL EVENTS" | eval field2=if(field1="TOTAL EVENTS",count,field2) | fields - count

Re: How to add final total count of results without adding another column?

niketn — Wed, 02 Nov 2016 09:05:56 GMT

This is Simple XML example, so it does not require you to add additional css/js ot HTML code. You can refer to Splunk 6.x Dashboard Examples app for details. Just edit source of your existing code and place job.resultCount related code in Chart <title> , <search><progress> and <search><cancelled>. The progress event sets the token sourcetype_count (could be any variable name). Cancelled event resets the token and Title section uses the token. (It is not that tough as it seems :))

PS: Splunk itself uses the same variable for knowing whether Search returned result or not. For example N/A being displayed when search returns not result.

Other options if you do not want to use job.resultCount would be

1) Use accum : ... |eval sno=0 | sno=sno+1 | accum sno| ...

2) Enable Row Number: Edit Panel, Choose Edit Table option and Under General settings set Row Numbers as Yes.

Re: How to add final total count of results without adding another column?

Silmarillion197 — Wed, 05 Aug 2020 06:41:25 GMT

Excellent. This works like a charm.
With this solution, we can show the number of returned results outside a XML table.
Great. Thank you.