Splunk Search

Appending Searched parsing output

Explorer

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

Its parsing my job. Please advise solution.

 

Manasi25_0-1598316994041.png

 

Labels (1)
0 Karma

Super Champion

top routingkey=0 # this is wrong.

|top routingkey limit=0 # supposed to be.

check @Nisha18789  answer properly.

————————————
If this helps, give a like below.
0 Karma

SplunkTrust
SplunkTrust

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"

0 Karma

Explorer

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.

Manasi25_0-1598494151925.png

 

 

 

0 Karma

SplunkTrust
SplunkTrust

@Manasi25 

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

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

appendpipe is enough.

0 Karma

Explorer

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

0 Karma

Super Champion

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"]
————————————
If this helps, give a like below.
0 Karma

Explorer

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

 

0 Karma

Builder

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.

0 Karma

Explorer

@richgalloway -

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

 

Manasi25_0-1599563146917.png

 

 

0 Karma

Builder

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
0 Karma

Explorer

Hello

 

Given query resulted below.

 

Manasi25_0-1599653357733.png

 

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!