topic Re: Find percentage of two numbers in Splunk Search

Find percentage of two numbers

FraserC1 — Thu, 20 Aug 2020 09:40:09 GMT

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?

Re: Find percentage of two numbers

Nisha18789 — Thu, 20 Aug 2020 10:20:45 GMT

Hello @FraserC1 , this should work

Hope it helps!

Please upvote my response if it resolves this issue.

Re: Find percentage of two numbers

FraserC1 — Thu, 20 Aug 2020 10:32:03 GMT

@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:

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:

pending	count	perc	server_group
FALSE	9	8.11	Linux Servers
FALSE	1	0.9	Linux Workstations
FALSE	4	3.6	MacOS Workstations
FALSE	30	27.03	Windows Server Full
FALSE	4	3.6	Windows 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:

column	row 1
%patch_pend	90.48
%patched	9.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!

Re: Find percentage of two numbers

Nisha18789 — Thu, 20 Aug 2020 11:06:25 GMT

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.

Hope this helps!

Please upvote my response if it resolves this issue.

Re: Find percentage of two numbers

FraserC1 — Thu, 20 Aug 2020 11:08:31 GMT

@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.

Re: Find percentage of two numbers

FraserC1 — Thu, 20 Aug 2020 11:21:27 GMT

@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.

Re: Find percentage of two numbers

Nisha18789 — Thu, 20 Aug 2020 11:58:32 GMT

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

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!

Re: Find percentage of two numbers

FraserC1 — Thu, 20 Aug 2020 13:17:19 GMT

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!

Re: Find percentage of two numbers

Nisha18789 — Thu, 20 Aug 2020 13:39:07 GMT

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

and then you can use |where pending=false

Re: Find percentage of two numbers

FraserC1 — Thu, 20 Aug 2020 14:22:59 GMT

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.