topic Re: addtotals to calculate percentage in Splunk Search

addtotals to calculate percentage

davidcraven02 — Mon, 22 Jan 2018 15:58:13 GMT

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

Re: addtotals to calculate percentage

FrankVl — Mon, 22 Jan 2018 16:16:40 GMT

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

Re: addtotals to calculate percentage

davidcraven02 — Mon, 22 Jan 2018 16:21:46 GMT

Thanks. How do I build this into my search?

Re: addtotals to calculate percentage

FrankVl — Mon, 22 Jan 2018 16:32:20 GMT

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.

Re: addtotals to calculate percentage

493669 — Mon, 22 Jan 2018 17:04:49 GMT

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

Re: addtotals to calculate percentage

mayurr98 — Mon, 22 Jan 2018 17:16:42 GMT

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!

Re: addtotals to calculate percentage

davidcraven02 — Tue, 23 Jan 2018 07:48:46 GMT

This offers a slight different solution which also works! Thanks

Re: addtotals to calculate percentage

davidcraven02 — Tue, 23 Jan 2018 07:51:33 GMT

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?

Re: addtotals to calculate percentage

493669 — Tue, 23 Jan 2018 07:58:30 GMT

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

Re: addtotals to calculate percentage

davidcraven02 — Tue, 23 Jan 2018 09:10:22 GMT

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

Re: addtotals to calculate percentage

FrankVl — Tue, 23 Jan 2018 09:25:40 GMT

Filter for the OS of choice after evaluating the percentages.

Re: addtotals to calculate percentage

493669 — Tue, 23 Jan 2018 09:31:19 GMT

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