topic Re: I have 2 files in csv format. I want to search whether one of the unique field is present in second file. in Dashboards & Visualizations

2 files in csv format: How to search whether one of the unique field is present in second file?

yuvasree — Thu, 21 Jul 2022 15:23:44 GMT

I have 2 files in csv format. I want to search whether one of the unique field is present in second file. if it is present mark the value as true else false. Kindly help with the command

Re: I have 2 files in csv format. I want to search whether one of the unique field is present in second file.

gcusello — Wed, 20 Jul 2022 12:17:21 GMT

Hi @yuvasree,

it depends on one detail:

do you want to search the values from the first csv ina field of the second or as free text search?

supponing that both the csv were uploaded in an index (your_index) with two sourcetypes (sourcetype1 and sourcetype2).

if in a field and they are named field1 (in sourcetype1) and field2 (in sourcetype2), you have to run something like this:

index=your_index sourcetype=sourcetype1 [ | search index=your_index sourcetype=sourcetype2 | rename field2 AS field1 | fields field1 ] | ...

the most important thing is that the field name at the end of the subsearch is the same of the main search.

If instead you want to performa a free text search, you have to run something like this:

index=your_index sourcetype=sourcetype1 [ | search index=your_index sourcetype=sourcetype2 | rename field2 AS query | fields query ] | ...

Ciao.

Giuseppe

Re: I have 2 files in csv format. I want to search whether one of the unique field is present in second file.

yuvasree — Wed, 20 Jul 2022 12:50:16 GMT

Thanks for the reply. If i have the same field name in both the files by which command can i mark the new field as true if existing in second file else false. Can i use

Re: I have 2 files in csv format. I want to search whether one of the unique field is present in second file.

gcusello — Wed, 20 Jul 2022 13:07:00 GMT

Hi @yuvasree,

in this way you filter results and you take only the one present in both the searches.

if you want to mark as present or not present, you have to run something like this:

index=your_index (sourcetype=sourcetype1 OR sourcetype=sourcetype2 | stats values(*) AS * dc(sourcetype) AS sourcetype_count BY field | eval status=if(sourcetype_count=2,"Both",if(sourcetype="sourcetype1","sourcetype2")) | table field status

Ciao.

Giuseppe

Re: I have 2 files in csv format. I want to search whether one of the unique field is present in second file.

yuvasree — Thu, 21 Jul 2022 12:11:38 GMT

@gcusello Thanks for the prompt response. It worked.

But when i am executing the provided command getting the error in eval as malformed function. But if I execute like this it works fine. How can i validate the source file name as well so that it won't have wrong result if duplicates present.

| eval status=if(sourcetype_count=2,"Both","Not present")

Re: I have 2 files in csv format. I want to search whether one of the unique field is present in second file.

gcusello — Thu, 21 Jul 2022 12:15:45 GMT

Hi @yuvasree,

yes i frgot a piece:

Anyway,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