I have a query that works, but the output calculates a percentage column in a chart. I need to show the total of TAM and the correct percentage value for all the returned rows. I'm using this:
| ...
See more...
I have a query that works, but the output calculates a percentage column in a chart. I need to show the total of TAM and the correct percentage value for all the returned rows. I'm using this:
| inputlookup Patch-Status_Summary_AllBU_v3.csv | stats count(ip_address) as total, sum(comptag) as compliant_count by BU | eval patchcompliance=round((compliant_count/total)*100,1) | fields BU total compliant_count patchcompliance | rename BU as Domain, total as TAM, patchcompliance as "% Compliance" | appendpipe [stats sum(TAM) as TAM sum(compliant_count) as compliant_count | eval totpercent=round((comp/TAM)*100,1)] | eval TAM = tostring(TAM, "commas")
The output is:
Domain TAM compliant_count % Compliance
BU1
1,180
1146
97.1
BU2
2,489
2420
97.2
BU3
409,881
96653
23.6
BU4
3
3
100.0
BU5
1,404
1375
97.9
BU6
119,003
90100
75.7
BU7
33,506
30669
91.5
BU8
2,862
1997
69.8
BU9
239,897
216401
90.2
BU10
3,945
3832
97.1
BU11
569
482
84.7
814,739
445078
If I add to the appendpipe stats command avg("% Compliance") as "% Compliance" then it will not take add up the correct percentage which in this case is "54.6" but the average would display "87.1".
How do I calculate the correct percentage as a total using the totals of columns TA