Exclude main search events when JOIN event gives no results

[Bleepie]

Dear Splunk Community,

I have the following query. The main query looks for errors in certain log files. If they are found, an list of events is returned. The RUNID is fetched from the events and is used in the JOIN to fetch profiles that are related to the events. Not all events from the main search have a profile. In that case, the result will be all events from the main search with empty profile collumns. I do not wish to see those events.

Example:

I have 10 events that show errors. 5 of these events have no profile. An event with no profile looks like this:

And an event with a profile looks like this:

My question is: How do I exclude events with no profiles attached to it? I want to get rid of the entire row if no profile is found. How do I achieve this/

index="myIndex" 
host="myHostname1*" OR host="myHostname2*" 
source="/opt/IBM/taddm/dist/log/sensors/*/*.log" 
CTJTD3028E
| table _time, errorcode, IP, runid, profile, _raw
| rex "(?<errorcode>CTJT\w{6})"
| rex field=_raw "(?<runid>\w{16}#)"
| eval runid = replace(runid,".$","")
| eval _time=strftime(_time,"%d/%m/%Y %H:%M:%S")
| rex field=_raw "(?<IP>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
| rex field=_raw "CTJTD3028E(?<_raw>.+)"
| rename errorcode AS "Foutcode"
| rename _raw AS "Foutmelding"
| rename runid AS "RUNID"
| rename _time AS "Datum"
| dedup Foutcode, IP 
| join type=left RUNID
    [ search index="myIndex" 
        host="myHostname1*" OR host="myHostname2*" 
        source="/opt/IBM/taddm/dist/log/services/ProcessFlowManager.log" OR
        source="/opt/IBM/taddm/dist/log/services/ClientProxy.log"
        "started with profile"
        myProfileName
    | rex field=_raw "Discovery\srun,\s(?<RUNID>[^\s]+)\sstarted\swith\sprofile\s(?<profile>[^\s\r]+)" 
    | stats count by profile RUNID 
    | fields profile RUNID]
    | rename profile AS "Profiel"

[ITWhisperer · Accepted solution]

Remove the type=left from the join - this is what is keeping the events from the left side of the join when there is no corresponding event from the right side of the join.