Now, my question: I want to have a list of hostnames that are occurring in sources "SCCM" and "RAIN", but not in source "TopDesk". Who can help me out? Bonus question: is using coalesce the best possible way to correlate over the different (hostname-) fields?
I think you are OK to use coalesce. The only thing I can think of that is more explicit would be using a case statement.
Regardless of how you determine the host_new field, here is a query that will show you the count by host and sourcetype (assuming "SCCM", "Rain", "TopDesk", etc. are differentitated by sourcetype). If sourcetype is not correct, then change that field to the correct field that identifies the sources.
<YOUR QUERY> ....
| eval host_new=coalesce(HostName,COMP_Name,'Object ID', ...)
| chart count by host_new sourcetype
| where (SCCM > 0 AND Rain > 0 AND TopDesk = 0)