Splunk Search for Total Values

[madhav_dholakia]

Hello There,

I have got a search result as given below (without the highlighted row, i.e. Total):

Analyst	Month	Total Count	SLA (%)	Working Days	Daily Count (Total Count / Working Days)
ABC	May-20	68	97	18	3.77
DEF	May-20	45	100	20	2.25
GHI	May-20	25	94	15	1.66
JKL	May-20	86	98	22	3.91
Total	May-20	224	97.25	75	2.97

Data for all these columns (except "Working Days") is available through a DB Connect Database live feed. Data for column "Working Days" is stored in a csv lookup file (created through Lookup Editor).

Search which gives me above output (without Total Values) is something like:

| inputlookup TeamDetails.csv 
| addinfo
| eval temp=strftime(info_min_time,"%B-%Y")
| where Month_Year=temp
| table Analyst Month_Year
| join type=left Analyst [search index="idx_test" source="src_test" sourcetype="srctype_test"
| sort -editTime
| dedup id
 | lookup TeamDetails.csv Analyst OUTPUT WorkingDays
 | stats count as TotalCount by Analyst, WorkingDays
 | eval DailyCount=round(TotalCount/WorkingDays,2)
 | table Analyst DailyCount WorkingDays ]
 | join type=left Analyst [search index="idx_test" source="src_test" sourcetype="srctype_test"
| sort -editTime
| dedup id
 | eval inSLACount=if(SLA_Flag="1",1,0)
 | eval outSLACount=if(SLA_Flag="0",1,0)
 | stats sum(inSLACount) as  insideSLA,	 sum(outSLACount) as outsideSLA, count(id) as TotalCount by Analyst
 | eval SLA=round(insideSLA/(TotalCount)*100,2)
 | table Analyst SLA TotalCount ]
 | table Analyst Month_Year TotalCount SLA WorkingDays DailyCount

How can I change this search so that I get above given table output (including Total numbers).

Thank you.

Madhav

[to4kawa · Accepted solution]

| makeresults 
| eval _raw="Analyst	Month	TotalCount	SLA	WorkingDays	DailyCount
ABC	May-20	68	97	18	3.77
DEF	May-20	45	100	20	2.25
GHI	May-20	25	94	15	1.66
JKL	May-20	86	98	22	3.91" 
| multikv forceheader=1 
| table Analyst Month TotalCount SLA WorkingDays DailyCount 
| rename COMMENT as "this is your sample. from here, the logic" 
| appendpipe 
    [| stats values(eval("Total")) as Analyst values(Month) as Month avg(SLA) as SLA avg(DailyCount) as DailyCount sum(TotalCount) as TotalCount sum(WorkingDays) as WorkingDays]

Hi @madhav_dholakia 
why don't you try appendpipe ?

Please Accept and Upvote, If you'd like.

[madhav_dholakia]

@to4kawa - works perfectly fine. Thank you very much.

[bmunson_splunk]

I would add this append pipe immediately before your table command.

| append [stats values(Month_Year) as Month_Year sum(TotalCount) as TotalCount avg(SLA) as SLA sum(WorkingDays) as WorkingDays avg(DailyCount) as DailyCount | eval Analyst = "Total" ] 

[madhav_dholakia]

Thank you, @bmunson_splunk