topic Re: How to compare column from two searches and find the difference between them and print all rows? in Splunk Search

How to compare column from two searches and find the difference between them and print all rows?

hakusama1024 — Mon, 26 Feb 2018 18:28:50 GMT

Thanks for your time. Im using splunk to parse the log.
I have two search. the columns i got from A is as below

tktnum, prcnum, type
Columns for search B is 
tktnum, _time.

How may I find tktnum existed in A but not in B then print table like
tktnum(In A not B), prcnum, type

search A with tktnum, prcnum, type 
| stats values(tktnum) as TKT1, values(prcnum) as PRCNUM, values(ftrectyp) as TYPE
    | appendcols [search B with tktnum] 
    | stats values(tktnum) as TKT2 ] 
| makemv TKT1
| makemv TYPE
| mvexpand TKT1
| mvexpand TYPE
| where not match(TKT2, TKT1) 
| table TKT1, TYPE

I do get the tktnum which exist in A, not in B. But the problem is TYPE not match. Can anyone help on it.
The type should be corresponding to that tktnum from the original row, but now I got is different.

Re: How to compare column from two searches and find the difference between them and print all rows?

elliotproebstel — Mon, 26 Feb 2018 19:31:01 GMT

This is pretty straightforward since the results from Search B contain a field that's not present in Search A. Combine the two searches at the outset so that you first gather all results from both searches. Then use the eventstats command to copy the _time field from results from Search B to results from Search A with the same tktnum, and finally filter down to only events that don't contain a _time field, as these are tickets that had results in Search A but not in Search B.

[ combined Search A and Search B ]
| eventstats first(_time) AS _time BY tktnum
| where isnull(_time)

Re: How to compare column from two searches and find the difference between them and print all rows?

somesoni2 — Mon, 26 Feb 2018 20:06:06 GMT

How about this?

search A with tktnum, prcnum, type
| where NOT [search B with tktnum _time | table tktnum]
| renamte tktnum as "tktnum(in A not B)"

Based on type of queries for search A, you could actually move the NOT filter to base search of A (search portion before first pipe symbol).

Re: How to compare column from two searches and find the difference between them and print all rows?

Taruchit — Tue, 09 Aug 2022 12:46:01 GMT

Hi @somesoni2 and @elliotproebstel,

I also have a similar requirement of searching results based on a column.
The below is the search results of SPL: -

index	host	pair	rectype
idx1	hostA	Pair1	index lookup
idx1	hostB	Pair1	lookup
idx2	hostC	Pair2	index lookup
idx2	hostD	Pair3	lookup
idx2	hostE		lookup
idx3	hostF		index lookup

In the above table, I have idx1 which has 2 host values: hostA, hostB; but, common pair value: Pair1.
rectype is a multi-value field, for hostA, rectype is "index lookup" and for hostB, rectype is "lookup".

I need your help to filter such records where if pair value is same and rectype values are different, along with rectype cell having values "index lookup".

Thus, from the above search output, I need to filter out rows having value: - hostA, hostB, hostC, hostF; and display rows with values with hostD, hostE.

Reason for filtering rows with hostA and hostB: -
-> Pair value is same.
-> rectype values are different.

Reason for filtering out hostC: -
-> rectype value is "index lookup".

Reason for filtering out hostF: -
-> rectype value is "index lookup".

Thus, the expected output after applying filter on above search result is: -

index	host	pair	rectype
idx2	hostD	Pair3	lookup
idx2	hostE		lookup

Please help by sharing your inputs.

Thank you

Re: How to compare column from two searches and find the difference between them and print all rows?

somesoni2 — Tue, 09 Aug 2022 13:39:32 GMT

Give this a try

Your current search with field index host pair rectype | eventstats dc(rectype) as rectypes by index pair | where NOT (rectypes=2 OR (mvcount(rectype)=2 AND isnotnull(mvfind(rectype,"(index|lookup)"))))

Re: How to compare column from two searches and find the difference between them and print all rows?

Taruchit — Tue, 09 Aug 2022 17:47:10 GMT

Thank you @somesoni2 for sharing your inputs. Your logic worked perfectly with the usecase.

I tried the below as well and it gave me results same as yours: -

|eventstats dc(rectype) as rectypes by index, pair |where NOT (rectypes=2) AND rectype="lookup" AND rectype!="index"

Do you find if I have missed any condition by removing mvcount and isnotnull from the code you shared?

Thank you

Re: How to compare column from two searches and find the difference between them and print all rows?

somesoni2 — Tue, 09 Aug 2022 19:06:50 GMT

Will following row be included in the result? Will there be any record with just rectype=index?

idx4

hostG

Pair4

index

If no, then your logic is correct. In fact, 2nd condition is (below) is not required. (if rectype=lookup, it'll never be index, so it's a deadcode)

AND rectype!="index"

Re: How to compare column from two searches and find the difference between them and print all rows?

Taruchit — Thu, 11 Aug 2022 12:01:03 GMT

Hi @somesoni2,

Thank you for your prompt response.

In my case, I do have records with rectype=index.

But, for my current usecase I need to filter out those records, so it seems the code logic is fine.

Thank you for your help.