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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk Life | Splunk is Officially Part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint. Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...