topic Re: How to do a text search from the lookup into index? in Splunk Search

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

onkarkore1 — Tue, 29 Sep 2020 14:26:02 GMT

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.

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

niketn — Tue, 29 Sep 2020 14:29:13 GMT

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

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

onkarkore1 — Tue, 29 Sep 2020 14:29:18 GMT

My index name is iot which is generating number of events as below,

index=iot

Below are the few event samples from iot index and transaction values are given contained in transaction_name column from transaction.csv lookup table.

trnsaction name: WSVCUpdateMilestone
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

transaction name: Workflow
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

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

transaction name: CallData
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. The goal is to have Splunk go through the lookup table and match text in the column, transaction_name with the index iot. and return either matching or non-matching term

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

transaction_name
WSVCUpdateMilestone
Workflow
DBServerId
CallData
....
These are thousands of transaction name contained in lookup table, we want to check whether index is returning any events containing above transaction value.

Manually executing search query is difficult for thousands of entries like below,

index="index_name" "transaction_name"

This is why we want to use lookup which will go through index events and return list of either matching or non_matching transaction values from table.

Any help would be great. I have tried the below:

index=iot [|inputlookup transaction.csv | return transaction_name]

But above search query is not returning any data, when I executed above query it returned no result found.

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

DalJeanis — Mon, 12 Jun 2017 20:41:58 GMT

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?

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

renatobamorim — Mon, 12 Jun 2017 21:22:22 GMT

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.

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

somesoni2 — Mon, 12 Jun 2017 22:13:18 GMT

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

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

onkarkore1 — Tue, 29 Sep 2020 14:26:29 GMT

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

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

onkarkore1 — Tue, 29 Sep 2020 14:26:32 GMT

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

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

arcdevil — Tue, 13 Jun 2017 10:20:26 GMT

Or that 🙂

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

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

onkarkore1 — Tue, 29 Sep 2020 14:26:40 GMT

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

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

onkarkore1 — Tue, 29 Sep 2020 14:26:42 GMT

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

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

arcdevil — Tue, 13 Jun 2017 10:38:22 GMT

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.

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

onkarkore1 — Tue, 29 Sep 2020 14:26:45 GMT

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

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

arcdevil — Tue, 13 Jun 2017 11:54:43 GMT

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

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

somesoni2 — Tue, 13 Jun 2017 15:48:03 GMT

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

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

onkarkore1 — Tue, 13 Jun 2017 17:54:55 GMT

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.

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

somesoni2 — Tue, 13 Jun 2017 18:18:41 GMT

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?

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

onkarkore1 — Tue, 13 Jun 2017 18:24:52 GMT

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.

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

onkarkore1 — Tue, 29 Sep 2020 14:27:06 GMT

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

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

somesoni2 — Tue, 13 Jun 2017 18:53:04 GMT

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