trying to do join between same column name ,expect to get only the result if the site ID is equal?

[Avichai]

this is my table:

moduleName  siteName    companyDUNS siteID
abc         site1          1111      16682
bbb         site2          22222          6956
ccc          site3         3333       28118

this is my lookup table(suppliers):

SiteName    DUNS    SiteID
site2          1111 16682
site3           666  16684
site1      55555566 55555

this is my query:

|inputlookup Suppliers | mvexpand SiteID |join Site ID:' [search sourcetype=UserActivity  | rex field=_raw " Module name: (?.*), Site name: (?.*), Site ID: (?.*), Site Duns: (?.*), User full name: (?.*), User ID: (?.*), User roles: (?.*)"  | search moduleName=incominggeneralledger
| table _time,moduleName,siteName,companyDUNS,siteID,Userfullname,UserID,UserRole]

expected result:

moduleName  siteName    companyDUNS siteID  SiteName    DUNS    SiteID
abc         site1           1111     16682      site2    1111   16682

from this result i want only

moduleName  siteName    companyDUNS siteID 
abc          site1  1111                   16682 

Thanks

[kamlesh_vaghela]

@Avichai

Can you please try this search?

search sourcetype=UserActivity 
| rex field=_raw " Module name: (?.), Site name: (?.), Site ID: (?.), Site Duns: (?.), User full name: (?.), User ID: (?.), User roles: (?.*)" 
| search moduleName=incominggeneralledger 
| table _time,moduleName,siteName,companyDUNS,siteID,Userfullname,UserID,UserRole | lookup Suppliers siteID OUTPUTNEW SiteName as SiteNameNew
| where isnotnull(SiteNameNew)
| table moduleName siteName companyDUNS siteID 

Note: I took this search from your questions. You have to replace original search before executing.

[Avichai]

thanks,
i didn't got any results

[kamlesh_vaghela]

Try this one:

YOUR_MAIN_SEARCH
| table moduleName siteName companyDUNS siteID 
| lookup Suppliers SiteID as siteID 
| where isnotnull(DUNS) 
| table moduleName siteName companyDUNS siteID

My Sample Search:

| makeresults 
| eval _raw="
moduleName    siteName    companyDUNS    siteID
 abc            site1           1111         16682
 bbb            site2           22222          6956
 ccc             site3           3333          28118
 " 
| multikv 
| table moduleName siteName companyDUNS siteID 
| lookup Suppliers SiteID as siteID 
| where isnotnull(DUNS) 
| table moduleName siteName companyDUNS siteID

[Avichai]

i tried the first query:
got no results.

how can i try the second query i have lots of records?