Dashboards & Visualizations

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

yuvasree
Explorer

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

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

gcusello
Esteemed Legend

Hi @yuvasree,

yes i frgot a piece:

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","sourcetype1","sourcetype2"))
| table field status

Anyway, 

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

View solution in original post

0 Karma

yuvasree
Explorer

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 

0 Karma

gcusello
Esteemed Legend

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

 

0 Karma

yuvasree
Explorer

@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")

 

0 Karma

gcusello
Esteemed Legend

Hi @yuvasree,

yes i frgot a piece:

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","sourcetype1","sourcetype2"))
| table field status

Anyway, 

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma

gcusello
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...