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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...