addtotals to calculate percentage

[davidcraven02]

I am trying to calculate what percentage of Operating Systems have windows 10 installed out of the total number which in this example is 174.

I have used a table as the easiest way to try and achieve this but ideally I want to display the final values as a single value. However, any solution would be really useful now.

`GEN_ProductionWorkstations` 
| join type=left machine 
    [ search index=ad source=otl_addnsscan 
    | eval machine=lower(name)] 
| rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
| rename data as IPAddress 
| search machine="*" 
| dedup machine 
| stats count(machine) by operatingSystem 
| addtotals col=t labelfield=totalOSCount label="osCount" fieldname="total" 
| fillnull value="Total Client Estate" operatingSystem
| fields- count(machine) Product totalOSCount

[mayurr98]

I think your requirement is something like this, also you need to customize your query some this commands have no useful purpose so you can just avoid that..

 `GEN_ProductionWorkstations` 
 | join type=left machine 
     [ search index=ad source=otl_addnsscan 
     | eval machine=lower(name)] 
 | rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
 | rename data as IPAddress 
 | search machine="*" 
 | dedup machine 
 | stats count(machine) as count by operatingSystem 
 | eventstats sum(count) as total_count
 |eval percentage=round(100*count/total_count,2)
| addcoltotals labelfield=operatingSystem  label=Total Client Estate"
 | fields- total_count 

let me know if this helps!

[davidcraven02]

This offers a slight different solution which also works! Thanks

[493669 · Accepted solution]

Hi @davidcraven02,
Try below:

 `GEN_ProductionWorkstations` 
 | join type=left machine 
     [ search index=ad source=otl_addnsscan 
     | eval machine=lower(name)] 
 | rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
 | rename data as IPAddress 
 | search machine="*" 
 | dedup machine 
 | stats count(machine) as count by operatingSystem 
 | eventstats sum(count) as total
| eval percentage = ((count/total)*100)
|eval percentage =percentage ."%"
|table operatingSystem , count , percentage 

[davidcraven02]

Thanks this works! How could this be tweaked to be used as a single value display to show the '% of OS's on Windows 10' for example. When I include the below it calculates it as 100% as the other OS's have been removed from the table.

 | search machine="*" operatingSystem="*10*"

Any ideas?

[493669]

have you tried to run this search at last of whole query?

[davidcraven02]

Yes. In a table format it works correct. But I want to display is as a single value i.e 80% which reflects the percentage of a particular OS.

I have tried the below, but it only works if Windows 10 is listed first in this table which make sit not reliable.

`GEN_ProductionWorkstations` 
  | join type=left machine 
      [ search index=ad source=otl_addnsscan 
      | eval machine=lower(name)] 
  | rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
  | rename data as IPAddress 
  | search $companyCode$ operatingSystem="*" OR NOT operatingSystem="*" 
  | fillnull value="No OS listed" operatingSystem
  | dedup machine 
  | stats count(machine) as count by operatingSystem 
  | eventstats sum(count) as total
 | eval percentage = ((count/total)*100)
 | eval percentage = round(percentage,2)
 |eval percentage =percentage ."%"
 |table percentage, operatingSystem , count , 
 |sort operatingSystem
 | fields-  count

[FrankVl]

Filter for the OS of choice after evaluating the percentages.

[493669]

yes as @FrankVl suggested | search $companyCode$ operatingSystem="*" OR NOT operatingSystem="*" should be after percentage evaluation
Try below:

 `GEN_ProductionWorkstations` 
   | join type=left machine 
       [ search index=ad source=otl_addnsscan 
       | eval machine=lower(name)] 
   | rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
   | rename data as IPAddress 
    | fillnull value="No OS listed" operatingSystem
   | dedup machine 
   | stats count(machine) as count by operatingSystem 
   | eventstats sum(count) as total
  | eval percentage = ((count/total)*100)
  | eval percentage = round(percentage,2)
  |eval percentage =percentage ."%"
  | search $companyCode$ operatingSystem="*" OR NOT operatingSystem="*" 
  |table percentage, operatingSystem , count , 
  |sort operatingSystem
  | fields-  count

[FrankVl]

I usually do that with a combination of eventstats (to add the total to each row) and eval (to divide row count by totals to get the percentage):

| eventstats sum(count) as totals
| eval percentage=100*count/totals

[davidcraven02]

Thanks. How do I build this into my search?

[FrankVl]

Add it after line 9 and adjust the field names to match what you have / want and see which bits of lines 10-12 you still need.