topic Re: help on where command which returns wrong results in Splunk Search

help on where command which returns wrong results

jip31 — Thu, 11 Jul 2019 12:16:03 GMT

hello

I have an issue with the the tonumber command
When I execute the query below and even if I specify that I want (HealthState00 < "85.00") I have results <"85.00" and also results >"85.00"
So I use the tonnumber command below but it doesnt works....
I use the workaround AND NOT HealthState00 = "100.00" AND NOT HealthState00 = "125.01" AND NOT HealthState00 = "100.12") for displaying the good datas
Could you please tell me why the tonnumber command doesnt works??

| inputlookup tablet_host.csv 
| lookup PanaBatteryStatus.csv "Hostname00" as host OUTPUT HealthState00 
| where (HealthState00 < "85.00")
| lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE 
| search SITE=$tok_filtersite|s$  
| stats values(SITE) as SITE values(HealthState00) as HealthState by host 
| sort +HealthState limit=10

Re: help on where command which returns wrong results

vnravikumar — Fri, 12 Jul 2019 06:05:56 GMT

Whether HealthState00 is a numeric field?

Re: help on where command which returns wrong results

jip31 — Fri, 12 Jul 2019 06:34:20 GMT

Yes this field is a numeric field with a point before the decimal and not a comma

Re: help on where command which returns wrong results

FrankVl — Fri, 12 Jul 2019 07:06:00 GMT

What if you do where HealthState < 85

Re: help on where command which returns wrong results

jip31 — Fri, 12 Jul 2019 09:15:47 GMT

I have no results...

Re: help on where command which returns wrong results

FrankVl — Fri, 12 Jul 2019 09:21:03 GMT

Then your healthstate field is not a number. Try:

 | inputlookup tablet_host.csv 
 | lookup PanaBatteryStatus.csv "Hostname00" as host OUTPUT HealthState00 
 | eval HealthState00=tonumber(HealthState00)
 | where HealthState00 < 85
 | lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE 
 | search SITE=$tok_filtersite|s$  
 | stats values(SITE) as SITE values(HealthState00) as HealthState by host 
 | sort +HealthState limit=10

Re: help on where command which returns wrong results

jip31 — Fri, 12 Jul 2019 09:40:49 GMT

always no results franck

Re: help on where command which returns wrong results

FrankVl — Fri, 12 Jul 2019 09:46:52 GMT

What does this show (can you perhaps share a screenshot of that):

| inputlookup tablet_host.csv 
| lookup PanaBatteryStatus.csv "Hostname00" as host OUTPUT HealthState00 
| eval HealthState00_number=tonumber(HealthState00)
| table host HealthState00 HealthState00_number

Re: help on where command which returns wrong results

jip31 — Fri, 12 Jul 2019 10:12:51 GMT

Here is the screenshot
https://www.cjoint.com/c/IGmkmAV0zHc

Re: help on where command which returns wrong results

FrankVl — Fri, 12 Jul 2019 10:21:53 GMT

I clearly see , there in HealthState00 and the fact that it is left-aligned in that column indicates it is not a numeric value. Due to the , the tonumber also fails. Try this:

  | inputlookup tablet_host.csv 
  | lookup PanaBatteryStatus.csv "Hostname00" as host OUTPUT HealthState00 
  | eval HealthState00=tonumber(replace(HealthState00,",","."))
  | where HealthState00 < 85
  | lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE 
  | search SITE=$tok_filtersite|s$  
  | stats values(SITE) as SITE values(HealthState00) as HealthState by host 
  | sort +HealthState limit=10

Re: help on where command which returns wrong results

jip31 — Fri, 12 Jul 2019 11:20:44 GMT

Yes it works franck 😉 thanks!

Re: help on where command which returns wrong results

FrankVl — Fri, 12 Jul 2019 12:07:47 GMT

Nice 🙂

I've converted my comment to an answer.