topic Combine a search and subsearch to create a table with all values in Splunk Search https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299016#M90120 <P>Hi guys,</P> <P>Quick question here: I have the following queries:</P> <P>Q1: Sub-Search for userID<BR /> Q2: Main search, which provides username and department</P> <P>Currently I can get a table with userID, Username &amp; Department.</P> <P>I would like to include in the result table each user's last access timestamp, but this field is in the sub-search index. What is the best approach to achieve that?</P> <P>Table:</P> <P>UserID | Username | Department | Last Access</P> <P>Thank you.</P> Fri, 06 Oct 2017 06:54:43 GMT robettinger 2017-10-06T06:54:43Z Combine a search and subsearch to create a table with all values https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299016#M90120 <P>Hi guys,</P> <P>Quick question here: I have the following queries:</P> <P>Q1: Sub-Search for userID<BR /> Q2: Main search, which provides username and department</P> <P>Currently I can get a table with userID, Username &amp; Department.</P> <P>I would like to include in the result table each user's last access timestamp, but this field is in the sub-search index. What is the best approach to achieve that?</P> <P>Table:</P> <P>UserID | Username | Department | Last Access</P> <P>Thank you.</P> Fri, 06 Oct 2017 06:54:43 GMT https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299016#M90120 robettinger 2017-10-06T06:54:43Z Re: Combine a search and subsearch to create a table with all values https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299017#M90121 <P>what are you using? Join or append or stats?</P> Fri, 06 Oct 2017 08:11:25 GMT https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299017#M90121 Sukisen1981 2017-10-06T08:11:25Z Re: Combine a search and subsearch to create a table with all values https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299018#M90122 <P>I managed to get what I want by using join, it does, take, however, a long time ... maybe there is a more "performant" way to achieve that?</P> Fri, 06 Oct 2017 08:27:16 GMT https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299018#M90122 robettinger 2017-10-06T08:27:16Z Re: Combine a search and subsearch to create a table with all values https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299019#M90123 <P>have you checked if the same can be achieved using something like |stats values(field)....?</P> Fri, 06 Oct 2017 08:56:29 GMT https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299019#M90123 Sukisen1981 2017-10-06T08:56:29Z Re: Combine a search and subsearch to create a table with all values https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299020#M90124 <P>Try this!</P> <PRE><CODE>(Condition of main and sub search) |stats earest(Username) as Username,earest(Department) as Department,latest("Last Access") as Last_Access by UserID UserID | Username | Department | Last Access ------------------------------- ------------ 1 X Y ------------ 1 _ _ 2017/10/1 1 X Y ------------ 1 X Y ------------ 1 _ _ 2017/10/2 ------------ 1 _ _ 2017/10/3 ------------ ------------------------------- ------------ 1 X Y 2017/10/3 </CODE></PRE> Fri, 06 Oct 2017 09:12:30 GMT https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299020#M90124 HiroshiSatoh 2017-10-06T09:12:30Z Re: Combine a search and subsearch to create a table with all values https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299021#M90125 <P>The main issue is the latest event I am looking for is not in the main search index, but the sub-search one ... I ditched the sub-search and performed a join which gives me what I want, but it is very expensive ...</P> Fri, 06 Oct 2017 11:16:09 GMT https://community.splunk.com/t5/Splunk-Search/Combine-a-search-and-subsearch-to-create-a-table-with-all-values/m-p/299021#M90125 robettinger 2017-10-06T11:16:09Z