Splunk Search
Highlighted

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

New Member

Hi

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.

0 Karma
Highlighted

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

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)
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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).

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.