Multiple queries question

[poiromaniax]

Hey all,

Firstly - the title doesnt actually encapsulate what Im trying to do, Ill try break it down simply:

I have AWS FlowLogs and AWS Route53 DNS resolver logs (in same index, different sourcetypes)

I want to search the FlowLogs but have it do a DNS lookup against the Resolver logs and then output it as a table.

Right now I have a query like:

(index=aws sourcetype=flowlogs)
| lookup dnslookup clientip as dest_ip OUTPUT clienthost as dest_DNS
| lookup dnslookup clientip as src_ip OUTPUT clienthost as src_DNS
| table _time dest_ip dest_DNS dest_port src_ip src_DNS src_port vpcflow_action

However, I would like to have the dest_ip and src_ip lookup against route53 resolver log, and then put THAT result in the table as dest_DNS and  src_DNS

Is this even possible?

[poiromaniax]

Thanks for the answer! I will give it a go

[yuanliu · Accepted solution]

Yes, it is possible to do what you wanted.  In fact, ease of correlation is one big advantage of Splunk and SPL.  You may want to describe how route53 resolver log looks like, however.  Without looking at actual data structure, it is rather hard to prescribe recipe. (This being a Splunk forum, not everyone knows what route53 is.)

This said, I'll make some simplifying assumptions.  Assuming that you have a search with route53 that result in events containing peek_ip and poke_dns.  The following pseudo code can be a direction to try.

(index=aws sourcetype=flowlogs)
| append
  [ route53_search
    | table peek_ip poke_dns ]
| eval peek_ip = if(isnull(peek_ip), dest_ip, peek_ip)
| eventstats values(poke_dns) as dest_DNS by peek_ip
| eval peek_ip = if(isnull(peek_ip), src_ip, peek_ip)
| eventstats values(poke_dns) as src_DNS by peek_ip

Among other caveats, this approach depends a lot on how stable that route53_search is, and whether the logs contain conflicting outputs.  Efficiency-wise, you may want to replace eventstats with stats if you know the exact field list in the final results.