Splunk Search

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

Contributor

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)

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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] `

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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] `

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

Even though I cannot get this to work, I believe your code is correct. I believe the trouble is the data I am using. I thank you for all your help.

0 Karma

Contributor

long radio silence, have we hit a dead end?

0 Karma

SplunkTrust
SplunkTrust

Yep, i feel like i'm talking in circles. You cant share data, and I'm not able to convey how to make it work with your data to you. So it's like my only out is to tell you to take some splunk search courses or something. Given all the instructions I have provided you should be able to read between the lines and make it work but that isn't happening.

Please read these documents and try to solve based on the conversation we've already had:

http://docs.splunk.com/Documentation/Splunk/6.1.2/SearchTutorial/Useasubsearch

I'll ask some folks in IRC to come here and see if they can explain it better.

0 Karma

Contributor

Sorry I really don't want to keep rehashing this issue however, as you are more experienced with sub-searching, and I follow your logic, would you mind answering a few more questions (with a yes or no)?

Is it possible this could be a data issue?

I have been successful with sub searches in the past using only one index, but have you had success with subsearches using a different index contained in a subsearch?

Thanks

0 Karma

SplunkTrust
SplunkTrust

I doubt its a data issue... but some more help here for you.

When you run the search you can click on "job" drop down, then click on "inspect job", then look at "normalized search". It should look like this:

litsearch index=main ( ( component="ExecProcessor" ) OR ( component="HttpPubSubConnection" ) OR ( component="HttpPubSubConnection" ) OR ( component="Metrics" ) OR ( component="Metrics" ) ) | fields keepcolorder=t "*" "_bkt" "_cd" "_si" "host" "index" "linecount" "source" "sourcetype" "splunk_server"

Or similar if you run a subsearch like this:

index=main [search index=_internal component=* | head 5 | table component]

You'll see where it's adding a large OR clause to the main search.

Now if the subsearch returns 0 results then the normalized search will look like this instead:

litsearch NOT () | fields keepcolorder=t "*" "_bkt" "_cd" "_si" "host" "index" "linecount" "source" "sourcetype" "splunk_server"

And that's obviously borked.

Try the approach above and see if you can spot the error in your normalized search.

0 Karma

Contributor

Yes I am a newbie, and trying to get access to courses has been a hurdle for me.

I think I am asking splunk to do something that is not possible. I have had success with sub-searches but not in this case.

I was side-tracked with another project so I will dive back in, if/when I get this working I will share my code (which will contain your code) just for closure on this emotionally draining question.

Thank you for your help.

0 Karma

Contributor

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

0 Karma

Contributor

sender is the actual field in index = mail
suser is the actual field in index = mail (but the format need the rex to match sender).

Thank you

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

Contributor

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!

0 Karma

Contributor

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.

0 Karma

SplunkTrust
SplunkTrust

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) ]
0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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?

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

Contributor

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

0 Karma