topic Re: How to search multiple indexes for the same value under different field names? in Splunk Search

How to search multiple indexes for the same value under different field names?

packet_hunter — Wed, 25 May 2016 18:12:25 GMT

Scenario: Ultimately, I would like to create an alert for an event in index A. Then I would like the alert to kickoff a search on index B based on a field value in index A. The value in index A and index B is the same, however, the fields are different. As this is a complex question, I would like to focus on using the field value of FieldA in index A to search for FieldB in index B.

Here is the logic I would like the search to follow:

index = A sourcetype = a Auser = *

index = B sourcetype = b Buser = Auser

Thank you for your help.
(hopefully it makes sense)

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Wed, 25 May 2016 18:24:11 GMT

index=B sourcetype=b [ search index=a sourcetype=a Auser=* | dedup Auser| fields Auser ]

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Wed, 25 May 2016 19:14:39 GMT

Thank you for the response, however not quite there yet.

index=B sourcetype=b [search index=a sourcetype=a Auser=* | dedup Auser| fields Auser ]

I added "search" in the brackets. I think this will work but I have to rex Auser to the common format.

I will let you know how your code works asap.

Thank you

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Wed, 25 May 2016 19:25:49 GMT

I am going to throw a curve ball now making the question more challenging.

Auser and Buser are not natively in the same format.

I have to use

rex field=Auser "(?<sender>\w+@\w+\.\w+)"

to match Buser format.

They are both email addresses e.g. someone@somedomain.tdl

Currently I am having no luck inserting the rex into your code.

Any help is appreciated.

Thank you!

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Wed, 25 May 2016 19:40:31 GMT

just to clarify, I am trying to find the results from

[search index=A sourcetype=a Auser=* | dedup Auser | fields Auser |rex field=Auser "(?<sender>\w+@\w+\.\w+)"  | stats list(sender)]

in index= B

The previous code gives me a list of results, in this case email addresses.

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Wed, 25 May 2016 20:12:09 GMT

Your regex is not capturing the user but the entire email address... we might also take to lowercase to help... I left out search but also forgot to rename it as Buser too.

 index=B sourcetype=b [ search index=a sourcetype=a Auser=* | fields Auser | dedup Auser | rex field=Auser "(?<sender>\w+)@\w+\.\w+" | eval Buser=lower(sender) ]

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Thu, 26 May 2016 15:38:40 GMT

Unfortunately I am having no luck. But I think we are close.

The following gives the result "sender" which is the field in index=B that I want to search.

index=A sourcetype=a Auser=*   |rex field=Auser "(?<sender>\w+@\w+\.\w+)"  | stats list(sender)

So if I could get the proper syntax to add this subsearch results to index=B, I think we are golden.

Thank you

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Thu, 26 May 2016 15:45:18 GMT

This should give a field called Buser which is the email sender... correct?

  search index=A sourcetype=a Auser=* | fields Auser | dedup Auser | rex field=Auser "(?<sender>\w+)@\w+\.\w+" | eval Buser=lower(sender) | table Buser

if so it should work fine here

 index=B sourcetype=b [ search index=a sourcetype=a Auser=* | fields Auser | dedup Auser | rex field=Auser "(?<sender>\w+)@\w+\.\w+" | eval Buser=lower(sender) | table Buser ]

assuming the name of the user field in index B is Buser... change the eval to be userNameFieldInIndexB=sender maybe?

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Thu, 26 May 2016 15:46:04 GMT

Maybe it will help if you give me the correct index names, sourcetype names and field names so I can give you the solution without you interpreting what I'm trying to tell you.???

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Thu, 26 May 2016 17:22:32 GMT

I wish I could give the "actual" code however I am not permitted for security reasons, however you are correct here:

in index= A the field is actually "suser" and it gets converted to sender, which is the field in index=B that I am looking for.

