I'm using the following search using Splunk 4.2.1:
sourcetype=somesourcetype (tag=Metric AND tag=ResponseTime) NOT tag=Page earliest=-20d | eval upperHost=upper(host) | lookup metrics_lookup Metric as eventtype output ExpectedSLA | lookup cluster_lookup host as upperHost output cluster | search elapsedTime > ExpectedSLA | table cluster, host, eventtype, ExpectedSLA, elapsedTime | sort elapsedTime desc
Both elapsedTime and ExpectedSLA are numeric, yet the search function is not working properly as it does not return any data.
search(elapsedTime < ExpectedSLA) returns all rows, even the ones where elapsedTime is greater than ExpectedSLA.
search(elapsedTime < 200) works as expected, and seach(ExpectedSLA > 200) works as expected; they just don't work together!
Any ideas?
Well you should try the 'where' command instead of 'search'. 'where' sees unquoted strings as field names on the right-hand-side of expressions, whereas 'search' sees them as literals.
For example:
| search foo=bar
will check the foo field against the literal "bar", whereas
| where foo=bar
will check whether the foo field equals the bar field. Something similar could well be happening with the > operator. (In effect it would be checking whether the elapsedTime field alphabetized after the string constant "ExpectedSLA".... )
The other idea is that eventtype is a multivalued field technically (even if there's only one value), so if you have more than one eventtype this might mean that ExpectedSLA comes out as a multivalued field, and the comparator gets confused.
maybe try a " | nomv eventtype", or a "| mvexpand eventtype" before the lookup?
Well you should try the 'where' command instead of 'search'. 'where' sees unquoted strings as field names on the right-hand-side of expressions, whereas 'search' sees them as literals.
For example:
| search foo=bar
will check the foo field against the literal "bar", whereas
| where foo=bar
will check whether the foo field equals the bar field. Something similar could well be happening with the > operator. (In effect it would be checking whether the elapsedTime field alphabetized after the string constant "ExpectedSLA".... )
The other idea is that eventtype is a multivalued field technically (even if there's only one value), so if you have more than one eventtype this might mean that ExpectedSLA comes out as a multivalued field, and the comparator gets confused.
maybe try a " | nomv eventtype", or a "| mvexpand eventtype" before the lookup?
Thanks Nick - using the where command did the trick!