Join Datasets from two sourcetypes but only display them if there is a dataset on the left side

[davidb89]

This Question is based on this question which solved my initial problem but created a new one. No matter which of this solutions I use:

 sourcetype="pfsensefirewall" source_address=10.0.2.10 firewall_action=block | join type=left source_address, source_port, destinatin_port, destination_address [search sourcetype=nxlog_sysmon  | stats count by SourceIp SourcePort DestinationPort DestinationoIp | rename SourceIp AS source_address, SourcePort AS source_port, DestinationPort AS destination_port, DestinationIp AS destination_address] | table source_address, destination_address, destination_port, Image

Or

 ( sourcetype="pfsensefirewall" source_address=10.0.2.10 firewall_action=block) OR (sourcetype=nxlog_sysmon)
 | eval source_address=coalesce(source_address,SourceIp) | eval source_port=coalesce(source_port,SourcePort)
 | eval destination_address=coalesce(destination_address,DestinationIp) | eval destination_port=coalesce(destination_port,DestinationPort)
 | stats values(Image) as Image by source_address, destination_address, destination_port

It basicly works but then it also displays datasets that are only present on the right side but not on the left side. I want to have all the datasets from the left side extended by fields from the right side. Or in a more comprehensible way: I want to extend the entries from our firewalls by the entries generated on our endpoints but I don't want to have endpoint information without a correlating dataset from our firewalls.

// The fields available and relevant for this sourcetypes are:

pfsenesefirewall: source_address, destination_address, message_section, protocol, source_port, destination_port, firewall_ip, tcp_flags
nxlog_sysmon: SourceIp, DestinationIp, SourcePort, DestinationPort, Protocol, Image

[DalJeanis]

Replace your stats with these -

| stats values(Image) as Image, values(sourcetype) as sourcetype, values(source_port) as source_port by source_address, destination_address, destination_port
| search match(sourcetype,"pfsensefirewall")

[niketn]

Since we are not seeing the visualization/table, it would be more helpful if you add field name for left side and right side. Looking at your query I think you need rows which have source_address always present. You can add isnotnull() condition check accordingly. Here is updated second query.

<YourBaseSearch>
| stats values(Image) as Image by source_address, destination_address, destination_port
| where isnotnull(source_adddress)

Please try out and let us know.

[davidb89]

Thanks for your answer but this does not work. When I add the where isnotnull(source_adddress) splunk does not find any results anymore. I also added the available fields to my initial post.

[niketn]

Please add data examples as well, to explain as to what you mean by dataset on the left side.

[cmerriman]

have you double checked that you have data that meets the criteria. you have a SourceIp, DestinationIp, SourcePort and DestinationPort from nxlog_sysmon that all match to a source_address, destination_address, destination_port and source_port from pfsenesefirewall?

I see you're looking specifically at source_address=10.0.2.10 above, in pfsenesefirewall, does SourceIp=10.0.2.10 in nxlog_sysmon exist with the exact same other criteria you're matching on?