Compare two different tables from different sources and get the matching and non matching

[niks987]

Hi All,

Hope you all are doing good.

I have to check 2 table from different sources and get a new table where its says match or not match.

Column1 Column2 One
abc abc match
pqr xyz not match

I tried to use a query to get the details but still it is not working reason maybe due to logs coming on different time. My query

index=main source="Replicationlogs_*.txt" AND sourcetype=replication Store.* 
| rex field=source Replicationlogs_(?<store_number>\d{4}).txt
| search store_number=*
| dedup SCD
| stats count by SCD store_number
| rename SCD as SCD1
| appendcols [search index=main source=*.log sourcetype=nitrogen_logs  (SCD!=: AND SCD!=-) 
| rex field=source (?<store_number>\d{4}).log
| search store_number=*
| dedup SCD
| stats count by SCD store_number
]
| eval Match=if(match(SCD1,SCD),"Yes","No")
| fillnull value=0
| fields - count

And the values i get are like this:-
STORE.brand 0010 No STORE.c-lens-fit
STORE.bridge-size 0010 No STORE.c-lens-issue-history
STORE.c-lens-fit 0010 No STORE.c-lens-payment-history
STORE.c-lens-issue-history 0010 No STORE.contact-detail
STORE.c-lens-payment-history 0010 No STORE.cust-c-lens-contract
STORE.c-lens-status-history 0010 No STORE.cust-c-lens-scheme

17 matches should appear but as they not in same row so the match is not displaying.

Please help me resolve this issue.

Thanks in advance

[gcusello]

HI @niks987,
you could use a different approach because there's the limit of 50,000 results in subsearch (your append search).
Something like this:

(index=main source="Replicationlogs_*.txt" sourcetype=replication Store.*) OR (index=main source=*.log sourcetype=nitrogen_logs  (SCD!=: AND SCD!=-))
| rex field=source Replicationlogs_(?<store_number>\d{4}).txt
| rex field=source (?<store_number>\d{4}).log
| stats values(sourcetype) AS sourcetype dc(sourcetype) AS count by SCD store_number
| eval Status=if(count=2,"Match",if(sourcetype="replication ","Present only in Replication","Present only in nitrogen_logs"))
| table store_number Status

Check only the regexes because I haven't your source to test them.

Ciao.
Giuseppe

[to4kawa]

 index=main (source="Replicationlogs_*.txt" AND sourcetype=replication "Store.*" ) OR (source="*.log" sourcetype=nitrogen_logs  (SCD!=":" AND SCD!="-") )
 | rex field=source "(?<store_number>\d{4})"
 | search store_number="*"
 | stats count dc(sourcetype) as Match by SCD store_number
 | eval Match=if(Match=2,"Yes","No")
 | fillnull value=0
 | fields - count

Hi, how about this?