Splunk Search

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

splunker9999
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

sundareshr
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

splunker9999
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

sundareshr
Legend

Sure, add this to the end

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

splunker9999
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

sundareshr
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

somesoni2
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

splunker9999
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

somesoni2
SplunkTrust
SplunkTrust

My bad.. Just corrected.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...