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.
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
...
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.
Right, but it does that anyway, you just don't get to see it.
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.