Splunk Search

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

robettinger
Explorer

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.

0 Karma

HiroshiSatoh
Champion

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
0 Karma

Sukisen1981
Champion

what are you using? Join or append or stats?

0 Karma

robettinger
Explorer

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?

0 Karma

Sukisen1981
Champion

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

0 Karma

robettinger
Explorer

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!