topic Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total in Splunk Search

sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

surekhasplunk — Fri, 10 Nov 2017 13:47:12 GMT

i have 11 rows as output of my search query.Which looks like below
Example
field1 field2
co 10
im 10
ae 10
be 10
iapp 10
idec 10
ienp 20
imain 30
inet 40

How can i alphabetically sort first 5 rows then get the total in lext row and then next 6 lines sort alphabetically and get sum in the last row .
I want the output like below

Example output
field1 field2
iapp 10
idec 10
ienp 20
imain 30
inet 40
Total 110
co 10
im 10
ae 10
be 10
Total 150

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

xavierashe — Fri, 10 Nov 2017 13:57:34 GMT

Take a look at union.
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Union

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

somesoni2 — Fri, 10 Nov 2017 14:48:00 GMT

Your sample data has 9 rows instead of 11 rows you mentioned. Should the sum of last 6 (or 4) include the cumulative sum or just sum of last 6(or 4)?

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

niketn — Fri, 10 Nov 2017 15:11:03 GMT

@surekhasplunk, I could not figure out a way to do this in single search because you need to many things to be done. I could not get what kind of sort you have applied. The data does not seem to be sorted ascending or descending. However, just to keep first five field1 starting with i in the first table I have applied reverse sort. Please see If the following output will suffice the need.

In order to get the above output following steps have been taken.
Step 1 Create your query to get stats as field2 for field1 values. Save as Base Search for post processing. Following is a run anywhere search for example, but you should replace with your own.

  <search id="baseSearch">
    <query>|  makeresults
  |  eval data="co 10;im 10;ae 10;be 10;iapp 10;idec 10;ienp 20;imain 30;inet 40"
  |  makemv data delim=";"
  |  mvexpand data
  |  eval data=split(data," ")
  |  eval field1=mvindex(data,0)
  |  eval field2=mvindex(data,1)
  |  table field1 field2
  |  sort - field1</query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
    <sampleRatio>1</sampleRatio>
    <done>
      <eval token="tokTailCounter">$job.resultCount$-5</eval>
    </done>
  </search>

Step 2: Add <done> Search Event Handler to get the remaining row count or total results - 5 as above.

Step 3:
a) Create first table for first five rows of base search using | head 5
b) Use eventstats to get total of first five rows and attach to 5 rows as cumuField2.
c) Use addcoltotals command to get total of five rows and display as Total in a new row.
d) Use <done > Search Event Handler to set the token for total of first five rows set in cumuField2 as tokFirst5RowsTotal
e) Use the Table Simple XML configuration <fields> to show only field1 and field2 and hide column cumuField2

  <row>
    <panel>
      <table>
        <search base="baseSearch">
          <query>|  head 5
          | eventstats sum(field2) as cumuField2
          | addcoltotals field2 label="Total" labelfield="field1"</query>
          <done>
            <set token="tokFirst5RowsTotal">$result.cumuField2$</set>
          </done>
        </search>
        <fields>field1,field2</fields>

Step 4 : Prepare the second table with remaining rows and overall total of all the rows.
a) Create second table to display remaining rows from base search. Give it id="myTable2"
b) Use the tail command with $tokTailCounter$ set in the base search to show remaining results other than first 5.
c) Use addcoltotals to get a total of remaining rows in the second table as new row Total.
d) Use eval to add the total of first five rows from first table (i.e. $tokFirst5RowsTotal$ ) to the Total calculated in previous step.

  <table id="myTable2">
    <search base="baseSearch">
      <query>| tail $tokTailCounter$
      | addcoltotals  field2 label="Total" labelfield="field1"
      | eval field2=if(field1=="Total",field2+$tokFirst5RowsTotal$,field2)</query>
    </search>

Step 5: Create an html panel with CSS to hide the table header of second table using selector based on table 2 id i.e. myTable2:

  <row>
    <panel>
      <html depends="$alwaysHideCSSStyle$">
        <style>
          #myTable2 thead{
            visibility:hidden;
          }
        </style>
      </html>
      ...

Following is the complete run anywhere Dashboard code based on sample data provided and used in the screenshot above:

