Timechart WHERE clause not behaving as expected

[jluxenberg]

In the file /var/log/server.log, we have one log line each time a host sends a heartbeat to our service.

I've got the following Splunk search query: source="/var/log/server.log" POST /heartbeat | timechart limit=10000 span=1h count by server_ip

The "limit" clause is required otherwise if there are more than about 10 hosts, they all get lumped into the "OTHER" category. Besides the limit issue (is this scalable for many many hosts?) it works great, it gives me the number of log lines per host per hour.

However, I'd like to select hours where this total count is 0 for a host, because that indicates a problem with that host. I tried the following: source="/var/log/server.log" POST /heartbeat | timechart limit=10000 span=1h count by server_ip where count = 0

But that doesn't parse, and if I do this: source="/var/log/server.log" POST /heartbeat | timechart limit=10000 span=1h count by server_ip where count < 5

it gives the same result as not using the "where" clause. What am I doing wrong?

[steveyz · Accepted solution]

try

source="/var/log/server.log" POST /heartbeat
| timechart limit=10000 span=1h count by server_ip
| untable _time server_ip count
| search count=0

The purpose of the 'untable' is to demux your data such that you have one count per hour per server_ip, which is necessarily if you are trying to isolate host/hour combination that have no data.

[gajananh999]

But this will not show Server_IP on right side of the graph as agenda and x-axis values will get also changed it will show only _time not the actual value how it shows in timechart

[jrodman]

I suspect your use of limit=100000 is defeating the where clause.