TI Feed Abuse CH Malicious Hash

Query

//This query checks DeviceFileEvents and DeviceProcessEvents against known malicious hashes from AbuseCH feed
//Provides additional context including malware signature, mime type, and VirusTotal percentage
let timeframe = 24h;
let abuse_feed = externaldata(input: string)
    [@'https://bazaar.abuse.ch/export/csv/recent/']
    with (format='txt', ignoreFirstRecord=True)
    | where input !startswith '#';
let hash_regex = @'(\b[a-fA-F0-9]{32,128}\b)';
let filetype_hash_regex = @'"[^n]\w*\/.*?"';
let datetime_regex = @'\d{4}-\d{2}-\d{2}\s.{8}';
let extracted_abuse_feed = abuse_feed
    | extend hashes = extractall(hash_regex, input) 
    | extend
        first_seen_utc = extract(datetime_regex, 0, input),
        signature = trim_end(@',\s"n\/a"', extract(@'"[^0-9]\w*\.{0,1}\w*",\s"n\/a"', 0, input)),
        mime_type = extract(filetype_hash_regex, 0, input),
        file_type_guess = extract(@'"\w{2,4}"', 0, input),
        vt_percent = extract(@'\d{1,2}\.\d{1,2}', 0, input),
        sha256_hash = hashes[0],
        md5_hash = hashes[1],
        sha1_hash = hashes[2],
        imp_hash = hashes[3]
    | extend imp_hash = iff(array_length(hashes) > 4, hashes[4], imp_hash);
union DeviceFileEvents, DeviceProcessEvents
| where TimeGenerated > ago(timeframe)
| union isfuzzy=true extracted_abuse_feed
| where (isnotempty(MD5) and MD5 == md5_hash)
    or (isnotempty(SHA1) and SHA1 == sha1_hash)
    or (isnotempty(SHA256) and SHA256 == sha256_hash)
    or (isnotempty(InitiatingProcessMD5) and InitiatingProcessMD5 == md5_hash)
    or (isnotempty(InitiatingProcessSHA1) and InitiatingProcessSHA1 == sha1_hash)
    or (isnotempty(InitiatingProcessSHA256) and InitiatingProcessSHA256 == sha256_hash)
| extend match_kind = case(
                          MD5 == md5_hash,
                          'MD5',
                          SHA1 == sha1_hash,
                          'SHA1',
                          SHA256 == sha256_hash,
                          'SHA256',
                          InitiatingProcessMD5 == md5_hash,
                          'InitProcessMD5',
                          InitiatingProcessSHA1 == sha1_hash,
                          'InitProcessSHA1',
                          InitiatingProcessSHA256 == sha256_hash,
                          'InitProcessSHA256', ''
                      )
| extend match = case(
                     match_kind == 'MD5',
                     MD5,
                     match_kind == 'SHA1',
                     SHA1,
                     match_kind == 'SHA256',
                     SHA256,
                     match_kind == 'InitProcessMD5',
                     InitiatingProcessMD5,
                     match_kind == 'InitProcessSHA1',
                     InitiatingProcessSHA1,
                     match_kind == 'InitProcessSHA256',
                     InitiatingProcessSHA256, ''
                 )
| project-reorder
    TimeGenerated,
    DeviceName,
    match,
    match_kind,
    first_seen_utc,
    file_type_guess,
    mime_type,
    signature,
    vt_percent,
    ProcessCommandLine,
    FileName,
    FolderPath,
    InitiatingProcessCommandLine,
    InitiatingProcessFileName,
    InitiatingProcessFolderPath,
    InitiatingProcessParentFileName,
    InitiatingProcessAccountName

Explanation

This query is designed to identify potentially malicious files or processes on devices by comparing their hashes against known malicious hashes from the AbuseCH feed. Here's a simplified breakdown of what the query does:

Timeframe Definition: The query looks at events from the last 24 hours.
AbuseCH Feed Import: It imports data from the AbuseCH feed, which contains information about recent malicious files.
Data Extraction: From the feed, it extracts:
- Hashes (MD5, SHA1, SHA256, and IMP hash)
- The first seen date of the malware
- Malware signature
- MIME type
- A guess of the file type
- VirusTotal detection percentage
Event Union: It combines data from DeviceFileEvents and DeviceProcessEvents with the extracted data from the AbuseCH feed.
Hash Matching: It checks if any of the hashes from the device events match those from the AbuseCH feed. It considers both file and initiating process hashes.
Match Identification: If a match is found, it identifies the type of hash that matched (e.g., MD5, SHA1, SHA256).
Result Projection: The query organizes the results to show relevant details such as:
- Time of event
- Device name
- Matched hash and its type
- Additional context like the first seen date, file type, MIME type, malware signature, and VirusTotal detection percentage
- Command lines and file paths related to the process or file

This query helps in identifying and providing context for potentially malicious activities on devices by leveraging known threat intelligence.