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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...