Splunk Search

addtotals to calculate percentage

davidcraven02
Communicator

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.

alt text

`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
1 Solution

493669
Super Champion

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 

View solution in original post

mayurr98
Super Champion

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
Communicator

This offers a slight different solution which also works! Thanks

0 Karma

493669
Super Champion

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
Communicator

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?

0 Karma

493669
Super Champion

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

0 Karma

davidcraven02
Communicator

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
0 Karma

FrankVl
Ultra Champion

Filter for the OS of choice after evaluating the percentages.

0 Karma

493669
Super Champion

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
0 Karma

FrankVl
Ultra Champion

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
Communicator

Thanks. How do I build this into my search?

0 Karma

FrankVl
Ultra Champion

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...