Splunk Search

How to do a text search from the lookup into index?

onkarkore1
Explorer

II have a lookup table named transaction.csv contains one colunm, transaction_name. The goal is to have Splunk go through the lookup table and match text in the column named, transaction_name. and return a matching term

Lookup table is "transaction.csv" having one column named, transaction_name it have N numbers of entries (1000 entries) follows:

transaction_name
status
result
failed
success
report
idle
....
Any help would be great. I have tried the below:

index=index_name [| inputlookup transaction.csv | eval search=transaction_name | table search]

Above search query not returning matching terms in table format.

I would like to see matching terms from csv file with respect to index events in the table format.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=iot [| inputlookup transaction.csv | eval search=transaction_name | table search]
| rex "transaction name: (?<transaction_name>\S+)" | stats count by transaction_name

View solution in original post

0 Karma

arcdevil
Path Finder

Or that 🙂

index=* 
    [| inputlookup transaction.csv 
    | return 10000 $search] 
| rex "transaction name: (?<transaction_name>\S+)" 
| stats count by index,transaction_name
0 Karma

onkarkore1
Explorer

I dont have any field in Splunk I want to do a text based search, I have below csv file which contains thousands of entries which I want to match with the events.

Splunk dont have any field, trying to execute text based search

Lookup table: transaction.csv
transaction_name
status
result
failed
success
report
idle
....
When I executed below query it is not returning any result, showing blank table

index="iot" [ | inputlookup "transaction.csv" | table transaction_name | rename transaction_name as search ] | table transaction_name

The above query didn't fetch any result.

I am trying to display list of all matching terms in table format as a search output

0 Karma

arcdevil
Path Finder

When you using "table" command you must specify field name.

To make your search work please modify it to:

index="iot" [ | inputlookup "transaction.csv" | return 10000 $transaction_name] | rex "transaction name: (?\S+)"  | table transaction_name 

And you get a text search, then create a field and a table based on the field.

0 Karma

onkarkore1
Explorer

I executed below one,

index="iot" [ | inputlookup "transaction.csv" | return 10000 $transaction_name] | rex "transaction name: (?\S+)" | table transaction_name

encountered error in regex... terms appearing in events are random they are not constant to any specific place

Then I executed below query,

index="iot" [ | inputlookup "tr.csv" | return 10000 $transaction_name] | table transaction_name

it's returning blank table

0 Karma

arcdevil
Path Finder

Sorry, error on copy paste. Correct search string:

 index="iot" [ | inputlookup "transaction.csv" | return 10000 $transaction_name] | rex  "transaction name: (?<transaction_name>\S+)"  | table transaction_name 

But the structure of the message is the same? I mean "transaction name: Workflow".

0 Karma

onkarkore1
Explorer

I thank you for all of your inputs and help!!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=iot [| inputlookup transaction.csv | eval search=transaction_name | table search]
| rex "transaction name: (?<transaction_name>\S+)" | stats count by transaction_name
0 Karma

onkarkore1
Explorer

I dont have any field in Splunk I want to do a text based search, I have below csv file which contains thousands of entries which I want to match with the events.

Splunk dont have any field, trying to execute text based search

Lookup table: transaction.csv
transaction_name
status
result
failed
success
report
idle
....
When I executed below query it is not returning any result, showing blank table

index="iot" [ | inputlookup "transaction.csv" | table transaction_name | rename transaction_name as search ] | table transaction_name

The above query didn't fetch any result.

I am trying to display list of all matching terms in table format as a search output

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The above search is doing a text based search. You can remove the rex line to just return the matching raw events.

0 Karma

onkarkore1
Explorer

Above search query is returning the matching raw events. But there are millions of events to scroll down and browse next page is difficult every time.

What we are looking for is to check each term from csv file whether any events contains term similar to csv lookup

I am trying to get toutput as a table which contains list of all matching terms and exclude non matching terms of csv lookup.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

These terms from lookup, do they always appear in a specific place (e.g. after first ] in the event) or they can be anywhere? Can an event contain more that one term?

0 Karma

onkarkore1
Explorer

Every event contains only one term. and there place is not specific. They can be appear anywhere in the event.

We just want to check how many terms from lookup returning matching raw events. We want to list down those matching terms which are returning result as a output.

