How to do a field lookup or another solution to overcome the join and subsearch result limitations?

[ManfredGrill]

Hi,

I basically need to lookup the field creationTime in an object log for objects that show up in a request log. Using join/subsearch this works fine, but due to the join command returning only 10000 values, I need another solution.

table: request_log (3000000+ entries)
_time, run_id, object_id
2016-05-21 10:13:34, 1238, 342_1
2016-05-21 10:13:37, 1239, 4432
2016-05-21 10:13:43, 1240, erweww
2016-05-21 10:13:44, 1241, sdf4_1
2016-05-21 10:13:44, 1242, 342_1
2016-05-21 10:13:51, 1243, erweww

each object_id can/will exist multiple times

table: object_log (400000+ entries)
_time, object_id
2012-01-17 21:48:21, 342_1
2012-10-30 14:28:25, erweww
2014-09-23 07:41:12, sdf4_1
2015-11-02 10:08:55, 4432

Each item has a single entry

As a result I need the age of an item once it shows up in the request table.

desired result table

_time, object_id, creationTime, ageDays
21/05/2016 10:13:51.000, erweww, 2012-10-30 14:28:25, 1299
21/05/2016 10:13:44.000, 342_1, 2012-01-17 21:48:21, 1585
21/05/2016 10:13:44.000, sdf4_1, 2014-09-23 07:41:12, 606
21/05/2016 10:13:43.000, erweww, 2012-10-30 14:28:25, 1299
21/05/2016 10:13:37.000, 4432, 2015-11-02 10:08:55, 201
21/05/2016 10:13:34.000, 342_1, 2012-01-17 21:48:21, 1585

This is the query I'm using to build the result table:

source="requests_log.txt" | join type=left object_id [search source="object_log.txt" | eval creationTime=field1] | eval ageDays=round(((strptime(dt,"%Y-%m-%d %H:%M:%S") - strptime(creationTime,"%Y-%m-%d %H:%M:%S"))/86400),0)

The query in general works fine, but due to the limitaions of the number of results (10000) the results are mssing data.
That means many rows don't contain creationDate and ageDays values.
Limitations about subsearches.
http://docs.splunk.com/Documentation/Splunk/6.4.1/Search/Aboutsubsearches

There are some similar problems in Splunk answers, but so far I've been unable to adjust them. Maybe someone can lead me the way.

[somesoni2 · Accepted solution]

Give this a try
Updated per comment below

 source="requests_log.txt" OR source="object_log.txt"
 | eval creationTime=if(source="object_log.txt",field1,null()) | eventstats values(creationTime) as creationTime by object_id 
 | search source="requests_log.txt"
 | eval ageDays=round(((strptime(dt,"%Y-%m-%d %H:%M:%S") - strptime(creationTime,"%Y-%m-%d %H:%M:%S"))/86400),0)

[ManfredGrill]

Hi somesoni2,
this works great and I think I understand how it works. This helped me to understand: http://blogs.splunk.com/2014/04/01/search-command-stats-eventstats-and-streamstats-2/

There is a minor issue in your search. The CreationTime in the eventstats command needs to be lowercase. Maybe you can edit your answer for future reference.

Thank you

[woodcock]

Like this:

source="requests_log.txt" OR source="object_log.txt"
| rename field1 AS creationTime
| stats values(*) AS * values(_time) AS _time BY object_id
| search source="requests_log.txt"
| eval ageDays=round(((strptime(dt,"%Y-%m-%d %H:%M:%S") - strptime(creationTime,"%Y-%m-%d %H:%M:%S"))/86400),0)
| mvexpand _time
| table _time object_id creationTime ageDays

[ManfredGrill]

Hi, still not working.
https://onedrive.live.com/redir?resid=3B1173AAA8CCEC5A!19649&authkey=!ACDVJ7ZPyQAY4Sk&v=3&ithint=pho...

[ManfredGrill]

Thanks for the quick response. Unfortunately it's not working as required.
See the screenshots.
https://onedrive.live.com/redir?resid=3B1173AAA8CCEC5A!19647&authkey=!AM72rus3NXwuaOg&v=3&ithint=pho...

https://onedrive.live.com/redir?resid=3B1173AAA8CCEC5A!19648&authkey=!AJe-0jdq7WIcpsI&v=3&ithint=pho...

[woodcock]

I have edited my original answer considerably. Try it again.