topic Re: Why am I getting different results between these 2 searches? in Splunk Search

Why am I getting different results between these 2 searches?

HeinzWaescher — Thu, 18 Dec 2014 09:25:58 GMT

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?

Re: Why am I getting different results between these 2 searches?

pedromvieira — Thu, 18 Dec 2014 13:34:36 GMT

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**.

Re: Why am I getting different results between these 2 searches?

HeinzWaescher — Fri, 19 Dec 2014 07:52:58 GMT

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

| eval sum=timestamp+_time

Re: Why am I getting different results between these 2 searches?

HeinzWaescher — Mon, 22 Dec 2014 09:32:34 GMT

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

Re: Why am I getting different results between these 2 searches?

MuS — Mon, 22 Dec 2014 10:42:40 GMT

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

Re: Why am I getting different results between these 2 searches?

HeinzWaescher — Tue, 23 Dec 2014 12:17:22 GMT

Thanks, I will keep that in mind!