Splunk Search

How do I use the username in events returned in a search of Index "A" to look up the user in index "B" and only return the events where the user in event from index "A" exists in index "B"

bulu
New Member
This part of my query gets me on the street I want to be on for this report

index="A"
| rex mode=sed field=User_Full_Name "s/ //g"
| eval User_Full_Name = LOWER(User_Full_Name)
| rex mode=sed field=Emergency_Contact1 "s/ //g"
| eval Emergency_Contact1 = LOWER(Emergency_Contact1)
| eval results = if(match(Emergency_Contact1,User_Full_Name), "match", "no match")
| dedup User_Full_Name
| search results="match"
| eval Service_Areas=split(Patient_Service_Areas, ",")
| search Service_Areas="50*"

This syntax does not return any results even though I know I have matches in my testing data

| eval User_Logon_ID = LOWER(User_Logon_ID)
| search index="B"
| eval HSCNET_ID = LOWER(HSCNET_ID)
| eval results = if(match(User_Logon_ID,HSCNET_ID), "USF", "no USF")
| search results="USF"

Tags (1)
0 Karma

Richfez
SplunkTrust
SplunkTrust

If, when used all by itself this search:

| search index="B" 
| eval HSCNET_ID = LOWER(HSCNET_ID)
| eval results = if(match(User_Logon_ID,HSCNET_ID), "USF", "no USF") 
| search results="USF"

Returns the list of users you would like to have search A filtered by, then you can use that piece as a subsearch for A to filter it down. When a subsearch runs and returns data, it'll be "inserted" into the original search as search terms. So if search index=X | table UserNames returns "Billy Sally Susan" as the three items, then 'index=Y [index=X | table UserNames]will run a search that ends up looking likeindex=Y (Billy OR Sally OR Susan)`. Follow that? That's what I think you want to do here.

There's enough going on in those searches that I'm not positive exactly where it should be, but it could go in anywhere that the names would "match up" so they've be search terms in it. Perhaps like this:

index="A" 
| rex mode=sed field=User_Full_Name "s/ //g" 
| eval User_Full_Name = LOWER(User_Full_Name) 
| rex mode=sed field=Emergency_Contact1 "s/ //g" 
| eval Emergency_Contact1 = LOWER(Emergency_Contact1) 
| eval results = if(match(Emergency_Contact1,User_Full_Name), "match", "no match") 
| dedup User_Full_Name [ search index="B" 
    | eval HSCNET_ID = LOWER(HSCNET_ID)
    | eval results = if(match(User_Logon_ID,HSCNET_ID), "USF", "no USF") 
    | search results="USF" ]
| search results="match" 
| eval Service_Areas=split(Patient_Service_Areas, ",") 
| search Service_Areas="50*"

Now, there's some ambiguity in my answer becuase I can't see the data and there's enough going on (for an early Sunday morning before church) that I could be wrong. But you could try that though and see what it gets you!

Happy Splunking!
Rich

bulu
New Member

This part of the search returns all of the events where the user full name and emergency contact full name match, and the service area is a service area I am concerned with.

index="A"
| rex mode=sed field=User_Full_Name "s/ //g"
| eval User_Full_Name = LOWER(User_Full_Name)
| rex mode=sed field=Emergency_Contact1 "s/ //g"
| eval Emergency_Contact1 = LOWER(Emergency_Contact1)
| eval results = if(match(Emergency_Contact1,User_Full_Name), "match", "no match")
| dedup User_Full_Name
| search results="match"
| eval Service_Areas=split(Patient_Service_Areas, ",")
| search Service_Areas="50*"

The problem is about 80% of the returned results are false positives for my purposes so I need to execute an additional search that takes the user logon ID (user's Windows AD username) from a returned event and looks it up in our identity management system (contained in a different index) to see if the user is one of our users or not, and only return the events where the user in the event exists in our identity management system. That's where I try to work this search in.

| eval User_Logon_ID = LOWER(User_Logon_ID)
| search index="B"
| eval HSCNET_ID = LOWER(HSCNET_ID)
| eval results = if(match(User_Logon_ID,HSCNET_ID), "USF", "no USF")
| search results="USF"

0 Karma

Richfez
SplunkTrust
SplunkTrust

To be clear - the second part of your search doesn't return events when it is placed inline after the first search?

And you want B to be the list of users to look up in A, right?

A sample of index A and index B's events would be useful if you could provide them.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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