topic Re: how to calculate percentage in Splunk Search

how to calculate percentage

Laya123 — Tue, 29 Sep 2020 07:01:10 GMT

Hi,

Can anyone help how to calculate percentage for the report below for '%Act_fail_G_Total'

host Act-Sucess Act-Fail Pub-Sucess Laun-Sucess Total %Act-fai_Total %Act_fail_G_Total
A 1 1 1 1 4 25 50
B 2 0 3 2 7 0 0
C 1 1 2 4 8 12.5 50

D 3 0 1 1 5 0 0
G_Total 7 2 7 8 24 8.3 100

Using the search below, I am able to get the report till %Act-fai_GTotal, but I am unable to get the %Act_fail_Total

Search:

|inputlookup _AAD.csv |where Status="Act" OR Status="Pub" OR Status="Laun" OR Status="Fail"|eval app_status = AAtype . ":" . Status | chart count over host by app_status usenull=f| addtotals | addcoltotals labelfield=host label=G_Total|foreach *Activate:Failed [eval  failed_by_total_Publish = '<<FIELD>>' * 100 / Total]

Can anyone help me to do this?

Thanks in advance

Re: how to calculate percentage

woodcock — Thu, 20 Aug 2015 00:16:43 GMT

I do not think anyone will make an effort to try to answer this question until you give us some sample raw data.

Re: how to calculate percentage

Laya123 — Tue, 29 Sep 2020 07:05:29 GMT

Sorry for the inconvenience

here is the raw data

host Act type STATUS
A Activate Failed
B Publish Success
A Activate Failed
C Activate Failed
B Activate Success
A Activate Success
C Launch Success
B Activate Success

I want my report like

host Activate Publish Launch Grand % of Activate failed % of Activate failed
Success Failed Success Failed Success Failed Total upon GrandTotal upon Total
A 1 2 0 0 0 0 3 66.7 66.7
B 2 0 1 0 0 0 3 0 0.0
C 0 1 0 0 1 0 2 50 33.3
Total 3 3 1 0 1 0 8 37.5 100

Using the search below, I am able to get the report till '% of Activate failed upon GrandTotal', but I am unable to get the % of Activate failed upon Total

Query:

|inputlookup _AAD.csv |where Status="Act" OR Status="Pub" OR Status="Laun" OR Status="Fail"|eval app_status = AAtype . ":" . Status | chart count over host by app_status usenull=f| addtotals | addcoltotals labelfield=host label=G_Total|foreach *Activate:Failed [eval failed_by_total_Publish = '<>' * 100 / Total]

Hope now i have given proper information

Thanks in advance

Re: how to calculate percentage

somesoni2 — Tue, 29 Sep 2020 07:05:40 GMT

What should be the formula/value for %Act_fail_G_Total', based on your sample data??

Re: how to calculate percentage

woodcock — Thu, 20 Aug 2015 22:28:21 GMT

It is closer but I have more questions than ever:

I do not see a field AAtype in your raw data.
I have no idea how to read the header-line (field-names) of your desired report (I think because some/all of the names have newlines in them).

Re: how to calculate percentage

Laya123 — Tue, 29 Sep 2020 07:03:13 GMT

Thank you so much for your response.

AA type is Acttype in my raw data

I want my report like

Host Activate_Success Activate_Failed Publish_Success Publish_Failed Total '%failed'
A 3 2 1 0 6

B 0 1 1 0 2
C 2 0 2 1 5

Total 5 3 4 5 13
I want to calculate *%failed from 'Activate_failed' Host wise from the above example total Activate_Failed cases are 3 and i want percentage of each Host like A-66.6% and For B-33.3% and for C-0.0% Total-100%

I hope this make sense

Thanks in advance

Re: how to calculate percentage

tom_frotscher — Fri, 21 Aug 2015 08:44:53 GMT

Hi,

i think you want something like this:

| stats count by host "Act type" STATUS | eval new_field = 'Act type'." ".'STATUS' | chart max(count) by host new_field | fillnull | addtotals | eval perc_failed = 'Activate Failed' / Total  * 100

Here is a run every example, just copy and past it in your search bar:

| stats count | eval host="A" | eval "Act type"="Activate" | eval STATUS="Failed" | append[| stats count | eval host="B" | eval "Act type"="Publish" | eval STATUS="Success"] | append[| stats count | eval host="A" | eval "Act type"="Activate" | eval STATUS="Failed"] | append[| stats count | eval host="C" | eval "Act type"="Activate" | eval STATUS="Failed"] | append[| stats count | eval host="B" | eval "Act type"="Activate" | eval STATUS="Success"] | append[| stats count | eval host="A" | eval "Act type"="Activate" | eval STATUS="Success"] | append[| stats count | eval host="C" | eval "Act type"="Launch" | eval STATUS="Success"] | append[| stats count | eval host="B" | eval "Act type"="Activate" | eval STATUS="Success"] | table host "Act type" STATUS | stats count by host "Act type" STATUS | eval new_field = 'Act type'." ".'STATUS' | chart max(count) by host new_field | fillnull | addtotals | eval perc_failed = 'Activate Failed' / Total  * 100

Re: how to calculate percentage

Laya123 — Tue, 29 Sep 2020 07:06:11 GMT

Hi,

Thanks for your response. That I got it with my query; but I want to add one more column with %Failed_Active. I want to take the percentage with same column

example: if Activate Failed having the values like ('------' given for separating the fields)

host -------- Activate Failed ------ % Failed_Active
A ------------- 2 --------------------------- 33.3 (formula to get this percent is 2/6*100)
B ------------- 3 ---------------------------50.0 (formula to get this percent is 3/6*100)
C ------------- 1 ---------------------------16.7 (formula to get this percent is 1/6*100)
D ------------- 0 --------------------------- 0.0 ( formula to get this percent is 0/6*100)
Total ------------- 6 ----------------------------100 (formula to get this percent is 6/6*100)

There is other columns also with this report, but I am getting all those but I am unable to calculate this percentage.

Thank you

Re: how to calculate percentage

tom_frotscher — Fri, 21 Aug 2015 14:23:55 GMT

Hi,

use eventstats to add the total number of "Activate Failed" to every event:

| stats count | eval host="A" | eval "Act type"="Activate" | eval STATUS="Failed" | append[| stats count | eval host="B" | eval "Act type"="Publish" | eval STATUS="Success"] | append[| stats count | eval host="A" | eval "Act type"="Activate" | eval STATUS="Failed"] | append[| stats count | eval host="C" | eval "Act type"="Activate" | eval STATUS="Failed"] | append[| stats count | eval host="B" | eval "Act type"="Activate" | eval STATUS="Success"] | append[| stats count | eval host="A" | eval "Act type"="Activate" | eval STATUS="Success"] | append[| stats count | eval host="C" | eval "Act type"="Launch" | eval STATUS="Success"] | append[| stats count | eval host="B" | eval "Act type"="Activate" | eval STATUS="Success"] | table host "Act type" STATUS | stats count by host "Act type" STATUS | eval new_field = 'Act type'." ".'STATUS' | chart max(count) by host new_field | fillnull | addtotals | eval perc_failed = 'Activate Failed' / Total  * 100 | eventstats sum("Activate Failed") as sum_failed | eval "% Failed Active" = 'Activate Failed' / 'sum_failed' * 100 | fields - sum_failed | addcoltotals labelfield=host label="Total" "Activate Failed" "% Failed Active"

Re: how to calculate percentage

Laya123 — Fri, 21 Aug 2015 15:53:08 GMT

Thank you so much; its working