Splunk Search

Subesearch with Inputlookup

Shimon81
Explorer

 I want to run a base query where some fields has a value which is present in inputlookup table

 

For example,  I have a csv file with the content:

 

type

1

2

3

.

.

and in my basesearch i have the fields : type1, type2

I tried this query but is not working:

index="example"

[|inputlookup myfile .csv |stats values(type) as types]

|Where type1 in(types) OR type2 in(types)

|table type1 type2 

 

Thanks

 

 

 

Labels (3)
Tags (3)
0 Karma
1 Solution

ITWhisperer
Legend

Alternatively (without the double lookup)

index="example"
[|inputlookup myfile.csv 
| eval range=mvrange(1,3)
| mvexpand range
| eval type2=if(range==2,type,null)
| eval type1=if(range==1,type,null)
| table type1 type2]
|table type1 type2 

View solution in original post

ITWhisperer
Legend

Alternatively (without the double lookup)

index="example"
[|inputlookup myfile.csv 
| eval range=mvrange(1,3)
| mvexpand range
| eval type2=if(range==2,type,null)
| eval type1=if(range==1,type,null)
| table type1 type2]
|table type1 type2 

View solution in original post

Shimon81
Explorer

@ITWhisperer thank you for your solution,

I made some changes in my query, and it dosent work,

I would be happy if you can tell me what is the problem

Here is my new query:

index="example"

|eval type1=mvindex(type.split(":"),0)

|eval type2=mvindex(type.split(":"),1)

|search

[|inputlookup myfile.csv 

| eval range=mvrange(1,3)

| mvexpand range

| eval type2=if(range==2,type,null)

| eval type1=if(range==1,type,null)

| table type1 type2]

|table type1 type2

 

Thank you

 

 

0 Karma

ITWhisperer
Legend

The split function takes 2 arguments, it isn't a method on the type object.

|eval type1=mvindex(split(type,":"),0)
|eval type2=mvindex(split(type,":"),1)
0 Karma

Shimon81
Explorer

Yes You right, but is not my problem ,

I got result with type that not in my csv file

Here is my query:

index="example"

|eval type1=mvindex(split(type,":"),0)

|eval type2=mvindex(split(type,":"),1)

|search

[|inputlookup myfile.csv 

| eval range=mvrange(1,3)

| mvexpand range

| eval type2=if(range==2,type,null)

| eval type1=if(range==1,type,null)

| table type1 type2]

|table type1 type2

 

Thanks

0 Karma

ITWhisperer
Legend

Please explain what the issue is in more detail.

0 Karma

Shimon81
Explorer

I have in my search base a field named 'type' that I need to split into type1 and type2 and to check if one of them exists in my csv file. My search at the moment is giving me a result that both types do not exist in the csv file, this is my query at the moment: 

 

index="example"

|eval type1=mvindex(split(type,":"),0)

|eval type2=mvindex(split(type,":"),1)

|search

[|inputlookup myfile.csv 

| eval range=mvrange(1,3)

| mvexpand range

| eval type2=if(range==2,type,null)

| eval type1=if(range==1,type,null)

| table type1 type2]

|table type1 type2

 

Thanks

 

 

0 Karma

manjunathmeti
Champion

hi @Shimon81,

Your approach is right. You need to rewrite the query.

index="example" type1 IN([|inputlookup myfile.csv | stats values(type) as types | return $types]) OR type2 IN([|inputlookup myfile.csv | stats values(type) as types | return $types])
| table type1 type2

 

If this reply helps you, a like would be appreciated.

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!