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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...