Splunk Search

Return all rows in which field value included in subsearch result

sergiyd
New Member

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 🙂
alt text

Tags (2)
0 Karma

woodcock
Esteemed Legend

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$"
0 Karma

sergiyd
New Member

> [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!

0 Karma

sideview
SplunkTrust
SplunkTrust

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

0 Karma

sergiyd
New Member

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&lt;somefield&gt;.+)" | 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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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

0 Karma

sergiyd
New Member

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!

0 Karma

woodcock
Esteemed Legend

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$"
0 Karma

sergiyd
New Member

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

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

sergiyd
New Member

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...