<dashboard>
  <label>Sort and Add</label>
  <search id="baseSearch">
    <query>|  makeresults
  |  eval data="co 10;im 10;ae 10;be 10;iapp 10;idec 10;ienp 20;imain 30;inet 40"
  |  makemv data delim=";"
  |  mvexpand data
  |  eval data=split(data," ")
  |  eval field1=mvindex(data,0)
  |  eval field2=mvindex(data,1)
  |  table field1 field2
  |  sort - field1</query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
    <sampleRatio>1</sampleRatio>
    <done>
      <eval token="tokTailCounter">$job.resultCount$-5</eval>
    </done>
  </search>
  <row>
    <panel>
      <table>
        <search base="baseSearch">
          <query>|  head 5
          | eventstats sum(field2) as cumuField2
          | addcoltotals field2 label="Total" labelfield="field1"</query>
          <done>
            <set token="tokFirst5RowsTotal">$result.cumuField2$</set>
          </done>
        </search>
        <fields>field1,field2</fields>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <html depends="$alwaysHideCSSStyle$">
        <style>
          #myTable2 thead{
            visibility:hidden;
          }
        </style>
      </html>
      <table id="myTable2">
        <search base="baseSearch">
          <query>| tail $tokTailCounter$
          | addcoltotals  field2 label="Total" labelfield="field1"
          | eval field2=if(field1=="Total",field2+$tokFirst5RowsTotal$,field2)</query>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

PS: I hope experts might be able to give more direct approach to this using Single search as this is a bit roundabout method, however, I have used Post processing to ensure that results are fetched only once and the other two searches for two tables are on two separate subset from the base search using head and tail commands.

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

niketn — Fri, 10 Nov 2017 15:13:09 GMT

@somesoni2, I have posted an answer with two separate searches for first five rows and remaining rows respectively. I am sure you have a single query in mind to do this. I will be waiting for your answer 🙂

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

surekhasplunk — Fri, 10 Nov 2017 15:13:10 GMT

Hi @somesoni2,
I need sum of first 5 rows sum as total which is 110 . And then at the end sum of first 5 + last 4 values summing as total of 150.
field1 field2
iapp 10
idec 10
ienp 20
imain 30
inet 40
Total 110
co 10
im 10
ae 10
be 10
Total 150

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

surekhasplunk — Fri, 10 Nov 2017 15:14:02 GMT

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

somesoni2 — Fri, 10 Nov 2017 15:36:59 GMT

Thanks @niketnilay for sample event generating search. Here is my attempt to do that in single search. Everything before | sort -field1 is to generate sample data, replace that with your search. To understand the logic better, add one command at a time and see the result.

| gentimes start=-1  |  eval data="co 10;im 10;ae 10;be 10;iapp 10;idec 10;ienp 20;imain 30;inet 40" |  makemv data delim=";" |  mvexpand data
 |  eval data=split(data," ")  |  eval field1=mvindex(data,0) |  eval field2=mvindex(data,1)  |  table field1 field2   
|  sort - field1
| addcoltotals label=Total labelfield=field1 | streamstats count as sno
| appendpipe [| head 5 | addcoltotals label=Total labelfield=field1 | eval keep="Y" ]
| appendpipe [| where NOT keep="Y" AND sno>5 | eval keep="Y" ] | where keep="Y" | table field1 field2

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

niketn — Fri, 10 Nov 2017 15:39:23 GMT

@somesoni2, you are the boss 🙂

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

surekhasplunk — Mon, 13 Nov 2017 12:02:26 GMT

Thanks a lot @somesoni2 and @niketnilay for this single search it worked perfect for me. Thanks a lot ... cheers...

Re: sort first 5 rows alphabetically then get total count then next 6 rows alphabetically and get the total

surekhasplunk — Tue, 05 Dec 2017 10:43:38 GMT

Hi @niketnilay & @somesoni2

But problem is if am choosing a single value from dropdown then for a single row also am getting IT operations overall and CTO Total
which is wrong . So if i select single value from dropdown then i should not show both the calculated values.
Is it possible ?

Thanks