Why am I getting different results between these 2 searches?

[HeinzWaescher]

Hi,
I'm using a search like this for a timerange of one single day:

sourcetype=A
| lookup lookup.csv id OUTPUT timestamp
| bucket span=1d _time
| eval flag=if(timestamp<=_time, "true", "false")
| stats dc(id) AS ids by flag

 true -> 50
 false -> 10

I tried out another way to verify the distinct count of ids for flag=true, but the result is different:

sourcetype=A
| lookup lookup.csv id OUTPUT timestamp
| bucket span=1d _time
| search timestamp<=_time
| stats dc(id) AS ids

result -> 60

I can't find the reason for different results here.

Best regards

Heinz

edit:

The "search" command in the second example seems to be the reason. The result is 50 by using "where timestamp<=_time".
Can somebody tell me the difference of search and where here?

[MuS · Accepted solution]

Hi HeinzWaescher,

The search command has two functions: used at the beginning of a search pipeline, it retrieves events from an index(es); used elsewhere in the pipeline, it filters the results of a previous search command.

The where command also filters the results of a previous search, but it uses the same expression syntax as the eval command and keeps only the results for which the evaluation was successful.

In other words, use where to filter/search/compare two fields and use search if you want to filter/search a specific value of some field.

hope this helps ...

cheers, MuS

[HeinzWaescher]

Thanks, I will keep that in mind!

[pedromvieira]

Your timestamp field isnt numeric.

From search documentation:
http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Search

Comparison expression
<cmp>
Syntax: = | != | < |  | >=
Description: Comparison operators. You can use comparison expressions when searching field/value pairs. Comparison expressions with "=" and "!=" work with all field/value pairs. **Comparison expressions with  = work only with fields that have numeric values**.

[HeinzWaescher]

Hm, why is this not a numeric value? Calculatiions work fine, e.g.

| eval sum=timestamp+_time

[HeinzWaescher]

I've done another check which tells me that the both fields are numeric:

| eval isnum=if(isnum(timestamp), "true", "false") -> true
| eval isnum2=if(isnum(_time), "true", "false") -> true