Combine a search and subsearch to create a table with all values

[robettinger]

Hi guys,

Quick question here: I have the following queries:

Q1: Sub-Search for userID
Q2: Main search, which provides username and department

Currently I can get a table with userID, Username & Department.

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?

Table:

UserID | Username | Department | Last Access

Thank you.

[HiroshiSatoh]

Try this!

(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

[Sukisen1981]

what are you using? Join or append or stats?

[robettinger]

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?

[Sukisen1981]

have you checked if the same can be achieved using something like |stats values(field)....?

[robettinger]

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 ...