Splunk Search

Running search on 2 indexes using lookup table

chr1s
Engager

Grateful if anyone can help or guide me in the right direction.

I am running a search against a lookup table. The output is a list of websites that were accessed. The website and source address are in index1. I want to use the source address to search in index2 to locate the user assigned to that IP address. 

Matching is working well and I am stuck how to proceed with the 2nd search query.

 

 

index=index1 domain=* OR index=index2
| lookup weblist.csv domain AS domain OUTPUT domain AS MATCHED
| where isnotnull(MATCHED) 
| table _time, MATCHED, src, user

 

 

In Index2, src_ip and user fields exist.

 

Labels (1)
0 Karma

chr1s
Engager

Hi @gcusello 

The syntax I have now is looking ok except the user field is not populated. The src or src_ip field needs to search index2 and a manual search returns many results. I assume that this is causing the user field not to populate? 

index=index1 domain=* OR index=index2
| lookup weblist.csv domain AS domain OUTPUT domain AS domain
| where isnotnull(domain)
| eval src=coalesce(src,src_ip)
| stats 
   earliest(_time) As _time
   values(user) AS user
   values(domain) AS domain
   BY src
| table _time domain src user

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @chr1s ,

manually check if the values in index 2 are aligned with the ones in index1 or if there's some difference.

Ciao.

Giuseppe

0 Karma

chr1s
Engager

Hi @gcusello 

There are differences in both indexes. Only src and src_ip are similar fields. Does both indexes need to have the same fields for this syntax to work correctly? 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @chr1s,

using coalesce, you put in one single field both the fields.

But you have to check if there are common values between the two indexes, even if the name is src or src_ip,  so you can check if the search is correctly running.

Ciao.

Giuseppe

0 Karma

chr1s
Engager

Hi @gcusello 

The only fields that are similar in index1 & index2 are: src and src_ip

The fields required in index1: 'src' or 'src_ip' and 'domain'

The fields required in Index2: 'src' or 'src_ip' and 'user'

Is there a way to create a variable from the index1 search and later to use it in the index2 search to get the username field?

0 Karma

gcusello
SplunkTrust
SplunkTrust

hi @chr1s 

don't use the filter with where because you loose some events.

check the events and especially the src and src_ip fields if they have compatible values because it should run as I sent.

Ciao.

Giuseppe

0 Karma

chr1s
Engager

Hi @gcusello 

Thank you for helping. The 2 common fields in both indexes is 'src_ip'

Index1 fields I need to use:

src or src_ip, domain

Index2 fields I need to use:

src_ip, user

domain field is only present in Index1. Hope this helps. 

0 Karma

gcusello
SplunkTrust
SplunkTrust

hi @chr1s,

when you say thet in index1 you have src or src-ip, do you mean that they could be both present?

if yes, please try this:

index=index1 domain=* OR index=index2
| eval src=coalesce(src,src_ip)
| stats 
   earliest(_time) As _time
   values(user) AS user
   values(domain) AS domain
   BY src
| table _time domain src user

in few words: I used the common field (src) to correlate the fields in the two indexes.

Ciao.

Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @chr1s,

let me understand: what's the common fields between index1 and index2?

if they are "src" and "domain", you could try something like this:

index=index1 domain=* OR index=index2
| stats 
   earliest(_time) As _time
   values(user) AS user
   BY domain src_ip
| table _time domain src user

if "src" and "domain" aren't present in both the indexes, please indicate which fields are present in the two indexes.

Ciao.

Giuseppe

 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...