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!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...