Splunk Search

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

surekhasplunk
Communicator

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

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

surekhasplunk
Communicator

Hi @niketnilay & @somesoni2

With your suggestion right now am using below code to sort first 7 rows then add IT Operations-overall in a row and then at the end CTO total row which sums all which works perfectly.
| addcoltotals label="CTO TOTAL" labelfield="Business Area" | streamstats count as sno
| appendpipe [| head 7 | addcoltotals label="IT OPERATIONS - OVERALL" labelfield="Business Area" | eval keep="Y" ]
| appendpipe [| where NOT keep="Y" AND sno>7 | eval keep="Y" ] | where keep="Y"

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

surekhasplunk
Communicator

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

0 Karma

niketn
Legend

@somesoni2, you are the boss 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@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.

alt text
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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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)?

0 Karma

surekhasplunk
Communicator

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

0 Karma

surekhasplunk
Communicator

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

0 Karma

niketn
Legend

@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 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

xavierashe
Contributor
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...