How to include unique attributes to the result after comparing 2 different lists from 2 different searches ?

[vincentgoh98]

Hi community, 

I have 2 different lists with fields as follow :

list A - ip_address, source, account_id

list B - ip_address, source, account_id, field4, field5

I want to compare both lists to accomplish list(B) - list(A), ie. remain only list(B) entries with unique ip_address value, comparing to list(A) entries, while also return the field value of field4 and field5. 

Example 

list A

ip_address	source	account_id
10.0.0.1	A	1000
192.168.0.1	A	1001

list B 

ip_address	source	account_id	field4	field5
10.0.0.2	B	999	xxx	yyyy
192.168.0.1	B	1001	xxy	yyyx

Result

ip_address	source	account_id	field4	field5
10.0.0.2	B	999	xxx	yyyy

I have tried the following :

index=seceng source="listB"
| eval source="B"
| fields ip_address source account_id field4 field5
| append 
[ | inputlookup listA
  | eval source="A" 
  | fields ip_address source account_id]
| stats values(source) as source, count by ip_address account_id field4 field5
| where count == 1 AND source == "B"

The issue of this query is that since field4 and field5 are unique attributes for list(B) only, thus the stats query will only return list(B) entries. It works when the field4 and field5 removed from the stats query, but they are the attributes that I want to include to the result.

Can anyone give me suggestion of how the expected result can be accomplished ? Really appreciate that, and thanks in advance !

[gcusello · Accepted solution]

Hi @vincentgoh98,

please try something like this:

index=seceng source="listB"
| eval source="B"
| fields ip_address source account_id field4 field5
| append 
[ | inputlookup listA
  | eval source="A" 
  | fields ip_address source account_id]
| stats 
    values(source) AS source 
    dc(source) AS dc_source 
    values(field4) AS field4 
    values(field5) AS field5 
    BY ip_address account_id 
| where dc_source=1 AND source="B"
| table ip_address account_id field4 field5

Ciao.

Giuseppe

[vincentgoh98]

Hi @gcusello ,

Thank you very much for your help ! I am pretty new to Splunk and thus never think of using dc() function. ! It does work for me, and I will mark it as solution.