Splunk Search

Show 0% if no results are found

Path Finder

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.

Labels (1)
0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Builder

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.

0 Karma

Path Finder

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.

0 Karma

Builder

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)

0 Karma

Super Champion
| stats count

 

————————————
If this helps, give a like below.
0 Karma

Path Finder

Absolutely useless.

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

0 Karma

SplunkTrust
SplunkTrust

Try adding this to your search

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

Path Finder

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"

0 Karma
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!