Subesearch with Inputlookup

[Shimon81]

 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

[ITWhisperer · Accepted solution]

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 

[Shimon81]

@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

[ITWhisperer]

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)

[Shimon81]

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

[ITWhisperer]

Please explain what the issue is in more detail.

[Shimon81]

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

[manjunathmeti]

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.