How to check if record exists in another index?

michaelrosello — Wed, 28 Feb 2018 12:32:43 GMT

I have two tables containing ticket numbers:

table 1

TicketNumber 
1             
2 
3

table 2

TicketNumber
2
3
4

Now I want to create a search using table2 but with an additional field if it exists in table1.

TicketNumber ExistingInTable1?
2             Yes
3             Yes
4             No

Re: How to check if record exists in another index?

mayurr98 — Wed, 28 Feb 2018 13:01:17 GMT

hey try this run anywhere search

| makeresults 
| eval TicketNumber="2 3 4" 
| makemv TicketNumber 
| mvexpand TicketNumber 
| table TicketNumber 
| join type=outer TicketNumber 
    [| makeresults 
    | eval TicketNumber="1 2 3" 
    | makemv TicketNumber 
    | mvexpand TicketNumber 
    | table TicketNumber 
    | eval ExistingInTable1?="Yes"] 
| fillnull value="No"

In your environment, you should try

<base table 2 search> 
| dedup TicketNumber 
| table TicketNumber 
| join type=outer TicketNumber 
    [ <base table 1 search> 
    | dedup TicketNumber 
    | table TicketNumber 
    | eval ExistingInTable1?="Yes"] 
| fillnull value="No"

Considering the performance factor, the best way to achieve this is

suppose you have table1 and table2 in different indexes or sources or sourcetypes
I am not sure it will work or not but you can always give it a try

index=table1 OR index=table2  | stats dc(index) as dc values(eval(if(index="table2",1,0))) as table2 by TicketNumber  | search table2=1 | eval ExistingInTable1?=if(dc=2,"Yes","No") | table TicketNumber ExistingInTable1?

you can choose source or sourcetype instead of index.
let me know if this helps!

topic Re: How to check if record exists in another index? in Splunk Search

How to check if record exists in another index?

Re: How to check if record exists in another index?