topic Re: search one index with sourcetype from another index in Splunk Search

search one index with sourcetype from another index

hartfoml — Mon, 03 Jun 2013 19:47:46 GMT

I have an index with several thousand entries, like a lookup table. I would like to use these entries to search my proxy logs for a match.

I would like to have the output show the value from the original index and the matching data from the proxy logs.

sourcetype=proxy  dest_host=* [| search index=foo sourctype=domain_name ] | top domain_name dest_host
OR 
sourcetype=proxy dest_host=* [| search index=foo sourcetype=domain_name | rename domain_name AS query | fields query ] | top dest_host

I can't seem to get it right.

Re: search one index with sourcetype from another index

sideview — Mon, 28 Sep 2020 14:02:00 GMT

If your intention is to search the "foo" index for the "domain_name" sourcetype and then use the "domain_name" value as a free text search against the "proxy" sourcetype, then here you go:

sourcetype=proxy dest_host=* [search index=foo sourcetype=domain_name | fields domain_name | rename domain_name as search | fields search ] | top domain_name dest_host

You were close - you just have to have only the search field in the subsearch pipeline. Otherwise I think it yields out all the fields as kv pairs, plus your one raw-text search term. ( someField="someValue" zomg_the_domain_name someOtherField="someOtherValue" )

Re: search one index with sourcetype from another index

hartfoml — Mon, 28 Sep 2020 14:02:12 GMT

Nick, good to hear from you thanks for helping
I haven't had ample time to really try this but on the first few tries this did not work. It showed zero results in too short a search time. almost like the search for proxy event was blank
The sourcetype=domain_name has a field called name=*.google.com there are 4000 of these name fields
I want to use the name field to search the proxy events to find matches so I want the see the matches from the proxy and the field that it matched on from the foo index
top name dest_host
I ajusted the search you gave me above to use name but got Zero results

Re: search one index with sourcetype from another index

hartfoml — Tue, 04 Jun 2013 16:14:16 GMT

Nick,

I've done some more testing and when I pass the field from the subsearch "| reanme anme as search | fields search" and look for the field name of the string passed to the top level search the string has no field name to reference.

Re: search one index with sourcetype from another index

hartfoml — Mon, 28 Sep 2020 14:02:17 GMT

so in the background I think it looks like this

Sourcetype=proxy dest_host=* "name1" OR "name2" OR name3" OR "name4" | top name dest_host

there is no field with the fieldname of "name" only string searchs

Re: search one index with sourcetype from another index

sideview — Tue, 04 Jun 2013 17:17:57 GMT

Well I made some assumptions in my answer, filling in ambiguities in your question. Notably in my sentence beginning with "If your intention is", note that I said it would do free text searches on the values, not fieldName="value" searches. Can you spell out more explicitly what exactly you're trying to do. I gather you want to search for these values as the values of a field in the proxy sourcetype? What is the field name?

Re: search one index with sourcetype from another index

hartfoml — Tue, 04 Jun 2013 21:08:58 GMT

Thanks again for helping,

I know I'm not saying it quit right but lets see if I can explain.

I have an index with over 4000 domain names that I want to use to search the proxy events as a string to see if any part of the proxy event is a match. The problem is that in the proxy event there are several domain names and if I look at the proxy event I have no way of knowing what the original sting search was.

Re: search one index with sourcetype from another index

hartfoml — Tue, 04 Jun 2013 21:12:29 GMT

In short I wanted the pass the field tag or some tag that would identify which string was used to match the event in the proxy log.

I could do a join with the two indexes but that only matches one field in both indexes

Thanks so much for your help. Let me know if this makes any sense at all.

Re: search one index with sourcetype from another index

sideview — Tue, 04 Jun 2013 22:08:53 GMT

OK. If there's no "domain_name" field in the proxy logs, then that was a bad assumption on my part. How about:

sourcetype=proxy dest_host=* [search index=foo sourcetype=domain_name | rename domain_name as search | fields search] | top dest_host

As far as preserving some indicator for which field the text-searches matched in each proxy event, is there some discrete set of extracted fields that it could match in the proxy log data? Or do you not have any idea what the matching fieldname(s) in the proxy logs might be?

Re: search one index with sourcetype from another index

hartfoml — Mon, 28 Sep 2020 14:02:50 GMT

Nick, this search works and I can use it.

The proxy events have there fields that could potable match the string from the subsearch (Referer, dest_server_suppiername, dest_host) The two important ones are (the Referer and the dest_host). I guess I could do a join first on dest_host then a seperet search to join on Referer.

It would be nice to do just one search and know that an event discovered was discovered using the domain_name=foo.bar.xxx from index=foo

Let me know if you have any more great advice