Splunk Search

calculate percentage from the results of two reports

jdlocklin526
Observer

I'm having a difficult time calculating a percentage based on two reports (searches).

Search 1
| inputlookup mydata.csv
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal

Search 2
| inputlookup mydata.csv
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total

I'd like to calculate a percentage
| eval percent=(myteamstotal/total)*100

I tried this and got 0 as a result:

| inputlookup GCC-SNOW_01012018_12312018.csv
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total
| eval percent=(myteamstotal/total)*100

How can I calculate the percentage?

0 Karma
1 Solution

adonio
Ultra Champion

maybe try | appendcols
something like this:

| inputlookup mydata.csv 
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| appendcols [ | inputlookup mydata.csv 
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total
| eval percent=(myteamstotal/total)*100

hope it helps

View solution in original post

0 Karma

adonio
Ultra Champion

maybe try | appendcols
something like this:

| inputlookup mydata.csv 
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| appendcols [ | inputlookup mydata.csv 
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total
| eval percent=(myteamstotal/total)*100

hope it helps

0 Karma

jdlocklin526
Observer

Thanks Adonio!

I just needed to close the brackets in the appendcols. Final query was:

| inputlookup mydata.csv
| where category= "category" AND assignment_group="myteam" AND yyyy_mm="$Datedropdown$"
| stats count as myteamstotal
| appendcols [ inputlookup mydata.csv
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count as total]
| eval percent=(myteamstotal/total)*100
| fields percent

As an intermediate step to check any issues, you can view fields percent, myteamstotal, total as a table.

0 Karma

HiroshiSatoh
Champion

Try this!

| inputlookup mydata.csv
| where category= "category" AND yyyy_mm="$Datedropdown$"
| stats count(eval(assignment_group="myteam")) as myteamstotal,count as total
| eval percent=(myteamstotal/total)*100

0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...