topic How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype? in Splunk Search

How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype?

Splunk2095 — Thu, 11 May 2023 11:09:16 GMT

Hi All,

I ran into a tricky one and can’t wrap my head around it (or if it is even possible). The use case is as follows:

There are 3 sourcetypes that share a common field “Detection_id”. This field has a unique value that appears in all 3 sourcetypes.

The first sourcetype is “DetectionName” and the 2nd sourcetype is “ProcessInfo”. I did a join based on the “Detection_id” field so that way I can see my detection names displayed in a table right next to the process information responsible for the detection from the 2nd sourcetype. That search works fine.

This is where it gets a bit tricky. The 3rd sourcetype is called “FalsePositive” meaning it was a detection that was already investigated and considered false positive. We do not want any events displaying in our table from the first two searches IF the same unique “Detection_id” value also appears in the “FalsePositive” sourcetype. That way time isn’t wasted scrolling through detections that were already investigated.

Any thoughts on the best way to handle this and/or if it is even possible?

Appreciate any answers or feedback.

Re: How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype

ITWhisperer — Thu, 11 May 2023 10:56:19 GMT

Firstly, try to avoid using join if possible as it is slow and has limitations

Secondly, you could combine your searches into one and then determine which detection_ids are not represented in the false positive sourcetype

sourcetype="DetectionName" OR sourcetype="ProcessInfo" OR sourcetype="FalsePositive" | eventstats values(sourcetype) as sourcetype by Detection_id | where sourcetype!="FalsePositive" | stats values(*) as * by Detection_id

Re: How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype

Splunk2095 — Thu, 11 May 2023 11:30:55 GMT

Thanks for the quick response! and yes I hate join... making Splunk do 2 searches every time it runs breaks my heart.

Will your example also work if for instance, I want my search to be based on sourcetype="DetectionName" AND detect_name="Bypass UAC" ? So putting that first one in brackets?

Essentially I will be having a scheduled search for each detection name so events can be grouped and analyzed based on the attack tactic.

Re: How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype

ITWhisperer — Thu, 11 May 2023 11:38:28 GMT

Yes, this should work

(sourcetype="DetectionName" AND detect_name="Bypass UAC") OR sourcetype="ProcessInfo" OR sourcetype="FalsePositive"

You also might want to change the where so that it is look for both valid sourcetypes as well as not having the FalsePositive sourcetype - by not having this extra check you are effectively doing a union i.e. events could come from either or both of the desired sourcetypes.

Re: How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype

Splunk2095 — Thu, 11 May 2023 12:57:36 GMT

Hmm so the search does run, but it is never producing results. I tested multiple variations, but this is the jist:

index=main (sourcetype="DetectionName" AND detect_name="Bypass UAC") OR sourcetype="ProcessInfo" OR sourcetype="FalsePositive"
| eventstats values(sourcetype) as sourcetype by Detection_id
| where sourcetype!="FalsePositive" AND sourcetype="DetectionName" AND sourcetype="ProcessInfo"
| stats values(ComputerName) as ComputerName values(detect_description) as detect_description values(detect_name) as detect_name values(detect_scenario) as detect_scenario values(Process) as Process values(CommandLine) as CommandLine by Detection_id

When I take away the where clause it runs but of course produces a long list of stuff where the majority are missing key field values (detect_description, detect_name, detect_scenario are all unique fields in the DetectionName sourcetype while Process and CommandLine are unique fields in the ProcessInfo sourcetype).

Re: How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype

ITWhisperer — Thu, 11 May 2023 13:59:06 GMT

Here is a runanywhere example of this technique working

| makeresults count=20 | fields - _time | eval sourcetype=mvindex(split("DetectionName,ProcessInfo,FalsePositive",","),random()%3) | eval Detection_id=random()%10 | eval detect_name=if(sourcetype="DetectionName","Bypass UAC",null()) | eval Process=if(sourcetype="ProcessInfo",mvindex(split("xyz",""),random()%3).mvindex(split("xyz",""),random()%3).mvindex(split("xyz",""),random()%3),null()) | eventstats values(sourcetype) as sourcetype by Detection_id | where sourcetype!="FalsePositive" AND sourcetype="DetectionName" AND sourcetype="ProcessInfo" | stats values(detect_name) as detect_name values(Process) as Process values(sourcetype) as sourcetype by Detection_id

Re: How to exclude results from joined subsearches if the field value appears in a 3rd sourcetype

Splunk2095 — Thu, 11 May 2023 14:58:36 GMT

I got it working!!!!

After specifying the original sourcetypes in the beginning, I did a pipe to fields and specified all fields that were going to be included in some form or another from each of the sourcetypes.

Then I tested by having a wildcard for the detection name and having |where sourcetype="FalsePositive". I saw 4 results and wrote down the detect_id's. Then I ran the same search but did |where sourcetype!="FalsePositive" and it displayed even more events, but none with the detect_id that showed up when including "FalsePositive".

Brain is fried hope that makes sense lol. Either way, thank you so much! I wouldn't have reached this point without your help. Also, the search runs so much faster than using join ofc :).