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
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
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
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
Thanks a lot @somesoni2 and @niketnilay for this single search it worked perfect for me. Thanks a lot ... cheers...
@somesoni2, you are the boss 🙂
@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.
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)?
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
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
@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 🙂
Take a look at union.
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Union