topic Re: Calculate percentage in every row | adding two search results into one in Splunk Search

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

shiv1593 — Tue, 29 Sep 2020 17:43:40 GMT

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.

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

mayurr98 — Tue, 16 Jan 2018 11:09:05 GMT

can you put your code in 101010 format?

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

mayurr98 — Tue, 16 Jan 2018 11:16:40 GMT

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!

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

paramagurukarth — Tue, 16 Jan 2018 11:43:28 GMT

use streamstats

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

shiv1593 — Tue, 16 Jan 2018 11:46:31 GMT

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"

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

mayurr98 — Tue, 16 Jan 2018 11:51:13 GMT

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

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

shiv1593 — Tue, 16 Jan 2018 12:47:02 GMT

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?

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

mayurr98 — Tue, 16 Jan 2018 12:54:55 GMT

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

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

shiv1593 — Tue, 16 Jan 2018 15:04:02 GMT

Did the same. Thanks for the help.