{"id":9398,"date":"2025-12-03T22:44:23","date_gmt":"2025-12-03T22:44:23","guid":{"rendered":"https:\/\/pariswells.com\/blog\/?p=9398"},"modified":"2025-12-03T23:23:45","modified_gmt":"2025-12-03T23:23:45","slug":"advanced-hunting-to-find-all-devices-not-onboarded-and-discovered-devices-to-defender","status":"publish","type":"post","link":"https:\/\/pariswells.com\/blog\/research\/advanced-hunting-to-find-all-devices-not-onboarded-and-discovered-devices-to-defender","title":{"rendered":"Advanced Hunting to Find all Devices not onboarded and discovered Devices to Defender"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code class=\"\">\/\/ Non-onboarded\/discovered devices + who saw them + network details + IPs\nDeviceInfo\n| where Timestamp > ago(30d)\n| where OnboardingStatus != \"Onboarded\"               \/\/ all not onboarded\/discovered\n| where isempty(MergedToDeviceId)                    \/\/ Remove invalidated\/merged devices\n| summarize arg_max(Timestamp, *) by DeviceId\n| invoke SeenBy()                                    \/\/ Add detecting onboarded devices\n| join kind=leftouter (\n    DeviceNetworkInfo\n    | where Timestamp > ago(30d)\n    | summarize arg_max(Timestamp, *) by DeviceId    \/\/ Latest network info\n) on DeviceId\n| extend ConnectedNetworksParsed = parse_json(ConnectedNetworks)\n| extend IPAddressesParsed = parse_json(IPAddresses)\n| summarize \n    IPsSeen              = make_set(IPAddressesParsed.Address),\n    DetectingDevices     = make_set(SeenBy),         \/\/ Onboarded devices that saw it\n    NetworkNames         = make_set(ConnectedNetworksParsed.Name),      \/\/ e.g., WiFi SSID or network name\n    NetworkCategories    = make_set(ConnectedNetworksParsed.Category),  \/\/ e.g., Enterprise, Public\n    LastSeen             = max(Timestamp),\n    FirstSeen            = min(Timestamp)\n    by NonOnboardedName = DeviceName, OSPlatform, DeviceId\n| project-reorder NonOnboardedName, IPsSeen, DetectingDevices, NetworkNames, NetworkCategories, OSPlatform, LastSeen, FirstSeen\n| order by LastSeen desc<\/code><\/pre>\n\n\n\n<p>Cleaner Version<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">\/\/ Non-onboarded\/discovered devices + who saw them + network details + IPs (excluding Unknown OS, iOS, Android, Linux; remove with no detecting devices)\nDeviceInfo\n| where Timestamp > ago(30d)\n| where OnboardingStatus != \"Onboarded\"               \/\/ all not onboarded\/discovered\n| where isempty(MergedToDeviceId)                    \/\/ Remove invalidated\/merged devices\n| summarize arg_max(Timestamp, *) by DeviceId\n| where isnotempty(OSPlatform) and OSPlatform != \"Unknown\" and OSPlatform !in (\"iOS\", \"Android\", \"Linux\")\n| invoke SeenBy()                                    \/\/ Add detecting onboarded devices\n| join kind=leftouter (\n    DeviceNetworkInfo\n    | where Timestamp > ago(30d)\n    | summarize arg_max(Timestamp, *) by DeviceId    \/\/ Latest network info\n) on DeviceId\n| extend IPAddresses = iff(isempty(IPAddresses) or isnull(IPAddresses), tostring(pack_array(pack_dictionary(\"IPAddress\", PublicIP, \"PrefixSize\", \"0\", \"AddressSpace\", \"Public\"))), IPAddresses)\n| extend ConnectedNetworks = iff(isempty(ConnectedNetworks) or isnull(ConnectedNetworks), '[]', ConnectedNetworks)\n| mv-apply IPAddressesParsed = parse_json(IPAddresses) on (\n    extend IPAddress = tostring(IPAddressesParsed.IPAddress)\n    | summarize IPsSeen = make_set(IPAddress) by DeviceId, NonOnboardedName = DeviceName, OSPlatform, tostring(SeenBy), ConnectedNetworks, Timestamp\n)\n| mv-apply ConnectedNetworksParsed = parse_json(ConnectedNetworks) on (\n    extend Name = tostring(ConnectedNetworksParsed.Name),\n           Category = tostring(ConnectedNetworksParsed.Category)\n    | summarize NetworkNames = make_set(Name),\n                NetworkCategories = make_set(Category) by DeviceId, NonOnboardedName, OSPlatform, tostring(SeenBy), tostring(IPsSeen), Timestamp\n)\n| summarize \n    IPsSeen = make_set(IPsSeen),\n    DetectingDevices = make_set(SeenBy),         \/\/ Onboarded devices that saw it\n    NetworkNames = make_set(NetworkNames),      \n    NetworkCategories = make_set(NetworkCategories),  \n    LastSeen = max(Timestamp),\n    FirstSeen = min(Timestamp)\n    by NonOnboardedName, OSPlatform, DeviceId\n| where array_length(DetectingDevices) > 0          \/\/ Remove devices with no detecting devices\n| project-reorder NonOnboardedName, IPsSeen, DetectingDevices, NetworkNames, NetworkCategories, OSPlatform, LastSeen, FirstSeen\n| order by LastSeen desc<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Cleaner Version<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-9398","post","type-post","status-publish","format-standard","hentry","category-research"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9398","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/comments?post=9398"}],"version-history":[{"count":2,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9398\/revisions"}],"predecessor-version":[{"id":9401,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/posts\/9398\/revisions\/9401"}],"wp:attachment":[{"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/media?parent=9398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/categories?post=9398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pariswells.com\/blog\/wp-json\/wp\/v2\/tags?post=9398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}