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
| 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.
| 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.
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" ]
Thank you, @bmunson_splunk