Archive

help on where command which returns wrong results

Contributor

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
0 Karma
1 Solution

Ultra Champion

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

View solution in original post

0 Karma

Ultra Champion

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

View solution in original post

0 Karma

Contributor

Yes it works franck 😉 thanks!

0 Karma

Ultra Champion

Nice 🙂

I've converted my comment to an answer.

0 Karma

Champion

Hi

Whether HealthState00 is a numeric field?

0 Karma

Contributor

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

0 Karma

Ultra Champion

What if you do where HealthState < 85

0 Karma

Contributor

I have no results...

0 Karma

Ultra Champion

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

Contributor

always no results franck

0 Karma

Ultra Champion

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

Contributor

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!