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
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
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!
This offers a slight different solution which also works! Thanks
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
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?
have you tried to run this search at last of whole query?
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
Filter for the OS of choice after evaluating the percentages.
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
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
Thanks. How do I build this into my search?
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.