Splunk Search
Highlighted

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

Path Finder

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

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

SplunkTrust
SplunkTrust

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
Highlighted

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

Path Finder

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?

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

My bad.. Just corrected.

0 Karma
Highlighted

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

Legend

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
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Legend

Sure, add this to the end

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

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

Path Finder

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?

0 Karma
Highlighted

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

Legend

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

0 Karma