KQL Search

Query Details

HomeAI ToolsDevice Query

TI Malicious Connection To Firehol Ipset List

Query

//This query checks network connections against potentially malicious IPs from Firehol ipset list
//Includes connection details, CIDR country and port information
let timeframe = 24h;
let threat_intel_feed = externaldata(DestIP: string)
    [@'https://raw.githubusercontent.com/firehol/blocklist-ipsets/master/firehol_level3.netset']
    with (format='txt', ignoreFirstRecord=true);
let cidr_ranges = materialize(externaldata(CIDRCountry: string, CIDR: string, CIDRCountryName: string)
    ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip'] with (type=csv, ignoreFirstRecord=true));
let port_info = (externaldata (Protocol: string, Port: int, Description: string)
    ['https://raw.githubusercontent.com/maraisr/ports-list/main/all.csv']
    with (type=csv, ignoreFirstRecord=true));
let ip_regex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let malicious_ips = materialize(
    threat_intel_feed
    | where DestIP matches regex ip_regex
    | distinct DestIP
    );
DeviceNetworkEvents
| where TimeGenerated > ago(timeframe)
| where ActionType in~('ConnectionSuccess', 'ConnectionRequest', 'InboundConnectionAccepted')
| where RemoteIP in(malicious_ips)
| summarize arg_max(TimeGenerated, *) by RemoteIP, DeviceName
| extend Country = tostring(geo_info_from_ip_address(RemoteIP).country)
| join kind=leftouter (cidr_ranges
    | distinct CIDRCountry, CIDRCountryName)
    on $left.Country == $right.CIDRCountryName
| extend RemoteUrl = iff(isempty(RemoteUrl), 'Not Applicable', RemoteUrl)
| where CIDRCountry !contains 'RFC' and not(ipv4_is_private(RemoteIP))
| join kind=leftouter (port_info
    | summarize Protocol = make_set(Protocol), Port_Desc = make_set(Description) by Port)
    on $left.RemotePort == $right.Port
| extend Port_Desc = iff(
                         isempty(Port_Desc)
    and RemotePort <= 49151,
                         pack_array('Unknown IANA Registered Port'),
                         Port_Desc
                     )
| extend Port_Desc = iff(
                         isempty(Port_Desc)
    and (RemotePort >= 49152 and RemotePort <= 65535),
                         pack_array('Unregistered Ephemeral/Private Port'),
                         Port_Desc
                     )
| project
    TimeGenerated,
    DeviceName,
    RemoteIP,
    CIDRCountry,
    RemoteUrl,
    RemotePort,
    ActionType,
    Port_Desc,
    InitiatingProcessAccountName,
    InitiatingProcessAccountUpn,
    InitiatingProcessFileName,
    InitiatingProcessId,
    InitiatingProcessCommandLine,
    InitiatingProcessMD5,
    InitiatingProcessFolderPath,
    LocalIP,
    LocalPort,
    Type
| sort by TimeGenerated desc

Explanation

This query is designed to identify and provide details about network connections from your devices to potentially malicious IP addresses. Here's a simplified breakdown of what it does:

  1. Timeframe: It looks at network events from the past 24 hours.

  2. Threat Intelligence Feed: It uses a list of potentially malicious IP addresses from the Firehol ipset list.

  3. CIDR and Country Information: It retrieves country and CIDR (Classless Inter-Domain Routing) information from an external CSV file.

  4. Port Information: It gathers details about network ports, including their protocols and descriptions, from another external CSV file.

  5. IP Filtering: It filters out valid IP addresses from the threat intelligence feed using a regular expression.

  6. Network Events: It examines network events on devices, specifically looking for successful or attempted connections to the malicious IPs.

  7. Data Enrichment:

    • It adds country information based on the IP address.
    • It matches the country with CIDR data to provide more context.
    • It includes port descriptions, identifying unknown or unregistered ports when necessary.

  8. Data Presentation: The query presents the following details for each connection:

    • Time of the event
    • Device name
    • Remote IP address
    • Country of the IP
    • Remote URL (if applicable)
    • Remote port and its description
    • Type of connection action
    • Information about the initiating process (e.g., account name, file name, command line, etc.)
    • Local IP and port

  9. Sorting: The results are sorted by the time the event was generated, in descending order, so the most recent events appear first.

Overall, this query helps in monitoring and analyzing network connections to potentially harmful IPs, providing insights into the nature of these connections and the associated risks.

Details

Benjamin Zulliger profile picture

Benjamin Zulliger

Released: November 10, 2024

Tables

DeviceNetworkEvents

Keywords

DeviceNetworkEventsCIDRCountryPortInformation

Operators

letexternaldatawithmaterializematchesregexdistinctagoin~summarizearg_maxextendtostringgeo_info_from_ip_addressjoinkindoniffisemptynotipv4_is_privatemake_setpack_arrayprojectsort by

Actions