Appending Searched parsing output

[Manasi25]

I m using append query multiple times for different searches for same index.

Its parsing my job. Please advise solution.

[thambisetty]

top routingkey=0 # this is wrong.

|top routingkey limit=0 # supposed to be.

check @Nisha18789  answer properly.

[to4kawa]

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"

[Manasi25]

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.

[to4kawa]

@Manasi25 

There is nothing about logs. what do you think making the query?

[Manasi25]

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

[to4kawa]

appendpipe is enough.

[Manasi25]

Its parsing my output as I have number of routingKey to append.

[thambisetty]

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"]

[Manasi25]

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

[Nisha18789]

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.

[Manasi25]

@richgalloway -

Below you which you given solution, parsing my output. Please advise.

[Nisha18789]

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

[Manasi25]

Hello

Given query resulted below.