topic Re: Comparison of two date fields gives bad result in Splunk Search

Comparison of two date fields gives bad result

niamurph — Mon, 19 Jun 2017 07:40:38 GMT

I use the following query in an attempt to view a subset of the file test10UniqueActiveUsers.csv

|inputlookup test10UniqueActiveUsers.csv | eval t=relative_time(now(), "-200d@d") | eval fa=strptime(firstactivity, "%s") | search "fa"<="t" |table fa, t, firstactivity

However the clause "fa"<="t" is always true and it returns the whole table.
If I do not use the generated ield t and instead put in the exact numeric value the it works fine. So

|inputlookup test10UniqueActiveUsers.csv | eval t=relative_time(now(), "-200d@d") | eval fa=strptime(firstactivity, "%s") | search "fa"<=1480550400.0 |table fa, t, firstactivity

works perfectly for >= or <=.
In the output table I can see that t is in fact equal to 1480550400.0, so the two queries above should be exactly equivalent.

The only thing I can think of is that one of the fields might have an odd type like a string, and a number string comparison is happening, but I do not know how to confirm or fix that.

For the first query where the comparison is always true the output is:

fa                            t      firstactivity  
1484407682.000000   1480550400.000000   1484407682
1479115786.000000   1480550400.000000   1479115786
1484531129.000000   1480550400.000000   1484531129
1481212575.000000   1480550400.000000   1481212575
1490712727.000000   1480550400.000000   1490712727
1482140767.000000   1480550400.000000   1482140767
1476115342.000000   1480550400.000000   1476115342
1484696713.000000   1480550400.000000   1484696713
1489640643.000000   1480550400.000000   1489640643

Re: Comparison of two date fields gives bad result

3no — Mon, 19 Jun 2017 12:35:28 GMT

Hi,

You can use tonumber on both of your field to be sure they are Integer :

| eval myInt = tonumber(your_field)

3no.

Re: Comparison of two date fields gives bad result

niamurph — Mon, 19 Jun 2017 13:30:42 GMT

I tried that and nothing changed - maybe my theory about the field type is wrong and the cause is something else. I am baffled !

Re: Comparison of two date fields gives bad result

3no — Mon, 19 Jun 2017 13:54:05 GMT

Try with :
| search fa<=t (without the "")

Re: Comparison of two date fields gives bad result

niamurph — Mon, 19 Jun 2017 14:46:37 GMT

Sorry - I tried that as well. No success. I only added the " s when the version without failed !!

Re: Comparison of two date fields gives bad result

DalJeanis — Mon, 19 Jun 2017 15:06:25 GMT

When comparing the values of two fields, do not use search, use where.

 | where fa<=t

The search keyword assumes the item on the left is a field name and the item on the right is a value. The where keyword allows both sides to be evaluated.

See the section on comparing two fields, midway down this page...

http://docs.splunk.com/Documentation/SplunkCloud/6.6.0/SearchReference/Search

Re: Comparison of two date fields gives bad result

niamurph — Mon, 19 Jun 2017 15:11:25 GMT

Thanks - that fixed it.

Re: Comparison of two date fields gives bad result

niamurph — Mon, 19 Jun 2017 15:12:51 GMT

Oh actually I have it working onw, and it needs the " removed alright - but the original cause was the fact that I used 'search' instead of 'where'