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
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
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
Thanks - that fixed it.
Hi,
You can use tonumber
on both of your field to be sure they are Integer :
| eval myInt = tonumber(your_field)
3no.
I tried that and nothing changed - maybe my theory about the field type is wrong and the cause is something else. I am baffled !
Try with :
| search fa<=t (without the "")
Sorry - I tried that as well. No success. I only added the " s when the version without failed !!
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'