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.
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?