topic Re: Matching values from a subsearch using append in Splunk Search

Matching values from a subsearch using append

cquinney — Thu, 01 Aug 2019 18:20:42 GMT

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.

Re: Matching values from a subsearch using append

grittonc — Wed, 30 Sep 2020 01:34:39 GMT

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 index_count of 2 if there are matches. To filter them, add |search index_count > 1 to the search.

Re: Matching values from a subsearch using append

woodcock — Fri, 02 Aug 2019 03:51:25 GMT

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=

Re: Matching values from a subsearch using append

cquinney — Fri, 02 Aug 2019 21:09:22 GMT

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.