In other words, in index=A I convert suser to an email address like someone@somedomain.tld as sender. "sender" is the field in index=B that I am trying to find all matches. I will let you know if I can get your latest code to work.

Thank you

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Thu, 26 May 2016 17:28:03 GMT

so in the second search... change the "eval Buser=lower(sender) | table Buser" to be "eval FieldNameInIndexB=lower(sender) | table FieldNameInIndexB"

What is happening is the subsearch (second search) is sending values for FieldNameInIndexB back and separating them with ORs so that they look like this on the main search

index=B sourcetype=b (FieldNameInIndexB=asdf OR FieldNameInIndexB=asdfasdf OR FieldNameInIndexB=234asd)

where FieldNameInIndexB is whatever we have in that last table command within the subsearch. the lower(sender) is there to force lowercase on sender and may not be require or desired.

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Thu, 26 May 2016 17:31:58 GMT

not getting it to work, maybe b/c the sender field is common to index A and index B?

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Thu, 26 May 2016 17:44:36 GMT

ok, I can give you this as index=A

index=main sourcetype=X_cef_syslog eventtype=X suser=* |rex field=suser "(?<attacker>\w+@\w+\.\w+)" | stats list(*)

this code works, and I changed the field name to attacker

I can give you this as index=B

index=mail sourcetype="xmail:textmail" and the field of interest is "sender"

I hope that helps

Now what I was thinking is using a subsearch as transactions and joins are expensive so,

index=mail sourcetype="xmail:textmail" [search index=main sourcetype=X_cef_syslog eventtype=X suser=* |rex field=suser "(?<attacker>\w+@\w+\.\w+)" | stats list(*)]  | eval sender = attacker

but this does not work

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Tue, 29 Sep 2020 09:48:48 GMT

index=mail sourcetype="xmail:textmail" [search index=main sourcetype=X_cef_syslog eventtype=X suser=* |rex field=suser "(?\w+@\w+.\w+)" |eval sender=attacker | fields sender]

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Tue, 29 Sep 2020 09:48:51 GMT

but problem is you're getting full email address and you said you want everything before @ as the attacker.

index=mail sourcetype="xmail:textmail" [search index=main sourcetype=X_cef_syslog eventtype=X suser=* |rex field=suser "(?\w+)@\w+.\w+" | eval sender=attacker | fields sender]

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Tue, 29 Sep 2020 09:48:59 GMT

index=mail sourcetype="xmail:textmail" [search index=main sourcetype=X_cef_syslog eventtype=X suser=* |rex field=suser "(?\w+)@\w+.\w+" | eval sender=attacker | fields sender]

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Thu, 26 May 2016 19:38:49 GMT

We have to start a new thread now because of too many comments:

You said you need everything before the @ in email address to be the sender/attacker, so here is the correct way to capture that with rex.

 `index=mail sourcetype="xmail:textmail" [search index=main sourcetype=X_cef_syslog eventtype=X suser=* |rex field=suser "(?<sender>\w+)@\w+.\w+" | fields sender] `

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Thu, 26 May 2016 20:13:39 GMT

my indexer crashed, I will let you know how it goes. Thanks for the reply.

Re: How to search multiple indexes for the same value under different field names?

jkat54 — Thu, 26 May 2016 20:19:05 GMT

Put your new comments here. the other thread is too long and comments wont stay...

Re: How to search multiple indexes for the same value under different field names?

packet_hunter — Thu, 26 May 2016 21:30:40 GMT

Sorry, for making you do some hard work here. Actually, I need the whole email address for sender and suser.

To recap, the suser field is not natively in a clean someone@somedomain.tld format, that is why I rex. After using the |rex field=suser "(?<attacker>\w+@\w+\.\w+)" then the format of suser [attacker] is the same as sender in index=mail sourcetype="xmail:textmail"

does that make sense?

The step I have trouble with is using the subsearch results to trigger a search for sender in the index =mail

I hope this clears things up