Show 0% if no results are found

[FraserC1]

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.

[gcusello]

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

[FraserC1]

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.

[gcusello]

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

[ITWhisperer]

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.

[FraserC1]

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.

[ITWhisperer]

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)

[thambisetty]

| stats count

[FraserC1]

Absolutely useless.

I am assuming you didn't even read the question.

[renjith_nair]

Try adding this to your search

| appendpipe [stats count|where count==0|eval "Patched Percent"=0]

[FraserC1]

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"