topic Re: How to compare two fields from two different searches and display results with matches and mis-matches? in Splunk Search

How to compare two fields from two different searches and display results with matches and mis-matches?

hasham19833 — Tue, 25 Jun 2019 08:10:47 GMT

I am running 2 different searches and have to compare the each value in one field with the values in the other field. The display result should show a match or a mismatch against each value.

given data:

Field A:
1111
2222
2424
3333
4444

Field B:
3333
1111
4444
3344

Results should be something like this table:

Field A -- Field B ---- results
1111 --- 1111  ----match
2222----            ---- no-match
3333----  3333 ---- match
4444---- 44444----match
2424 ----           ----  no match
         ----   3344 ---- not match

Re: How to compare two fields from two different searches and display results with matches and mis-matches?

DavidHourani — Tue, 25 Jun 2019 10:27:17 GMT

Hi @hasham19833,

Are those values in the same events, could you share your search ?

If the fields are on the same line it should be like this :

YourBaseSearch 
| eval results=if(FieldA==FieldB, "match","not match")

Or else you will have to join both events and then add the logic above.

Cheers,
David

Re: How to compare two fields from two different searches and display results with matches and mis-matches?

hasham19833 — Tue, 25 Jun 2019 12:10:26 GMT

See the search query below; So its searching from 2 different indexes, index AS is event based, and the field "eventKey" appears in every result. While the 2nd field is from index DS and is extracted from a large xml file, so im guessing its multivalue field. That field is "availId". Now i want to see which individual values from 'availId' is matching with the values of field eventKey.

(index=DS sourcetype=SCHed source="/archived/ABC.SCH") OR (index=AS sourcetype=ASED "Finished" earliest=-19hr latest=-7hr )
| spath output=availId path=Schedule.Window.Avail{@availId}
| mvexpand availId
| eval results=if(availId==eventKey, "match","not match")
| table availId results

Re: How to compare two fields from two different searches and display results with matches and mis-matches?

DavidHourani — Tue, 25 Jun 2019 12:17:24 GMT

In your case availID and eventKey are never on the same line. You need to join the events from the two indices using a key.

Something like

...
|stats values(availID) as availID values(eventKey) as eventKey by JoinKey
...

Re: How to compare two fields from two different searches and display results with matches and mis-matches?

hasham19833 — Tue, 25 Jun 2019 12:46:29 GMT

this is not giving out any results. there is nothing common between the 2 events, except the IDs, which are again by different field titles. is there anyway that i can combine the values from 2 fields into one field so that all the ids (even duplicates or common are in the same field)?

Re: How to compare two fields from two different searches and display results with matches and mis-matches?

ilyaresh — Wed, 26 Jun 2019 01:24:20 GMT

Following on hasham19833 answer (https://answers.splunk.com/comments/754583/view.html)
This search

(index=tst_aaa AND id_a=*) OR (index=tst_bbb AND id_b = *) 
| eval id = if(isnull(id_a), id_b, id_a)
| stats values(id_a) as id_a values(id_b) as id_b by id
| eval m = if(isnull(id_a) OR isnull(id_b), "not matched", "matched")

will produce

id    id_a    id_b  m
1111    1111    1111    matched
2222    2222            not matched
2424    2424            not matched
3333    3333    3333    matched
3344           3344 not matched
4444    4444    4444    matched

Re: How to compare two fields from two different searches and display results with matches and mis-matches?

jnudell_2 — Wed, 26 Jun 2019 02:26:45 GMT

Hi @hasham19833 ,

You might try something like this:

(index=DS sourcetype=SCHed source="/archived/ABC.SCH") OR (index=AS sourcetype=ASED "Finished" earliest=-19hr latest=-7hr )

| spath output=availId path=Schedule.Window.Avail{@availId}

| eval AllKeys = coalesce(eventKey, availId)

| stats values(eventKey) as eventKey values(availId) as availId by AllKeys

| eval CheckMatch = if(eventKey == availId, "Match", "No Match")

This works if availId is NOT a multi-value field. If it IS a multi-value field, you will need to do something like this:

(index=DS sourcetype=SCHed source="/archived/ABC.SCH") OR (index=AS sourcetype=ASED "Finished" earliest=-19hr latest=-7hr )

| spath output=availId path=Schedule.Window.Avail{@availId}

| eval availId = if(isnull(availId), "-", availId) 

| mvexpand availId 

| eval availId = case( availId!="-", availId )

| eval AllKeys = coalesce(eventKey, availId) 

| stats values(eventKey) as eventKey values(availId) as availId by AllKeys 

| eval CheckMatch = if(eventKey == availId, "Match", "No Match")

Here is what it might look like: