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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...