Return all rows in which field value included in subsearch result

[sergiyd]

Hello!
I guess I need something like selfjoin, but selfjoin joins to itself, when I have to filter results with subsearch.

Green color is what I need 🙂

[woodcock]

OK, so combining your ad-hoc field extraction with my original solution yields this:

 BaseSearchText | rex "(?i)somefield\s*:(?<somefield>.+)" | map search="search BaseSearchText | rex "(?i)somefield\s*:(?<somefield>.+)" | search somefield=$somefield$"

[sergiyd]

> [SimpleResultsTable module] Server reported HTTP status=400 while getting mode=results_preview <?xml version='1.0' encoding='UTF-8'?> <response><messages><msg type='FATAL'>The search job terminated unexpectedly.</msg></messages></response>

However, thank you!

[sideview]

I think what you want is really simple. It's very common for relatively new Splunk users looking for parallels to sql to dive too deeply into advanced things like the join command.

However here, I think what you want is either

"some text that I'm looking for" | table somefield 

or

"some text that I'm looking for" | stats count by somefield

UPDATE
Looking at your sql (and it's been a while), it looks like maybe what you want is to get the matches for the "some text that I'm looking for", then get the values of somefield that appear in those events, and then get all events that have those values of somefield? If so then you want a normal subsearch, ie search syntax that looks like this:

["some text that I'm looking for" | table somefield]

docs about subsearches:
http://docs.splunk.com/Documentation/Splunk/6.2.3/Search/Aboutsubsearches

[sergiyd]

I think I have to understand the philosophy of splunk, when I still somewhere at sql level...

I found the way to extract somefield values

"some text that I'm looking for" | rex field=_raw "(?i)somefield\s*:(?P<somefield>.+)" | table somefield

So, this is my subsearch and I need to filter main search, that hasn't any conditions except the same somefield must be included in this subsearch results.

[sideview]

Yes. OK. Here is the section of the docs that gives high level links to docs pages about field extractions. http://docs.splunk.com/Splexicon:Fieldextraction
What you've found here is a quick and dirty way, basically the "extract fields with search commands" link on that page.

What you want to do is create a proper field extraction, either with the Interactive Field Extractor, or by putting a similar regex into the conf files. Both options have links on that page.

I assumed you were already familiar with field extractions as they're something you need to understand quite early on.

You also might want to go through the tutorial if you haven't already - http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchTutorial/WelcometotheSearchTutorial

[sergiyd]

Reading the "excellent" documentation, I, as a developer, I try to draw a parallel to what I already know (SQL).

Also, when I tried to create a new field extraction (in fact I did), it requires sourcetype to be specified, but I have many sourcetype*s and I want to search across all of them. I wasn't able to use extracted field even with exact *sourcetype.

I think I have to leave this venture.
Thank you!

[woodcock]

Based on this:

select * from Data where somefield in (select somefield from Data where _raw like "%search text%")

This should work

"search text" | map search="search somefield=$somefield$"

[sergiyd]

I can't define somefield permanently (I used splunk tutorial). So, I can use only query time fields.

[sideview]

Can you tell us where your event boundaries are in Splunk? Are they multiline events such that the lines that have the somefield value are in the same event as the "some text I'm [not] looking for lines" ? Or are these all individual single-line events?

If these are multiline events, then what you want is really simple. Either "some text that I'm looking for" | table somefield or "some text that I'm looking for" | stats count by somefield. And even if they are single line events then selfjoin and all that isn't what you need at all. sql metaphors only go so far in splunk before they lead you astray.

[sergiyd]

sideview
Yes, there are multiline events, in this example each event is separated with three dots (...)