Splunk Search

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

shiv1593
Communicator

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:

.alt text

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.

Tags (1)
1 Solution

mayurr98
Super Champion

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!

View solution in original post

paramagurukarth
Builder

mayurr98
Super Champion

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
Communicator

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?

0 Karma

mayurr98
Super Champion

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

0 Karma

shiv1593
Communicator

Did the same. Thanks for the help.

0 Karma

mayurr98
Super Champion

can you put your code in 101010 format?

0 Karma

shiv1593
Communicator
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"
0 Karma

mayurr98
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

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