How to make a "join" work even when the events or sourcetype on one side don't exist

[thisissplunk]

I've got a query that uses a join to join events from two different sourcetypes. Sometimes the second sourcetype doesn't exist yet, and this breaks the entire query.

How can I try to determine if the sourcetype exists, and do something else that won't break the join?

Ex:

index=customer1 sourcetype=host_info
| join hostname [search index=customer1 sourcetype=dhcplogs]
| stats count by dhcp_ip _time

Sometimes we don't have dhcplogs for certain customers, and this query breaks on those.

[woodcock]

Like this:

index=customer1 AND (sourcetype=host_info OR sourcetype=dhcplogs)
| fillnull dhcp_ip value="N/A"
| stats count by dhcp_ip _time

[DalJeanis]

Try something like this...

  index=customer1 sourcetype=host_info
 | fields _time hostname
 | join type=left hostname [search index=customer1 sourcetype=dhcplogs | table hostname dhcp_ip  ]
 | eval dhcp_ip = coalesce(dhcp_ip,"((Not found))")
 | stats count by dhcp_ip _time

---

Technically, _time doesn't need to be in the fields command, but I wanted to show where I believed it was coming from.

[MuS]

Hi there,

here is a hint how it could be done https://answers.splunk.com/answers/176466/how-to-use-eval-if-there-is-no-result-from-the-bas-1.html or read this to replace or this one https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to replace the join with a stats 😉

cheers, MuS