Splunk Search

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

davidb89
Engager

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

0 Karma

DalJeanis
Legend

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

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

davidb89
Engager

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.

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

cmerriman
Super Champion

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?

0 Karma
Get Updates on the Splunk Community!

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...