I m using append query multiple times for different searches for same index.
Its parsing my job. Please advise solution.
top routingkey=0 # this is wrong.
|top routingkey limit=0 # supposed to be.
check @Nisha18789 answer properly.
index=victorops
| stats count by routingKey
| rex field=routingKey mode=sed "s/routing([A-Z]+).*/\1/"
| eventstats sum(count) as total
| eval perc = round( count / total * 100 ,2 )
| appendpipe [| stats sum(count) as Count avg(perc) as Percent
| eval routingKey="Total"]
| table routingKey Count Percent
| rename routingKey = "Product Name"
Hello
These query gives the output all my routingKey in ascending order.
I need below --
Product Name Count Percent (avg)
IA 50 50%
IB 75 30%
IC 55 0.5%
ID 0 0%
Each product name has different routingKey.
Output should be -- i need avg for percent in TOTAL row.
There is nothing about logs. what do you think making the query?
index=victorops
routingKey=routingA OR routingKey=routingB OR routingKey=routingKeyC OR routingKey=routingKeyD OR routingKey=routingE | top limit=50 routingKey
| stats sum(count) as count, avg(percent) as percent
| eval routingKey = "IA"
| append [ search routingKey=routingAA OR routingAB OR routingKey=routingAC OR routingKey=routingAD OR routingKey=routingAE | top limit=50 routingKey
| stats sum(count) as count, avg(percent) as percent
|eval routingKey = "IB" ]
| addcoltotals labelfield=routingKey label=Total
| table routingKey, count, percent
Above resulted below --
Product Name Count Percent
IA 50 0.25
IB 40 30
Total 90 (Sum of routigKey) 15.125 (avg) ----I need this as average
But while running above query, its parsing my output. Plz advise
appendpipe is enough.
Its parsing my output as I have number of routingKey to append.
can you specify index or sourcetype or source in append search? this will limit search to that particular index or sourcetype or source and performance will improve.
your sub search used in append :
search routingKey=routingCLBT_Infrastructure_Operations # this will return only one routingKey
| top limit=50 routingKey # you will only get one result with 3 headers routingKey count percent and percent is 100% here since you have applied filter already and routingKey has only one at the moment.
| stats sum(count) as count, avg(percent) as percent # it has no effect
| eval routingKey="CLBT" # creating field and assigning static value.
I would suggest below search in append command:
| append [search index=<specify_index>
| stats count by routingKey
| eventstats sum(count) as total
| eval percent=round((count/total)*100,2)
| search routingKey=routingCLBT_Infrastructure_Operations
| eval routingKey="CLBT"]
Hello, Thanks for help !
But , i have multiple routingKey's for one Output. So how can I modify this? Below is example which I have run for my report.I need addtotals for all with count and percent(avg) --Below results parsing my output. I have "IA- IJ" field values.
index=victorops
routingKey=routingA OR routingKey=routingB OR routingKey=routingKeyC OR routingKey=routingKeyD OR routingKey=routingE | top limit=50 routingKey
| stats sum(count) as count, avg(percent) as percent
| eval routingKey = "IA"
| append [ search routingKey=routingAA OR routingAB OR routingKey=routingAC OR routingKey=routingAD OR routingKey=routingAE | top limit=50 routingKey
| stats sum(count) as count, avg(percent) as percent
|eval routingKey = "IB" ]
| addcoltotals labelfield=routingKey label=Total
| table routingKey, count, percent
Hi @Manasi25 append is an expensive query in terms of search time. Could you please share details on what you are trying to achieve with your query.
Hi @Manasi25 , one question- why you need so many appends? Can you try something like below, it will be quick and I think gives the same output.
You have to include all the routing keys below, I have just added two sets and accordingly update the case statement as well.
index=victorops routingKey=routingA OR routingKey=routingB OR routingKey=routingKeyC OR routingKey=routingKeyD OR routingKey=routingE OR routingKey=routingAA OR routingAB OR routingKey=routingAC OR routingKey=routingAD OR routingKey=routingAE
| eval routingKey=case(routingKey="routingA" OR routingKey="routingB" OR routingKey="routingKeyC" OR routingKey="routingKeyD" OR routingKey="routingE","IA",routingKey="routingAA" OR "routingAB" OR routingKey="routingAC" OR routingKey="routingAD" OR routingKey="routingAE","IB",1=1,"other")
| top routingKey limit=0
| addcoltotals labelfield=routingKey label=Total
Hello
Given query resulted below.