Searching two indexes to compare and show the difference

[SGun]

index="proxy_logs" category="none"
| top category, protocol, url, cs_Referer limit=1000
| eval results = if(match(upper(cs_Referer),upper(url)), "hit", "miss")
| where results="miss"
| table category, protocol, url, cs_Referer, results

Above is working thanks to a couple of posts on here.

No I want to compare the "url" field in index1 against another index2 that also has the "url" field and show the output of index1 that does not match index2.

First search looks for items that don't match in the first index.

I then want to search the search the second index and output only items that do not match the first index.

[jplumsdaine22]

Something like this might do the trick. This should who you any url in index1 only (ie no match in index2)

foo (index=index1 OR index=index2) 
| stats values(index) as indexes by url 
| where mvcount(indexes)=1 AND indexes="index1"

[SGun]

I was hoping to include, as I want to check the output is a miss.

| eval results = if(match(upper(cs_Referer),upper(url)), "hit", "miss")
| where results="miss"

Thanks,

[jplumsdaine22]

This will work | eval results = if(match(upper(cs_Referer),upper(url)), "hit", "miss") provided an event contains two fields. I assume one index has cs_referer and the other has url. What fields correlates the events between indexes?

For example, lets say each event has a field requestID, that is identical in both indexes. Then you might do:

(index=index1 OR index=index2) 
| stats values(cs_referer) AS cs_referer values(url) as url by requestID
| eval results = if(match(upper(cs_Referer),upper(url)), "hit", "miss") 
| where results="miss"

[SGun]

No results on this one .

[jplumsdaine22]

is your eval working? ie take out the where clause. Also check my spelling of cs_referer (field names are case sensitive)

[SGun]

index="proxy_logs" OR index="websitelist" category="none" cs_Referer!="-"
| stats values(cs_Referer) AS cs_Referer values(url) as url by requestID
| eval results = if(match(upper(cs_Referer),upper(url)), "hit", "miss")

| where results="miss"

produces no results

index="proxy_logs" OR index="websitelist" category="none" cs_Referer!="-"
| stats values(index) as indexes by url
| where mvcount(indexes)=1 AND indexes="bcoat_logs"

Produces results, but I need to confirm the output data.

What I am trying to do is look up all the urls in the proxy_logs where the url with an unknown category that does not match the cs_Referer then mark it as a "miss".

Then check the "miss" results against the second index "websitelist" url

Other issues is that the "proxy_logs" outputs url not just as a domain name... http:// , www. etc. The "websitelist" has just the domain.

Thanks,