Splunk Search

Matching field from one index to an identical field in a second index to get the value of another field in the second index

uh2
New Member

I've spent over a month reading on here and trying to do this myself, but it's time to throw up the white flag.

I've got two indexes:
Index=Main1 contains fields code_ID, domain
Index=Main2 contains fields code_ID, user

code_ID is a common field between both indexes. What I need to do is search index=Main1 for a certain pattern of text (index=Main1 "abcdefg"), which will return the code_ID and domain fields. Then, I need to take the code_ID that was retrieved from this search and search index=Main2 for an event that contains the same data. In one of those events, there will be a field called user that does not exist in index=Main1.
I need to display a table with the code_ID, domain and user fields. So far, everything I've done has produced an empty user field column.

Thanks in advance for your help.

Tags (2)
0 Karma

niketn
Legend

Give the following a try (ideally values() stats command should give only one result as per code_ID, if it does then do change values() to last() function.)

index="Main1" OR index="Main2" code_ID="*" 
| stats values(domain) as Domain values(user) as User by code_ID

You should also look at Splunk map command: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Map#1._Use_a_sudo_event_to_locat...

index="Main1" code_ID="*" 
| stats count by Code_ID domain 
| map search="search index="Main2" Code_ID=$Code_ID$"
| fields Code_ID domain User

Similar to join you can also use append, appendcols, transaction commands also, however, you would need to compare the query performance for each one of them and choose the best that suits your needs (hoping that stats will work faster compared to others). Refer to Splunk event correlation techniques documentation: http://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Here's one way, perhaps not the best.

index=Main1 "abcdefg" | join code_ID [ search index=Main2 "abcdefg" ] | table code_ID, domain, user
---
If this reply helps you, Karma would be appreciated.
0 Karma

uh2
New Member

Thanks for responding. I'll give this a shot...although the text "abcdefg' does not exist in index=Main2, only in Main1. The only thing tying the two together is the code_ID field.

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...