from openhunt.mordorutils import *
spark = get_spark()

"""
Explanation: SMB Create Remote File
Metadata
|     Metadata      |  Value  |
|:------------------|:---|
| collaborators     | ['@Cyb3rWard0g', '@Cyb3rPandaH'] |
| creation date     | 2020/10/12 |
| modification date | 2020/10/12 |
| playbook related  | [] |
Hypothesis
Adversaries might be creating a file remotely via the Server Message Block (SMB) Protocol.
Technical Context
Client systems use the Common Internet File System (CIFS) Protocol to request file and print services from server systems over a network. CIFS is a stateful protocol, in which clients establish a session with a server and use that session to make a variety of requests to access files, printers, and inter-process communication (IPC) mechanisms, such as named pipes.
The extended CIFS Protocol is known as the Server Message Block (SMB). The SMB2 CREATE Request packet is sent by a client to request either creation of or access to a file. In case of a named pipe or printer, the server MUST create a new file.
Offensive Tradecraft
Adversaries leverage SMB to copy files over the network to either execute code remotely or exfiltrate data.
Security Datasets
| Metadata  |    Value  |
|:----------|:----------|
| docs      | https://securitydatasets.com/notebooks/atomic/windows/lateral_movement/SDWIN-200806015757.html        |
| link      | https://raw.githubusercontent.com/OTRF/Security-Datasets/master/datasets/atomic/windows/lateral_movement/host/covenant_copy_smb_CreateRequest.zip  |
Analytics
Initialize Analytics Engine
End of explanation
"""


sd_file = "https://raw.githubusercontent.com/OTRF/Security-Datasets/master/datasets/atomic/windows/lateral_movement/host/covenant_copy_smb_CreateRequest.zip"
registerMordorSQLTable(spark, sd_file, "sdTable")

"""
Explanation: Download & Process Security Dataset
End of explanation
"""


df = spark.sql(
'''
SELECT `@timestamp`, Hostname, ShareName, SubjectUserName, SubjectLogonId,  AccessMask
FROM sdTable
WHERE LOWER(Channel) = 'security'
    AND (EventID = 5140)
    AND NOT ShareName LIKE '%IPC$'
    AND NOT SubjectUserName LIKE '%$'
'''
)
df.show(10,False)

"""
Explanation: Analytic I
Look for non-system accounts SMB connecting (Tree Connect) to a file share that is not IPC$.
| Data source | Event Provider | Relationship | Event |
|:------------|:---------------|--------------|-------|
| File | Microsoft-Windows-Security-Auditing | User accessed file share | 5140 |
End of explanation
"""


df = spark.sql(
'''
SELECT `@timestamp`, Hostname, ShareName, SubjectUserName, b.SubjectLogonId, IpAddress, IpPort
FROM sdTable b
INNER JOIN (
    SELECT SubjectLogonId
    FROM sdTable
    WHERE LOWER(Channel) = "security"
        AND EventID = 5140
        AND ShareName LIKE '%IPC$'
        AND NOT SubjectUserName LIKE '%$'
    ) a
ON b.SubjectLogonId = a.SubjectLogonId
WHERE LOWER(b.Channel) = 'security'
    AND b.EventID = 5140
    AND b.ShareName LIKE '%C$'
    AND NOT SubjectUserName LIKE '%$'
'''
)
df.show(10,False)

"""
Explanation: Analytic II
Look for non-system accounts SMB connecting (Tree Connect) to an IPC$ Share and administrative shares (i.e C$) with the same logon session ID.
| Data source | Event Provider | Relationship | Event |
|:------------|:---------------|--------------|-------|
| File | Microsoft-Windows-Security-Auditing | User accessed file share | 5140 |
End of explanation
"""


df = spark.sql(
'''
SELECT `@timestamp`, Hostname, ShareName, SubjectUserName, SubjectLogonId, IpAddress, IpPort, RelativeTargetName
FROM sdTable
WHERE LOWER(Channel) = "security"
    AND EventID = 5145
    AND ShareName LIKE '%C$'
    AND NOT SubjectUserName LIKE '%$'
    AND AccessMask = '0x2'
'''
)
df.show(10,False)

"""
Explanation: Analytic III
Look for non-system accounts SMB accessing a file with write (0x2) access mask via administrative share (i.e C$).
| Data source | Event Provider | Relationship | Event |
|:------------|:---------------|--------------|-------|
| File | Microsoft-Windows-Security-Auditing | User accessed File | 5145 |
End of explanation
"""


df = spark.sql(
'''
SELECT `@timestamp`, Hostname, ShareName, SubjectUserName, d.SubjectLogonId, IpAddress, IpPort, RelativeTargetName
FROM sdTable d
INNER JOIN (
    SELECT b.SubjectLogonId
    FROM sdTable b
    INNER JOIN (
        SELECT SubjectLogonId
        FROM sdTable
        WHERE LOWER(Channel) = "security"
            AND EventID = 5140
            AND ShareName LIKE '%IPC$'
            AND NOT SubjectUserName LIKE '%$'
    ) a
    ON b.SubjectLogonId = a.SubjectLogonId
    WHERE LOWER(b.Channel) = 'security'
        AND b.EventID = 5140
        AND b.ShareName LIKE '%C$'
) c
ON d.SubjectLogonId = c.SubjectLogonId
WHERE LOWER(d.Channel) = 'security'
    AND d.EventID = 5145
    AND d.ShareName LIKE '%C$'
    AND d.AccessMask = '0x2'
'''
)
df.show(10,False)

"""
Explanation: Analytic IV
Look for non-system accounts SMB connecting (Tree Connect) to an IPC$ Share and administrative shares (i.e C$) and accessing/creating a file with write (0x2) access mask with the same logon session ID.
| Data source | Event Provider | Relationship | Event |
|:------------|:---------------|--------------|-------|
| File | Microsoft-Windows-Security-Auditing | User accessed file share | 5140 |
| File | Microsoft-Windows-Security-Auditing | User accessed File | 5145 |
End of explanation
"""


df = spark.sql(
'''
SELECT `@timestamp`, Hostname, ShareName, SubjectUserName, SubjectLogonId, IpAddress, IpPort, RelativeTargetName
FROM sdTable b
INNER JOIN (
    SELECT LOWER(REVERSE(SPLIT(TargetFilename, '\'))[0]) as TargetFilename
    FROM sdTable
    WHERE Channel = 'Microsoft-Windows-Sysmon/Operational'
        AND Image = 'System'
        AND EventID = 11
) a
ON LOWER(REVERSE(SPLIT(RelativeTargetName, '\'))[0]) = a.TargetFilename
WHERE LOWER(b.Channel) = 'security'
    AND b.EventID = 5145
    AND b.AccessMask = '0x2'
'''
)
df.show(10,False)

"""
Explanation: Analytic V
Look for files that were accessed over the network with write (0x2) access mask via administrative shares (i.e C$) and that were created by the System process on the target system.
| Data source | Event Provider | Relationship | Event |
|:------------|:---------------|--------------|-------|
| File | Microsoft-Windows-Security-Auditing | User accessed File | 5145 |
| File | Microsoft-Windows-Sysmon/Operational | Process created File | 11 |
End of explanation
"""


