How to exclude IPs from results while using Tstats and a sourcetype that is not the same?

[aminfosec]

Current search is essentially this:

| tstats values(All_Traffic.src) as src
    from datamodel=Network_Traffic.All_Traffic
    by All_Traffic.dest
| lookup mythreatlist IOC as dest OUTPUTNEW list
| list=*
| search NOT whitelistedSources
| search NOT whitelistedDestinations

The tstats model uses a sourcetype the returns logs that do not have the URL in them, only destination IP. This cannot change. A second sourcetype, stURL, does have the URLs. I am looking for a way to use a subsearch/join so that I can exclude all source IPs where the URL is splunkdotcom, even if the IP for splunkdotcom is on my threat list.

I have tried to create the subsearch

[search index=A sourcetype=stURL url="*splunkdotcom*" | fields src]

to obtain all source IPs that visited splunk.com and then exclude them from my tstats search but it does not appear to be working as intended.

[woodcock]

Try this:

|tstats values(All_Traffic.src) as src 
FROM datamodel=Network_Traffic.All_Traffic
WHERE NOT [search index=A sourcetype=stURL url="*splunkdotcom*" | fields src | rename src AS "All_Traffic.src"]
BY All_Traffic.dest
...

[jkat54]

Your subsearch is very close to being what you want.

You'll need to add

| format 

or

| return src

To your subsearch depending on how you want the subsearch to work. In this case | return seems the most appropriate

See the docs for format and return as needed.

[jkat54]

[woodcock]

Right, but it does that anyway, you just don't get to see it.

[woodcock]

I don't think you said what you meant to say. Adding | format will change absolutely nothing about the effect of the subsearch other than readability.