Splunk Search

How to check if record exists in another index?

michaelrosello
Path Finder

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

mayurr98
Super Champion

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!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...