topic Re: Why is my search with a where condition not filtering results as expected? in Splunk Search

Why is my search with a where condition not filtering results as expected?

splunker9999 — Mon, 15 Aug 2016 20:54:03 GMT

Hi,

We have a search which gives us average CPU time by host and we want to plot a line graph to get hosts which have CPU usage greater than 25%.

Our search below is giving us results, but when we filter results like search host>25, it is filtering the results, but it is displaying other hosts during that time.

Ex: If we need to check CPU perc on *linux1, linux2, linux3, and we have CPU>25 for linux1 at 10 mins ago, we are getting results for other hosts as well.

Results we are getting like below, but we are looking only for >25 (linux1 here below)

  _time    linux1    linux2     linux3
  10.00    27        16         17
  10.10    30        18         20

Search:

index=idx1 sourcetype=cpu host=linux1* OR host=linux2* OR host=linux3*  all                | rex field=host "(?P<hostname>[^.]+)"             | eval hostname=lower(hostname) `linux_hostname`                       | eval linuxCPU=100 - pctIdle  | timechart span=10m avg(linuxCPU) as cpu by hostname  useother=f limit=0
|search linux3>25 OR linux2>25 OR linux1 >25

We should results like

_time     linux1
10.00     27
10.10     25

Re: Why is my search with a where condition not filtering results as expected?

somesoni2 — Mon, 15 Aug 2016 20:57:51 GMT

Give this a try
Updated

index=idx1 sourcetype=cpu host=linux1* OR host=linux2* OR host=linux3*  all 
| rex field=host "(?P<hostname>[^.]+)"
| eval hostname=lower(hostname) `linux_hostname` 
| eval linuxCPU=100 - pctIdle  
| bucket span=10m _time
| stats avg(linuxCPU) as cpu by _time hostname 
|search cpu >25

index=idx1 sourcetype=cpu host=linux1* OR host=linux2* OR host=linux3*  all 
| rex field=host "(?P<hostname>[^.]+)"
| eval hostname=lower(hostname) `linux_hostname` 
| eval linuxCPU=100 - pctIdle  
| bucket span=10m _time
| stats avg(linuxCPU) as cpu by _time hostname 
|search cpu >25 
| timechart span=10m avg(cpu) as cpu by hostname  useother=f limit=0

Re: Why is my search with a where condition not filtering results as expected?

sundareshr — Mon, 15 Aug 2016 20:58:25 GMT

Try this

index=idx1 sourcetype=cpu host=linux1* OR host=linux2* OR host=linux3*  all | rex field=host "(?P<hostname>[^.]+)"  | eval hostname=lower(hostname) `linux_hostname` | eval linuxCPU=100 - pctIdle  | timechart span=10m avg(linuxCPU) as cpu by hostname  useother=f limit=0 | untable _time hostname cpu | where cpu>25 | xyseries _time hostname cpu

Re: Why is my search with a where condition not filtering results as expected?

splunker9999 — Mon, 15 Aug 2016 21:27:00 GMT

Looks Good, Is there a way we can format time on X axis?

Currently this looks like 2016-08-15T00:05:00.000-07:00 is not in readable format? is there a way we can get this to readable for mat something like 8.30AM Mon Aug 15 2016

Re: Why is my search with a where condition not filtering results as expected?

sundareshr — Mon, 15 Aug 2016 21:30:10 GMT

Sure, add this to the end

... "| eval _time=strftime(_time, "%-H.%M%p %a %b %d %Y")

Re: Why is my search with a where condition not filtering results as expected?

splunker9999 — Mon, 15 Aug 2016 22:00:47 GMT

Thanks, and will this not work for the span (1d or 1h)? this is not retrieving any results if I change span to 1h or 1d to check 7days period?

Re: Why is my search with a where condition not filtering results as expected?

sundareshr — Mon, 15 Aug 2016 22:09:17 GMT

It should work for any span. You could test by removing everything from the untable command. Maybe there's nothing with >25?

Re: Why is my search with a where condition not filtering results as expected?

splunker9999 — Mon, 15 Aug 2016 22:10:50 GMT

These queries are working till stats command, but afterthat they are not retrieving results because we have filtered by linux1 ,linux2 and linux3 are values not fields?

Re: Why is my search with a where condition not filtering results as expected?

somesoni2 — Tue, 16 Aug 2016 00:13:56 GMT

My bad.. Just corrected.