topic Re: Join Datasets from two sourcetypes but only display them if there is a dataset on the left side in Splunk Search

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

davidb89 — Tue, 29 Sep 2020 14:54:29 GMT

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

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

niketn — Thu, 13 Jul 2017 07:40:48 GMT

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.

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

davidb89 — Thu, 13 Jul 2017 07:55:17 GMT

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.

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

niketn — Sat, 15 Jul 2017 18:51:40 GMT

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

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

cmerriman — Tue, 29 Sep 2020 14:57:28 GMT

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?

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

DalJeanis — Tue, 29 Sep 2020 14:55:43 GMT

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")