Search inside the files based on keywords to do sub search in splunk

[rojit]

I have following types of txt files in my source and contents of each files are mentioned below in CAPS:

a1.txt:
KEYWORD1
SQLDB

a2.txt:
KEYWORD1
KEYWORD2

a3.txt:
KEYWORD1
KEYWORD2
SQLDB

a4.txt:
KEYWORD1
KEYWORD2

From the above files, I need to search only txt files contains 'SQLDB' to fetch values 'KEYWORD1' or 'KEYWORD2' inside that file.
In above example, it should search only a1.txt and a3.txt files for me to have my further searches.

So basically, need to identify certain ways similar to Exists.
Appreciable, if anyone can help me to achieve this..

[rojit]

Final approach worked for me as follows:

index=X sourcetype=Y (SQLDB  OR KEYWORD1 OR KEYWORD2) [search index=X sourcetype=Y SQLDB | dedup source | table source]

[Richfez · Accepted solution]

@rojit

So let's make a different search. We are going to build it up bit by bit. I'm doing it this way so you will have explanations and can modify this as needed to make it work for you.

First task is to build a search that returns the source fields of the files that have the SQLDB string in them. You haven't provided much context, so you'll have to fill in some parts of this.

index=X sourcetype=Y SQLDB | dedup source | table source

You should run this and confirm it returns, in your case, a1.txt and a3.txt. This must be right or else the rest of this answer won't work.

Now, we'll use that little search above as a subsearch inside a bigger search.

index=X sourcetype=Y (KEYWORD1 OR KEYWORD2) [search index=X sourcetype=Y SQLDB | dedup source | table source]

The only difference on the "inside" search is that we had to add search to the front of it. The way the subsearch works will be to run that little search first, and the list of source will get returned to the outside search, where it'll get incorporated like:

index=X sourcetype=Y KEYWORD1 OR KEYWORD2 (source=a1.txt OR source=a3.txt)

That's not one you have to type, that happens on the back end. But that's what ends up being run so it should return anywhere those two keywords show up, but ONLY inside a1.txt or a3.txt.

Does that help?

Happy Splunking,
Rich

[rojit]

Thanks a lot @rich7177
The approach worked for my scenario..Below is the final code I was looking for:

index=X sourcetype=Y (SQLDB  OR KEYWORD1 OR KEYWORD2) [search index=X sourcetype=Y SQLDB | dedup source | table source]

[cmerriman]

as @rich7177 mentions, if the files are in Splunk, and they are listed as sources you're search could be something like source=*.txt SQLDB |search KEYWORD1 OR KEYWORD2

[rojit]

Thanks @cmerriman and yes files are there in splunk server. I already tried the above step.
The problem I am facing here is since we have base search as "SQLDB", it already will only shows lines with "SQLDB" in those txt files.
But I need to search KEYWORD1 and KEYWORD2 inside the text file having "SQLDB".

[cmerriman]

@micahkemp has another great option of putting all the keywords in the base search. source=*.txt SQLDB (KEYWORD1 OR KEYWORD2)

[Richfez]

Are these file in Splunk already? If so, the "source" field should contain the file name. Can you confirm this?

If they are NOT in Splunk, well, that's probably your first step.

[rojit]

Yes files are there in splunk currently. I already tried the way @cmerriman mentioned as below:

source=*.txt SQLDB |search KEYWORD1 OR KEYWORD2

The problem I am facing here is since we have base search as "SQLDB", it will only shows lines with "SQLDB" in those txt files.
But I need to search KEYWORD1 and KEYWORD2 inside the text files having "SQLDB".

[micahkemp]

What do you mean "search KEYWORD1 and KEYWORD2 inside the text files"?

The way I'm parsing it right now it seems like you mean this:

source=*.txt SQLDB KEYWORD1 KEYWORD2

Which would give you events from any .txt file that has the words "SQLDB", "KEYWORD1", and "KEYWORD2" present.