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
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
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
Yes it works franck 😉 thanks!
Nice 🙂
I've converted my comment to an answer.
Hi
Whether HealthState00
is a numeric field?
Yes this field is a numeric field with a point before the decimal and not a comma
What if you do where HealthState < 85
I have no results...
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
always no results franck
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
Here is the screenshot
https://www.cjoint.com/c/IGmkmAV0zHc