[BUG] Detections with joins failed to properly translate to Sigma

[ajkingio]

Describe the bug

As far as I can tell every detection with a join failed to translated properly to your dev/endpoint/ sigma collection.
According to this search there are 60 rules using joins.

repo:splunk/security_content path:detections/endpoint join

Here is one example:
SPL: https://github.com/splunk/security_content/blob/f6882b10686ba9ba0d5e58ab2a2d3add636c57f7/detections/endpoint/batch_file_write_to_system32.yml

data_source:
- Sysmon Event ID 1
search: '| tstats `security_content_summariesonly` count FROM datamodel=Endpoint.Processes where 
Processes.process_name=* by _time span=1h Processes.process_guid Processes.process_name Processes.dest Processes.user 
| `drop_dm_object_name(Processes)` | join process_guid 
    [| tstats `security_content_summariesonly` count min(_time) as firstTime max(_time) as lastTime FROM datamodel=Endpoint.Filesystem
    where Filesystem.file_path IN ("*\\system32\\*", "*\\syswow64\\*") Filesystem.file_name="*.bat" by _time span=1h Filesystem.dest Filesystem.file_create_time Filesystem.file_name Filesystem.file_path Filesystem.process_guid 
    | `drop_dm_object_name(Filesystem)`]
| table dest user file_create_time, file_name, file_path, process_name, firstTime, lastTime 
| dedup file_create_time
| `security_content_ctime(firstTime)` 
| `security_content_ctime(lastTime)`
| `batch_file_write_to_system32_filter`'

Sigma: https://github.com/splunk/security_content/blob/f6882b10686ba9ba0d5e58ab2a2d3add636c57f7/dev/endpoint/batch_file_write_to_system32.yml

data_source:
- Sysmon Event ID 1
search:
  selection1:
    Image|endswith: '*'
  condition: selection1

Expected behavior

For the example above I would create the following Sigma.

data_source:
- Sysmon Event ID 11
search:
    selection1:
        TargetFilename|contains: .bat
    selection2:
        TargetFilename|contains:
            - system32
            - syswow64
     condition: selection1 AND selection2

I'm not sure if this would be an acceptable change since it's not going off the Endpoint Data Model like the original rule. If this is acceptable let me know and I would be happy to work on a PR to get these Sigma rules updated.

[patel-bhavin]

Hello @ajkingio : We currently DO NOT actively support/ maintain sigma search conversion for ESCU detection. If you see an error with the same associated in the detections/ directory, please open up an issue / PR with requested changes.

We will consider removing this dev/ directory completely since our detections work better when directly written into SPL which you can find in the detections/ . Thanks for the PR!