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 Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...