<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Remote Service creation
Metadata
|     Metadata      |  Value  |
|
Step1:  Download & Process Security Dataset
Step2:  Analytic I
Look for new services being created in your environment under a network logon session (3). That is a sign that the service creation was performed from another endpoint in the environment
| Data source | Event Provider | Relationship | Event |
|
<ASSISTANT_TASK:>
Python Code:
from openhunt.mordorutils import *
spark = get_spark()
Explanation: Remote Service creation
Metadata
|     Metadata      |  Value  |
|:------------------|:---|
| collaborators     | ['@Cyb3rWard0g', '@Cyb3rPandaH'] |
| creation date     | 2019/08/15 |
| modification date | 2020/09/20 |
| playbook related  | ['WIN-190813181020'] |
Hypothesis
Adversaries might be creating new services remotely to execute code and move laterally in my environment
Technical Context
None
Offensive Tradecraft
Adversaries may execute a binary, command, or script via a method that interacts with Windows services, such as the Service Control Manager. This can be done by by adversaries creating a new service.
Adversaries can create services remotely to execute code and move lateraly across the environment.
Security Datasets
| Metadata  |    Value  |
|:----------|:----------|
| docs      | https://securitydatasets.com/notebooks/atomic/windows/lateral_movement/SDWIN-190518210652.html        |
| link      | https://raw.githubusercontent.com/OTRF/Security-Datasets/master/datasets/atomic/windows/lateral_movement/host/empire_psexec_dcerpc_tcp_svcctl.zip  |
Analytics
Initialize Analytics Engine
End of explanation
sd_file = "https://raw.githubusercontent.com/OTRF/Security-Datasets/master/datasets/atomic/windows/lateral_movement/host/empire_psexec_dcerpc_tcp_svcctl.zip"
registerMordorSQLTable(spark, sd_file, "sdTable")
Explanation: Download & Process Security Dataset
End of explanation
df = spark.sql(
'''
SELECT o.`@timestamp`, o.Hostname, o.SubjectUserName, o.SubjectUserName, o.ServiceName, a.IpAddress
FROM sdTable o
INNER JOIN (
    SELECT Hostname,TargetUserName,TargetLogonId,IpAddress
    FROM sdTable
    WHERE LOWER(Channel) = "security"
        AND EventID = 4624
        AND LogonType = 3            
        AND NOT TargetUserName LIKE "%$"
    ) a
ON o.SubjectLogonId = a.TargetLogonId
WHERE LOWER(o.Channel) = "security"
    AND o.EventID = 4697
'''
)
df.show(10,False)
Explanation: Analytic I
Look for new services being created in your environment under a network logon session (3). That is a sign that the service creation was performed from another endpoint in the environment
| Data source | Event Provider | Relationship | Event |
|:------------|:---------------|--------------|-------|
| Service | Microsoft-Windows-Security-Auditing | User created Service | 4697 |
| Authentication log | Microsoft-Windows-Security-Auditing | User authenticated Host | 4624 |
End of explanation
<END_TASK>