Splunk Search

Searching two indexes to compare and show the difference

SGun
Explorer

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.

Tags (1)
0 Karma

jplumsdaine22
Influencer

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

SGun
Explorer

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,

0 Karma

jplumsdaine22
Influencer

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

SGun
Explorer

No results on this one .

0 Karma

jplumsdaine22
Influencer

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

0 Karma

SGun
Explorer

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,

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...