✄𐘗
text code block:
✄𐘗from logs-fortinet_fortigate.log-*, logs-network_traffic.dns-*, logs-suricata.eve-*, logs-zeek.dns-*, packetbeat-*
| where (
      data_stream.dataset in ("fortinet_fortigate.log", "network_traffic.dns", "suricata.eve", "zeek.dns")
      or event.dataset == "dns"
    )
    and dns.question.name is not null
    and dns.question.registered_domain is not null
| eval
    Esql.client_ip = source.ip,
    Esql.dataset = COALESCE(data_stream.dataset, event.dataset),
    Esql.dns_question_name = REPLACE(TO_LOWER(dns.question.name), """\.$""", ""),
    Esql.dns_registered_domain = REPLACE(TO_LOWER(dns.question.registered_domain), """\.$""", "")
| where Esql.client_ip is not null
| eval Esql.rmm_vendor = CASE(
    Esql.dns_registered_domain == "action1.com", "Action1",
    Esql.dns_registered_domain == "anydesk.com", "AnyDesk",
    Esql.dns_registered_domain == "atera.com", "Atera",
    Esql.dns_registered_domain in ("beyondtrustcloud.com", "bomgar.com", "bomgarcloud.com"), "BeyondTrust",
    Esql.dns_registered_domain in ("connectwise.com", "screenconnect.com"), "ConnectWise",
    Esql.dns_registered_domain in ("centrastage.net", "datto.com", "datto.net"), "Datto",
    Esql.dns_registered_domain == "dwservice.net", "DWService",
    Esql.dns_registered_domain in (
        "getgo.com", "gotoassist.at", "gotoassist.com", "gotoassist.me", "gotoresolve.com",
        "logmein-gateway.com", "logmein.com", "logmeininc.com", "logmeinrescue.com", "logmeinrescue.eu"
    ), "GoTo/LogMeIn",
    Esql.dns_registered_domain == "gotohttp.com", "GotoHTTP",
    Esql.dns_registered_domain in ("kaseya.com", "kaseya.net"), "Kaseya",
    Esql.dns_registered_domain == "level.io", "Level",
    Esql.dns_registered_domain == "meshcentral.com", "MeshCentral",
    Esql.dns_registered_domain in ("netsupportmanager.com", "netsupportsoftware.com"), "NetSupport",
    Esql.dns_registered_domain in ("ninjaone.com", "ninjarmm.com", "ninjarmm.net"), "NinjaOne",
    Esql.dns_registered_domain == "pulseway.com", "Pulseway",
    Esql.dns_registered_domain == "remotepc.com", "RemotePC",
    Esql.dns_registered_domain == "remoteutilities.com", "RemoteUtilities",
    Esql.dns_registered_domain == "rustdesk.com", "RustDesk",
    Esql.dns_registered_domain == "simple-help.com", "SimpleHelp",
    Esql.dns_registered_domain in ("splashtop.com", "splashtop.eu"), "Splashtop",
    Esql.dns_registered_domain in (
        "superops.ai", "superops.com", "superopsalpha.com", "superopsbeta.com"
    ), "SuperOps",
    Esql.dns_registered_domain in (
        "aurelius.host", "kabuto.io", "servably.com", "syncroapi.com", "syncromsp.com"
    ), "Syncro",
    Esql.dns_registered_domain == "tacticalrmm.com", "TacticalRMM",
    Esql.dns_registered_domain == "teamviewer.com", "TeamViewer",
    Esql.dns_registered_domain == "tightvnc.com", "TightVNC",
    Esql.dns_registered_domain == "ultravnc.com", "UltraVNC",
    Esql.dns_registered_domain == "vnc.com", "RealVNC",
    (
        Esql.dns_question_name == "assist.zoho.com"
        or Esql.dns_registered_domain in ("zohoassist.com", "zohoassist.jp")
    ), "ZohoAssist",
    NULL
)
| where Esql.rmm_vendor is not null
| stats
    Esql.event_count = COUNT(*),
    Esql.rmm_vendor_count = COUNT_DISTINCT(Esql.rmm_vendor),
    Esql.rmm_vendor_values = VALUES(Esql.rmm_vendor),
    Esql.dns_question_name_values = MV_SLICE(VALUES(Esql.dns_question_name), 0, 20),
    Esql.dns_registered_domain_values = VALUES(Esql.dns_registered_domain),
    Esql.dataset_values = VALUES(Esql.dataset),
    Esql.observer_name_values = VALUES(observer.name),
    Esql.first_seen = MIN(@timestamp),
    Esql.last_seen = MAX(@timestamp)
  by Esql.client_ip
| where Esql.rmm_vendor_count >= 2
| keep
    Esql.client_ip,
    Esql.event_count,
    Esql.rmm_vendor_count,
    Esql.rmm_vendor_values,
    Esql.dns_question_name_values,
    Esql.dns_registered_domain_values,
    Esql.dataset_values,
    Esql.observer_name_values,
    Esql.first_seen,
    Esql.last_seen