help on where command which returns wrong results

[jip31]

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

[FrankVl · Accepted solution]

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

[jip31]

Yes it works franck 😉 thanks!

[FrankVl]

Nice 🙂

I've converted my comment to an answer.

[vnravikumar]

Hi

Whether HealthState00 is a numeric field?

[jip31]

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

[FrankVl]

What if you do where HealthState < 85

[jip31]

I have no results...

[FrankVl]

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

[jip31]

always no results franck

[FrankVl]

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

[jip31]

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