How to join searches from two indexes?

[chrispounds]

Hi all,

I've been looking up information about Joins ect, but can't seem to get mine to output so i'm wondering if you can help.

i have a search:

index=netfw message_tag=RT_FLOW_SESSION_DENY  | lookup emotet_ip.csv lookup_ip AS dest| search rule=emotetc2block 
| stats count by dest src_ip 
| sort -count

I then have a search on another index, my dhcp index, to try and put some info around the dest IP's that i'm getting back:

index="netdhcp" ip=redacted
| table ip nt_host

What i ideally want to do, is pull the IPs found in the first search, then add context from the dhcp search, then output to table. I understand you need a common field, in this case, i can rename dest AS ip if needed.

Any ideas?
Many thanks

[VatsalJagani]

@chrispounds - Please visit my answer below, that might help.

[VatsalJagani]

Hi @chrispounds,

Please try below search if you just want fields from the second search.

index="netdhcp" [index=netfw message_tag=RT_FLOW_SESSION_DENY | lookup emotet_ip.csv lookup_ip AS dest| search rule=emotetc2block | stats count by dest src_ip | rename src_ip as ip]
| table ip nt_host

Let's understand the above search.
For example, your first search gives below results:

dest src_ip count
  A     B     5
  C     D     2

Your Final search would internally look like:

index="netdhcp" (ip="B" OR ip="D")
| table ip nt_host

Above will consider only src_ip in search for ip, if you want dest also to be found you can try:

index="netdhcp" [index=netfw message_tag=RT_FLOW_SESSION_DENY | lookup emotet_ip.csv lookup_ip AS dest| search rule=emotetc2block | stats count by dest src_ip | eval ip=mvappend(dest,src_ip) | mvexpand ip | table ip]
| table ip nt_host

Now final search would look like:

index="netdhcp" (ip="A" OR ip="B" OR ip="C" OR ip="D")
| table ip nt_host

I hope this helps!!

[chrispounds]

hi @VatsalJagani,

Is there a way i can include the rule field, to make sure it's pulling IP's relating to the rule?

That last search is pulling back some results, so keen to tune it further 🙂

Many thanks,
Chris

[chrispounds]

Also - i'm probably better off outputting the results of the firewall search command to an outputs lookup file which is appended every so often, then that way it pulls in the latest data?

[VatsalJagani]

Is this rule field is part of index="netdhcp"?
If yes, then you can add that field with table, that will display.

[martynoconnor]

Hi Chris,

Does your organisation tend to use relatively static host/IP combinations? i.e. because of the lease time and the number of users/working hours, they don't change very often? If so, an easy way to achieve what you want would be to have the netdhcp index run as a scheduled search to populate a lookup, which you can then reference that lookup in your first search. This would avoid having to do something like joins which can be computationally expensive.

If you're not familiar with using searches to populate lookups, you can see how to do so in the documentation here: https://docs.splunk.com/Documentation/Splunk/7.2.6/Alert/OutputToCSVLookup

Regards,

Martyn

[chrispounds]

Hi Marty,

I do have an alert set that sends the results to an output lookup

index=netfw message_tag=RT_FLOW_SESSION_DENY | lookup emotet_ip.csv lookup_ip AS dest| search rule=emotetc2block
| stats count by dest src_ip
| rename dest AS ip
| sort -count

runs every hour to emotet_output.csv.

I need to incorporate this into my netdhcp search, which i'm struggling with!

Many thanks,
Chris

[martynoconnor]

So once you have populated your lookup using that search you can then just call that lookup in the netdhcp search in order to enrich your data, in much the same way as you have in the first search you posted in this thread. So:

netfw search populates a lookup
netdhcp search uses netfw lookup for data enrichment

[aromanauskas]

Here are a few options that could point you in the right directions.

(index=netfw message_tag=RT_FLOW_SESSION_DENY) OR (index="netdhcp" ip=*)| lookup emotet_ip.csv lookup_ip AS dest| search rule=emotetc2block OR index="netdhcp" |eval dest=coalesce(dest,ip)
| stats count,values(nt_host) AS nt_host by dest src_ip
| sort -count

OR

(index=netfw message_tag=RT_FLOW_SESSION_DENY) OR (index="netdhcp" ip=*) |eval dest=coalesce(dest,ip)
| stats count,values(nt_host) AS nt_host,values(src_ip) AS src_ip by dest|mvexpand src_ip| lookup emotet_ip.csv lookup_ip AS dest| search rule="emotetc2block"