Splunk Search

How do I use results from one search in a subsearch?

tmurray3
Path Finder

Trying to use the results of one query in the sub query search. I am not getting the results I expected. The first search returns about 2400 ids, and I want to pull those same id's from the sub query. The results returned are far less than expected; less than 100. It should be exactly the same count as the first query.

index=12_access Server connection terminated |stats count by tid| rename tid AS extid|table extid| join extid [search index=13_access ]| stats count by extid,resource.

Is the query logic wrong? Any ideas would be greatly appreciated.

0 Karma

somesoni2
Revered Legend

My guess is that join is the problem here. Join subsearch, apart from being in-efficient, has limitation on number of rows match (subsearch limit). For your requirement I would try like this

index=13_access [ search index=12_access Server connection terminated |stats count by tid | table tid | rename tid as extid ]
| stats count by extid,resource
0 Karma

Sukisen1981
Champion

I think like somesoni2 is saying please try to achieve the same through the stats command like he ahs shown.
Join has limitations. And also in your join usage , since you did not specify a join type , the default is an innder join meaning only extid common to both searches will be considered as the 'key' fields. If you really want to go for join (I still feel somesoni's stat query will work out for you) please read the join docuementation - http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Join
if you want all of the events in the main search and only those values in the subsearch have matching field values, consider using a left join.

0 Karma