Calculate percentage in every row | adding two search results into one

[shiv1593]

I am fairly new to Splunk and I have a Two fold question. I am running a query to find the top issues reported in the last three months. Below is my query:

host="service_desk_tickets" Region=AMER "Sub Category"=* extracted_Source=* Created="" Summary="" Number="*"
| stats count(Number) AS "Number Of Tickets" BY extracted_Source Summary
| chart sum("Number Of Tickets") OVER Summary BY extracted_Source
| rename Summary AS "Top Issues Reported By Clients"
| addtotals
| sort - Total
| addcoltotals labelfield="Top Issues Reported By Clients"

Here is the resultant of it:

.

My Questions are:

1> How can I add another Column called Percentage and calculate the average percentage of the total in terms of the total number of events. Example: Row 1 has a total of 1024, so 1024 is 13.646% of 7504 (My total number of events)

2> Can I change something in my query to add combine Row 5 and Row 7 into one row, Row 1, 2, 4, into one row, naming it as a new row and add their totals into one, as they are pretty much the same issue, password reset or account unlock. If possible. I have heard that once indexed, data fields can't be added into one.

[paramagurukarth]

use streamstats

[mayurr98 · Accepted solution]

okay to solve your first query

you can try

host="service_desk_tickets" Region=AMER "Sub Category"= extracted_Source= Created="" Summary="" Number="*"
| stats count(Number) AS "Number Of Tickets" BY extracted_Source Summary
| chart sum("Number Of Tickets") OVER Summary BY extracted_Source
| rename Summary AS "Top Issues Reported By Clients"
| addtotals
| sort - Total
| eventstats sum(Total) as Total1
| eval pecentage=Total1*100/Total
| addcoltotals labelfield="Top Issues Reported By Clients" | fields - Total1

Now for your second query, in order to combine multiple count you can use replace command. let suppose you want to combine AD Password reset and AD accound unlock into a value called Reset....you can replace multiple values like that..I am just showing you how to do with two values..you can do the same for multiple values like that

then you can change your query as

 host="service_desk_tickets" Region=AMER "Sub Category"= extracted_Source= Created="" Summary="" Number="*"
| replace "AD Password reset" WITH "Reset" "AD accound reset" WITH "Reset" IN Summary 
| stats count(Number) AS "Number Of Tickets" BY extracted_Source Summary
 | chart sum("Number Of Tickets") OVER Summary BY extracted_Source
 | rename Summary AS "Top Issues Reported By Clients"
 | addtotals
 | sort - Total
 | eventstats sum(Total) as Total1
 | eval pecentage=Total1*100/Total
 | addcoltotals labelfield="Top Issues Reported By Clients" | fields - Total1

Let me know if this helps!

[shiv1593]

Thanks a lot Mayur. Your solution works perfectly, with a change. The correct Percentage will be calculated by eval pecentage=Total*100/Total1. Apart from that, it is perfect.

Replace command is working wonders too, but I have a lot of fields to combine, which might make the query way too long. Is there an alternative, where I can use specific keywords and combine the data, like by using Password reset, or Account unlock and combine whichever field have these keywords?

[mayurr98]

you can use wildcard operator * . like | replace *reset* WITH Reset IN Summary
let me know if this helps !

[shiv1593]

Did the same. Thanks for the help.

[mayurr98]

can you put your code in 101010 format?

[shiv1593]

host="service_desk_tickets" Region=AMER "Sub Category"= extracted_Source= Created="" Summary="" Number="*"
| stats count(Number) AS "Number Of Tickets" BY extracted_Source Summary
| chart sum("Number Of Tickets") OVER Summary BY extracted_Source
| rename Summary AS "Top Issues Reported By Clients"
| addtotals
| sort - Total
| addcoltotals labelfield="Top Issues Reported By Clients"

[mayurr98]

I have updated my query check my solution and let me know if it helps!