Splunk Search

Splunk Search for Total Values

madhav_dholakia
Path Finder

Hello There,

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

AnalystMonthTotal CountSLA (%)Working DaysDaily Count (Total Count / Working Days)
ABCMay-206897183.77
DEFMay-2045100202.25
GHIMay-202594151.66
JKLMay-208698223.91
TotalMay-2022497.25752.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

Labels (4)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust

 

 

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

View solution in original post

0 Karma

to4kawa
SplunkTrust
SplunkTrust

 

 

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

View solution in original post

0 Karma

madhav_dholakia
Path Finder

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

Tags (1)
0 Karma

bmunson_splunk
Splunk Employee
Splunk Employee

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
Path Finder

Thank you, @bmunson_splunk

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!