0 Karma

onkarkore1
Explorer

Or is there any way to return only single matching raw event for each terms of csv lookup

Example: we have thousands of terms in csv lookupup file. So can we return only single matching event for each word. i.e. thousand unique matching raw events with respect to csv lookup.

Because above search query is returning millions of matching raw events. So its difficult figure out for every terms.

I have used below search query,

index=iot [| inputlookup transaction.csv | eval search=transaction_name | table search] | stats count by transaction_name

Can we dedup events based on terms. So that it can return only one matching raw event per terms from CSV

0 Karma

somesoni2
SplunkTrust
SplunkTrust

There might be a very in-efficient method. For that,
1) Update your lookup table to include wildcard character * before and after the values.

Lookup table: transaction.csv
transaction_name
*status*
*result*
*failed*
....

2) You need to create lookup transform and that will use wildcard attribute for match (default is exact match). So, you can create lookup transform on search heads, transforms.conf

[transaction_lookup ]
 filename = transaction.csv
 match_type = WILDCARD(transaction_name)

3) use the search like this

index=iot [| inputlookup transaction.csv | eval search=transaction_name | table search]
| lookup transaction_lookup transaction_name as _raw OUTPUT transaction_name as matched_term | dedup matched_term | table _time _raw matched_term
0 Karma

onkarkore1
Explorer

Yes, its working. This is what I am looking for, Thank you very much. I truly appreciate your time and effort.

I thank you for all of your help!!

0 Karma

renatobamorim
Explorer

Try this

index=index_name
| join type=left transaction_name [| inputlookup transaction.csv append=T | eval listed="true"]
| where listed="true"

The JOIN command put in your search result the field "listed" if the "transaction_name" value is in your lookup.

0 Karma

onkarkore1
Explorer

I want Splunk to return the term which is matching with events.

I want to see the output as list of terms matching with the events

I have below events,

160701 09:57:32.322 (I 4) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] Workflow (FX we_WorkflowUpdate.c 272) Status returning by WSVCUpdateMilestone: 0

160701 09:57:32.322 (I 4) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] Workflow (FX we_WorkflowUpdate.c 272) Status returning by WSVCUpdateMilestone: 0

160701 09:57:32.266 (D 5) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] DBServerId (FX wf_Engine.c 659) Account server: 3

160701 09:57:32.266 (I 4) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] CallData (FX wf_Engine.c 701) CONTENT IN DEBUG FILE

Now, I have a lookup table named transaction.csv having one column named, transaction_name it have N numbers of entries (1000 entries) follows:

transaction_name
WSVCUpdateMilestone
Workflow
DBServerId
CallData

I have executed below command but it is not showing list of matching terms as a search result.

I am trying to see list of matching term in table format as a search result

index="iot"[| inputlookup transaction.csv | eval search=transaction_name | table search]

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Are you saying you want splunk to return you one event that contains each term? Or return the term, if there exists one event that contains that term?

What are you wanting to do with the results?

0 Karma

onkarkore1
Explorer

Yes I want Splunk to return each term which is exist in the events

I want to see list of all terms as a search result which are present in events.

Events are as below,

160701 09:57:32.322 (I 4) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] Workflow (FX we_WorkflowUpdate.c 272) Status returning by WSVCUpdateMilestone: 0

160701 09:57:32.322 (I 4) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] Workflow (FX we_WorkflowUpdate.c 272) Status returning by WSVCUpdateMilestone: 0

160701 09:57:32.266 (D 5) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] DBServerId (FX wf_Engine.c 659) Account server: 3

160701 09:57:32.266 (I 4) [:0x0 0x5774c746 0x49ce2b 0x28 0x0 0x0:N_MlIsMd] CallData (FX wf_Engine.c 701) CONTENT IN DEBUG FILE

Now, I have a lookup table named transaction.csv contains one colunm, transaction_name.

transaction_name
WSVCUpdateMilestone
Workflow
DBServerId
CallData
....

0 Karma

niketn
Legend

@onkarkore1 this information is not sufficient.

Which is the common field in Lookup and Index that you want to correlate?
If it is not transaction_name, what is the corresponding field in Lookup table?
If it is transaction_name, what is the name of the field with same values is the index like status, result etc?
What is search? Is that a field? What are its values?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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