Knowledge Management

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

raviteja029
Explorer

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

0 Karma
1 Solution

niketn
Legend

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...

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

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...

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

raviteja029
Explorer

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 . |

0 Karma

niketn
Legend

[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)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

raviteja029
Explorer

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 ?

0 Karma

niketn
Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

raviteja029
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...