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.
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
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
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?
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
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?
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
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.
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 userin few words: I used the common field (src) to correlate the fields in the two indexes.
Ciao.
Giuseppe
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 userif "src" and "domain" aren't present in both the indexes, please indicate which fields are present in the two indexes.
Ciao.
Giuseppe