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
( 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
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")
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.
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.
Please add data examples as well, to explain as to what you mean by dataset on the left side.
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?