Splunk Search

Find percentage of two numbers

Path Finder

Hi,

 

I am trying to search through some patch data to find percentage of devices that have been patched against the total amount of machines.
The search I have is below:

index="automox" sourcetype="automox:devices"
| dedup name
| eval patch_pend=if(pending_patches>0, 1, 0)
| eval patched=if(pending_patches=0, 1, 0)
| stats sum(patch_pend) , count(name) AS total, sum(patched)

 

I first have to run an eval to find the amount of machines with pending patches more than 0, this gives me the filed patch_pend and then eval to get patched machines.

Then I used stats to get the total amount within patch_pend and also count names which is total amount of machines and finally get the total patched machines.

Then my thought was to do another eval similar to below:

| eval perc=round(patch_pend*100/total,2)

But what this gives me is just one full pie chart with the total at 100%.

So I have these three numbers:

Total amount of machines
machines with patches pending
Machines with no patches pending

What I want to show is the percentage of machines that have 0 patches.
Can anyone point me in the right direction to do this?

0 Karma
1 Solution

Builder

Hello @FraserC1 , this should work

| index="automox" sourcetype="automox:devices"
| dedup name
| eval patch_pend=if(pending_patches>0, 1, 0)
| eval patched=if(pending_patches=0, 1, 0)
| stats sum(patch_pend) as patch_pend , count(name) AS total, sum(patched) as patched
| eval %patch_pend=round((patch_pend*100/total),2)
| eval %patched=round((patched*100/total),2)
| fields %*
| transpose

 

Hope it helps!

Please upvote my response if it resolves this issue.

View solution in original post

Builder

Hello @FraserC1 , this should work

| index="automox" sourcetype="automox:devices"
| dedup name
| eval patch_pend=if(pending_patches>0, 1, 0)
| eval patched=if(pending_patches=0, 1, 0)
| stats sum(patch_pend) as patch_pend , count(name) AS total, sum(patched) as patched
| eval %patch_pend=round((patch_pend*100/total),2)
| eval %patched=round((patched*100/total),2)
| fields %*
| transpose

 

Hope it helps!

Please upvote my response if it resolves this issue.

View solution in original post

Path Finder

@Nisha18789 

This has done exactly what I want, thank you!

I wonder if you could help me with one other query?

It is in the same index and sourcetype, I've now been asked if I can show the same information for each server group.

The search I have is:

index="automox" sourcetype="automox:devices" server_group="*"
| dedup name
| stats count by pending, server_group
| eventstats sum(count) as perc
| eval perc=round(count*100/perc,2)
| search pending=false
| table pending, count, perc, server_group

However this just gives a percentage based on the amount of machines within each server group, which is not what I was looking for.

What I want is like below:

pendingcountpercserver_group
FALSE98.11Linux Servers
FALSE10.9Linux Workstations
FALSE43.6MacOS Workstations
FALSE3027.03Windows Server Full
FALSE43.6Windows Workstations

 

But the percentage is based on the total amount of machines, rather than how many exist within the server group.

As an example, if I run your search you provided with the extra bit, server_group="Windows Workstations", then the percentages it gives me are:

columnrow 1
%patch_pend90.48
%patched9.52

And this is what I would expect.

The reason I am trying to do this, is so that on a dashboard I can show a trellis view of each server group and the percentage of unpatched(or patched) machines.

I hope that makes sense and sorry for the wall of text!

0 Karma

Builder

Hi @FraserC1 ,  I am a little confused on what data pending field contains, but below query will give you an idea on how to do what you are looking for, let me know if you have any questions.

 

| index="automox" sourcetype="automox:devices" server_group="*"
| dedup name
| eval pending_servergrp="%"+pending+" : "+server_group
| stats count by pending_servergrp
| addcoltotals labelfield=pending_servergrp label=Total
| transpose 0 header_field=pending_servergrp
| fields - column
| foreach %*
[ eval %<<MATCHSTR>> = ROUND('%<<MATCHSTR>>'*100/Total,2) ]
| fields - Total
| transpose

 

Hope this helps!

Please upvote my response if it resolves this issue.

Path Finder

@Nisha18789 

Apologies I should have explained, the pending field is either "true" or "false", if true it means there are still patches to apply.

Thanks for the search query I will try it just now.

0 Karma

Path Finder

@Nisha18789 

So I tried the search but unfortunately it doesn't show me what I was looking for.
I am trying to use the first search you provided, but show those results for all of the server groups.

If I just use pending=true, that should only show me devices that have pending patches. And from there I would like to know the percentage of that number in relation to all the machines in the same server group.

Appreciate your help with this.

0 Karma

Builder

hi @FraserC1 , it would be simple then if I understand it correctly you can use this for the tabular data

index="automox" sourcetype="automox:devices" server_group="*"
| dedup name
|top pending, server_group
| where pending="false"
| addcoltotals labelfield=pending label=patchedServers
| eval percent=if(pending="patchedServers",100-percent,percent)
| eval count=if(pending="patchedServers","",count)

But if you need bar chart, you need to get rid of other columns , so you can below two lines after above query

| eval pending_servergrp=pending+ ":"+server_group

| fields - count pending server_group

 

Let me know if that helps!

Path Finder

Hi @Nisha18789 ,

I think this is almost it, the only problem is the percentages.
It would need to be a percentage of the total amount of unpatched machines in each server group rather than the current "100-percent" calculation it is doing.
I think the calculation would be:

unpatched_machines/total_machines_in_group*100

Thanks again for your help!

0 Karma

Builder

Hi @FraserC1 , my bad I misunderstood your requirement on the %, here is what I think should help

index="automox" sourcetype="automox:devices" server_group="*"
| dedup name
|top pending, server_group
| fields - percent
| eventstats sum(count) as total by server_group
| eval percent=round(count*100/total,2)
| fields - total
| sort server_group

and then you can use |where pending=false

 

Path Finder

Hi @Nisha18789 ,

 

Yup this is exactly what I was looking for.
Can't thank you enough for taking the time to help me with this!

 

Very much appreciated.

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!