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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...