Archive
Highlighted

Matching values from a subsearch using append

Path Finder

I'm having an issue with matching results between two searches utilizing the append command. I realize I could use the join command but my goal is to create a new field labeled Match.

index=type1 EVENT_TYPE=Blah1 KEYFIELD=*
| append
[search index=type2 EVENT_TYPE=Blah2
| eval KEYFIELD2=field1.field2.field3.field4
| fields KEYFIELD2]
| eval results1=if(KEYFIELD=KEYFIELD2,"Match","No Match") 
| eval results2=if(match(KEYFIELD ,KEYFIELD2),"Match","No Match") 
| eval results3=if(like(KEYFIELD ,"%".KEYFIELD2."%"), "Match","No Match") 

Even though I know there are "matches", my results only come back as No Match.
Any assistance on this would be greatly appreciated.

Tags (1)
Highlighted

Re: Matching values from a subsearch using append

Contributor

You are appending records, which doesn't put them side-by-side. So you will never have a record that has both KEYFIELD and KEYFIELD2 to compare to each other. You will need to use a stats command that correlates the two datasets. What happens if you try this?

index=type1 EVENT_TYPE=Blah1 KEYFIELD=*
| append
[search index=type2 EVENT_TYPE=Blah2
| eval KEYFIELD=field1.field2.field3.field4
| fields KEYFIELD, index]
| stats dc(index) as index_count by KEYFIELD
| sort -index_count

There should be some values of KEYFIELD that have an indexcount of 2 if there are matches. To filter them, add `|search indexcount > 1` to the search.

0 Karma
Highlighted

Re: Matching values from a subsearch using append

Esteemed Legend

Like this:

(index=type1 EVENT_TYPE=Blah1 KEYFIELD=*) OR (index=type2 EVENT_TYPE=Blah2)
| eval KEYFIELD=coalesce(KEYFIELD, field1.field2.field3.field4)
| stats values(*) AS * dc(index) AS index_count BY KEYFIELD

Then add some combination of logic using index=... AND/OR index_count=

Highlighted

Re: Matching values from a subsearch using append

Path Finder

I still required a field/column within my table stating Match or No Match. In order to accomplish this, I ended up creating a lookup file

lookup ProofOfConcept.CSV KEYFIELD as KEYFIELD OUTPUTNEW KEYFIELD as KEYFIELD2
| eval results1=if(KEYFIELD=KEYFIELD2,"Match","No Match")

When run over the last 24hrs I had both matches and no matches populate, which was to be expected.

0 Karma