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?
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.
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.
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:
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!
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.
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.
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.
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!
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!
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
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.