Dashboards & Visualizations

% of total

schou87
Path Finder

hi I have a table as shown below. I want to get the % of total for each status for previous 6 days. How do i write a query to get the same.

DATEStatus_1Status_2Status_3
2021-05-191433123
2021-05-184512456
2021-05-1746213
2021-05-1658564
2021-05-1549987
2021-05-1440543

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _* linecount
| fields - DATE
| addtotals
| addcoltotals labelfield=Result label=Total
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where Result="Total"
| fields - Percentage_Total
| fields Percentage_*

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| eventstats sum(Status_*) as Total_*
| foreach Status_*
    [| eval Percentage_<<MATCHSTR>>=100*<<FIELD>>/Total_<<MATCHSTR>>]
0 Karma

schou87
Path Finder

Hi,

I am getting an error  : Unencoded <

Also, can we please consider the below:

Status_1: A

Status_2: B

Status_3: C

I don't want to use matchstring as there is no wildcard match in my example. That was just an example.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _raw linecount
| eval _time=DATE
| fields - DATE
| eventstats sum(*) as Total_*
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total_<<FIELD>>]
| rename _time as DATE
0 Karma

schou87
Path Finder

Hi, your query gives me % total for each field individually on daily basis but can you tell me how to do a % of total for previous 6 days together and not calculate day on day basis. So I just want to return 3 rows for % total of my 3 fields.

 

Like % A=  A/A+B+C *100 ( for all 6 days together and not by individual dates)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _raw linecount
| eval _time=DATE
| fields - DATE
| addtotals
| stats sum(*) as *
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]

You can remove Percentage_Total as this will always be 100

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Alternatively

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _raw linecount
| eval _time=DATE
| fields - DATE
| addcoltotals 
| addtotals
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where isnull(_time)
| fields - _time
0 Karma

schou87
Path Finder

Hi @ITWhisperer , thanks for that!! But it is returning 8 rows where i just want below (numbers are hypothetical)

A    96.51

B     3.43

C     0.05

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

8 rows? There should only be one row where the date is null - this is the row generated by the addcoltotals - if you want to be more specific you could do this

| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _* linecount
| fields - DATE
| addtotals
| addcoltotals labelfield=Result label=Total
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where Result="Total"
0 Karma

schou87
Path Finder

I got your point on the ISNULL. Sorry I missed that part.

But the result is giving me extra columns which i dont want

A B C Percentage_A Percentage_B Percentage_C Percentage_Total Total

766828862.50825082508250842.244224422442244395.247524752475241003030

 

I just want to keep the percentage values 

Percentage_A Percentage_B Percentage_C

2.50825082508250842.244224422442244395.24752475247524
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="DATE	A	B	C
2021-05-19	14	33	123
2021-05-18	45	12	456
2021-05-17	4	6	213
2021-05-16	5	8	564
2021-05-15	4	9	987
2021-05-14	4	0	543"
| multikv forceheader=1
| fields - _* linecount
| fields - DATE
| addtotals
| addcoltotals labelfield=Result label=Total
| foreach *
    [| eval Percentage_<<FIELD>>=100*<<FIELD>>/Total]
| where Result="Total"
| fields - Percentage_Total
| fields Percentage_*

schou87
Path Finder

Awesome!! Thank you @ITWhisperer for your patience with me. 🙂 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...