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

[splunker9999]

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

[sundareshr]

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

[splunker9999]

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

[sundareshr]

Sure, add this to the end

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

[splunker9999]

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?

[sundareshr]

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

[somesoni2]

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 

OR

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

[splunker9999]

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?

[somesoni2]

My bad.. Just corrected.