I have the following search:
index="automox" sourcetype="automox:devices" server_group="Windows Server Pilot"
| 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
| where pending="false"
| sort -percent
| rename server_group AS "Server Group", count AS "Devices", percent AS "Patched Percent"
| table "Server Group", "Patched Percent"
What I want to do is when there are no events with "pending=false" , I would like this to be reflected in the stats table as 0%. This is for patch compliance which is why I would like this.
I tried using fillnull but I haven't managed to get it working.
Any help would be appreciated.
Hi @FraserC1,
the problem is to know the list of servers to monitor.
The easiest way is to create a lookup (called e.g. perimeter.csv ) with this list (at least one field called server_group).
Lookup that you can update automatically with a scheduled search or manually.
Then you have to add some rows to your search:
index="automox" sourcetype="automox:devices" server_group="Windows Server Pilot"
| 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
| where pending="false"
| eval server_group=lower(server_group)
| append [ | inputlookup perimeter.csv | eval server_group=lower(server_group), percent=0 | fields server_group percent ]
| stats sum(percent) AS percent BY server_group
| sort -percent
| rename server_group AS "Server Group", count AS "Devices", percent AS "Patched Percent"
| table "Server Group", "Patched Percent"
Only one question: how can you have more server_groups if you filter your results in the main search with one server_group?
Ciao.
Giuseppe
Hi,
I should have said that the search is usually "server_group=*", I am just using this group because it is the offending group that is giving me problems.
I don't really understand the point of creating a lookup for this, the search already returns the servers that are being monitored.
Hi @FraserC1,
if the search returns all the group_servers you haven't any problem and don't need any add-on to you search, you have all the resuolts.
My solution is to solve the situation when some group_server is missing (and I understood that this is your problem, if I'm wrong, sorry!), but to do this you have to know the exact list of group_servers to monitor.
Ciao.
Giuseppe
This is a common scenario - trying to find something that doesn't exist in your data. in your case, you could append some "dummy" events with count=0 possibly for all the event types, then add these to your existing events.
Thanks, I think I get what you mean but not sure how to implement it. I will do some googling and see what I can find.
Try this:
index="automox" sourcetype="automox:devices" server_group="Windows Server Pilot"
| dedup name
| top pending, server_group
| appendpipe [ stats values(pending) as pending by server_group | eval count=0, percent=0, pending="false" ]
| stats sum(count) as count by pending, server_group
| eventstats sum(count) as total by server_group | eventstats sum(count) as total by server_group
| eval percent=round(count*100/total,2)
| fields - total
| where pending="false"
| sort -percent
| rename server_group AS "Server Group", count AS "Devices", percent AS "Patched Percent"
| table "Server Group", "Patched Percent"
This should add a "dummy" event with pending="false" for every server_group with count of 0, then add the 0 to any existing pending="false" counts, before you calculate the total for the server_group (I removed the fields - percent because that should not get past the stats, in any event you are overriding it with the eval)
| stats count
Absolutely useless.
I am assuming you didn't even read the question.
Try adding this to your search
| appendpipe [stats count|where count==0|eval "Patched Percent"=0]
Thanks for this, I tried it but it doesn't give me what I want.
I think the problem is because no events are returned because of "pending=false"