How to create a summary table with number of calls and percentage

[raviteja029]

Hi,

Can anyone help write a query where it can display

User | Number of calls current week | number of calls last week | Percentage change in the calls for current and last week

My Query
index=abc client1 OR client2 earliest=-14d@d latest=-7d@d | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) | eval weeknum="Last Week"
| append [ search
index=abc client1 OR client2 earliest=-7d@d latest=-0d@d | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) | eval weeknum="curent Week" ]
| chart count over CustomerName by weeknum

how to add percentage change here ?

Thanks in advance

[niketn · Accepted solution]

I think you are hitting the limits of maximum events allowed in append command. You could try one of the following:

Option 1) Use multisearch which is not restricted by subsearch limitations:

| multisearch
          [search index=abc client1 OR client2 earliest=-14d@d latest=-7d@d 
          | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) 
          | eval weeknum="Last Week" ]
           [  search index=abc client1 OR client2 earliest=-7d@d latest=-0d@d 
          | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) 
          | eval weeknum="curent Week"]
| chart count over log_level by weeknum
| eval perc=round(('Yesterday'/'Last Week Same Day')*100,2)

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Multisearch#Subsearch_processing...
Option 2) Use stats in each search and use appendcols instead of append:

 index=abc client1 OR client2 earliest=-14d@d latest=-7d@d 
| eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) 
| chart count as "Last Week" over CustomerName  
| appendcols [ search 
 index=abc client1 OR client2 earliest=-7d@d latest=-0d@d 
| eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) 
| chart count as "curent Week" over CustomerName ]
| eval perc=round(('current Week'/'Last Week')*100,1)

Option 3) Use summary index for each sub search if time duration pulls too many events:
Option 4) Reduce Time duration of each search to reduce total matched events:
*Option 5) Modify the limits: * http://docs.splunk.com/Documentation/Splunk/latest/Search/Aboutsubsearches#Subsearch_performance_con...

[raviteja029]

I just tried Option 1 and it worked!

Thank you very much for the time and effort.

for Reference the output looks like -

CustomerName . | . Last Week . | . curent Week | . Perc |
USER1 | . 87204 | 53960 . | 61.88 . |
USER2 . | 147738 | 64848 | 43.89 . |

[niketn]

[UPDATED] Complete query added.

---

@raviteja029, just pipe the following eval to calculate percent towards the end of your search.

index=abc client1 OR client2 earliest=-14d@d latest=-7d@d | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) | eval weeknum="Last Week" 
| append [ search 
index=abc client1 OR client2 earliest=-7d@d latest=-0d@d | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) | eval weeknum="curent Week" ]
| chart count over CustomerName by weeknum
| eval perc=round(('current Week'/'Last Week')*100,1)

[raviteja029]

How do I complete the statement? I mean % should also print in the summary

index=abc client1 OR client2 earliest=-14d@d latest=-7d@d | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) | eval weeknum="Last Week"
| append [ search
index=abc client1 OR client2 earliest=-7d@d latest=-0d@d | eval CustomerName=case( CLIENT== "client1","USER1",CLIENT== "client2","USER2", 1==1, CLIENT) | eval weeknum="curent Week" ]
| eval perc=round(('current Week'/'Last Week')*100,1) | chart count over CustomerName by weeknum AND perc ?

[niketn]

@raviteja029, I meant the | eval perc statement to be the final pipe. So it should come after chart command not before. I have updated the answer with complete query. Please try.

[raviteja029]

I don't know for what reason the values popping up for curent Week are not coming right for the above search