topic Ignore Field in Embedded Search but include it in Final Table in Splunk Search

Ignore Field in Embedded Search but include it in Final Table

ZellNorman — Tue, 29 Sep 2020 17:07:35 GMT

Scenario:
- The data I need is ultimately contained in completely different indeces/sourcetypes
- I have a set of 5 computers and a whitelist of addresses they're supposed to go out to but they're going to others
- Their hostnames are contained in my "ip_to_hostname" sourcetype
*- The addressed they request are contained in my "dns_requests" sourcetype
- The "dns_requests" sourcetype basically gives the "internal_ip" and "destination_ips"
- The "ip_to_hostname" sourcetype has the fields "internal_ip" and "hostname" but does **NOT* have the field "destination_ip"

Problem:
I created an embedded search which first uses "dns_requests" to grab the list of "internal_ip" then passes that to the ultimate search which uses the "ip_to_hostname" sourcetype to resolve the "internal_ip" to the "hostname". The problem is I can't figure out how to make a table which features all 3 needed items making a table of: internal_ip, hostname, destination_ips

Hypothesis:
I think I'll need to use the "join" or "transaction" commands in some capacity here?

Search:
sourcetype=ip_to_hostname [search sourcetype=dns_requests AND direction=outbound AND internal_ip=10.1.1.0/24 | dedup internal_ip | table internal_ip] | table internal_ip, hostname | sort - internal_ip

FLAWED Search:
sourcetype=ip_to_hostname [search sourcetype=dns_requests AND direction=outbound AND internal_ip=10.1.1.0/24 | dedup internal_ip | table internal_ip, destination_ips] | table internal_ip, hostname destination_ips | sort - internal_ip

Re: Ignore Field in Embedded Search but include it in Final Table

ZellNorman — Fri, 08 Dec 2017 15:43:10 GMT

Done this. I used join.

Thanks everyone

Re: Ignore Field in Embedded Search but include it in Final Table

DalJeanis — Fri, 08 Dec 2017 18:42:53 GMT

@ZellNorman - Please post the solution code as an answer and accept the answer, so people can see how it worked. Thanks!

Re: Ignore Field in Embedded Search but include it in Final Table

ZellNorman — Fri, 15 Jun 2018 12:49:12 GMT

@Daljeanis
Sorry about my late reply.

I actually changed it around again so it's not using join but my solution is contained within a Dashboard where $field1$ is either an internal or external IP address (it can be 10.*) and $field2$ is optional and is usually meant to be a remote I or, DNS name if you want to specify it in order to see which internal IP visited a specific foreign address or in the absence of $field2$ see a list of internal IPs visited a remote adddress. It replaces remote IP it finds in the 10.0.0.0/8 range with a "-" if they're returned

$field1$ $field2$ | rex field=_raw "[\s]*(?$field1$)" | rex field=_raw "[\s]*(?[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3})" | rex field=_raw "[\s]*(?[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3});" | rex field=_raw "[\s]*(?10\.[\d]{1,3}\.[\d]{1,3}\.[\d]{1,3})" | eval Assoc_Foreign_IPs=if(cidrmatch("10.0.0.0/8",Assoc_Foreign_IPs), "-", Assoc_Foreign_IPs) | stats values(Assoc_Foreign_IPs), values(DNS_Foreign_IPs_End_Semi_Colon) by specified_internal_ip, sourcetype, Assoc_Other_Internal_IPs | sort - values