search function not working properly when comparing lookup value

[johnboldt]

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?

[sideview · Accepted solution]

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?

[johnboldt]

Thanks Nick - using the where command did the trick!