During the last few years, I worked with a couple of customers who struggle with getting control over their corporate networks. Even though we all know corporate networks should be managed well with all the correct security controls like segmentation, Network Access Control implementations, and zero-trust designs, a lot of organizations keep struggling with knowing what devices are present on legacy network segments.
There are a couple of great network scanning and documentation solutions out there that can help organizations in getting insights into their networks. Although these dedicated network scanning solutions have many more features than what we are going to discuss in this post, I hope to inspire people you do not always need an expensive fancy network scanning solution to know what exists on your network. In this post I want to show you how we can get better network insights with just a couple of MDE onboarded devices with Device Discovery enabled, a sentinel workbook, and a list of your network segments described in a watchlist. Using this, I am confident you will have better insights into your network compared to what Device Discovery provides out of the box.
Device Discovery is a feature in Defender for Endpoint that helps you discover unmanaged devices on your corporate networks without the need for extra appliances. This feature uses your onboarded devices to probe and scan your corporate networks and search for enterprise endpoints, network appliances, and IoT devices.
There are two modes in which device discovery can run, being basic and standard discovery. In essence, the basic discovery mode will passively collect events in the network without initiating network traffic, while the standard mode will initiate traffic to actively find devices on a network. To have the best results during device discovery, the standard mode is recommended.
Since it is not the purpose of this blog post to rewrite the Microsoft learning pages, I like to refer to Microsoft learn which you can follow on how to set up device discovery in MDE. I mainly want to focus on how we can improve the visibility of Device Discovery, so it can be more useful.
It is important to set expectations right first. Device Discovery is not a vulnerability scanning feature for remote devices (unless you configure authenticated scans, but that is for another post ?). It purely analyzes traffic on the network to find devices on the same subnet as the onboarded devices and provide more context. This means that if you want to have insights into a network, you need at least one MDE onboarded device on the subnet in question. Device discovery analyzes the following protocols to gather information on the neighbor network devices: ARP, FTP, HTTP, HTTPS, ICMP, LLMNR, NBNS, RDP, SIP, SMTP, SNMP, SSH, Telnet, UPNP, WSD, SMB, NBSS, IPP, PJL, RPC, mDNS, DHCP, AFP, CrestonCIP, IphoneSync, WinRM, VNC, SLP, LDAP
How frequently does device discovery run? Well, this is not something you can configure yourself. According to the Microsoft learn pages: "Devices will actively be probed when changes in device characteristics are observed to make sure the existing information is up to date (typically, devices probed no more than once in a three-week period)". This means you will have to trust on Device Discovery to be able to detect changes in device characteristics to probe a device. I would rather like an option to forcefully schedule an extra probe on top of the automatic detection, to be more sure a scheduled 'full probe' is executed frequently.
The biggest problem I have with the 'Devices' view in Defender XDR, is the limited ability to filter on certain properties, and the missing context of the subnets devices are connected to. To address these two main issues, I started creating a workbook in Microsoft Sentinel to create my own visualization of the device discovery data, mainly focused on the discovered unmanaged devices of the network. The main dependency for this workbook to work is that you will need to ingest Defender XDR DeviceInfo and DeviceNetworkInfo table to Microsoft Sentinel.
All the other properties can serve for extra context to enrich the data in the workbook even further.
There are 8 usable parameters in the workbook which can be used to filter the data set:
In the above screenshot, you can see the overview graphs I added to the workbook. The goals of these is to give you a better idea of the onboarding state of your environment, but also to give an idea where the unmanaged devices live and what kind of devices these are.
In the above screenshots, you can see what the detailed view looks like. This view can be used to get more specific information regarding the unmanaged devices found by the filters you configured. It is important to note that one unmanaged device can be found multiple times in this view. This is because we aggregate the first and last time a device was seen, based on all the properties you can find in the view. This means that if -for example- a device has two NIC's resulting in two IP and MAC addresses bound to the device, you will find the device two times in this view.
You will notice that the DeviceName, DeviceId, and IP address is a clickable link. When you click on the DeviceName or DeviceId you will be redirected to the Device page in Defender XDR:
When you click on the IP address, you will be redirected to the IP address page in Defender XDR:
I did not add the workbook to the Sentinel GitHub repository yet, since I think the workbook still needs some extra features before in is ready to go to the Content Hub of Sentinel. Either way, if you already want to test out the workbook you can find it here:
{
"version": "Notebook/1.0",
"items": [
{
"type": 1,
"content": {
"json": "# Device Discovery\n-----\n"
},
"name": "text - 2"
},
{
"type": 11,
"content": {
"version": "LinkItem/1.0",
"style": "tabs",
"links": [
{
"id": "c1950c2c-12f7-4c71-adbb-40e070b8d86b",
"cellValue": "view",
"linkTarget": "parameter",
"linkLabel": "Overview",
"subTarget": "overview",
"style": "link"
}
]
},
"name": "links - 9"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": "## Unmanaged devices\r\nInformation regarding all the unmanaged devices found via Device Discovery"
},
"name": "text - 2"
},
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "65a5d150-0bcb-45df-9fab-629ac143918f",
"version": "KqlParameterItem/1.0",
"name": "time_range",
"label": "First seen",
"type": 4,
"isRequired": true,
"typeSettings": {
"selectableValues": [
{
"durationMs": 3600000
},
{
"durationMs": 14400000
},
{
"durationMs": 43200000
},
{
"durationMs": 86400000
},
{
"durationMs": 172800000
},
{
"durationMs": 259200000
},
{
"durationMs": 604800000
},
{
"durationMs": 1209600000
},
{
"durationMs": 2419200000
}
],
"allowCustom": false
},
"timeContext": {
"durationMs": 2592000000
},
"value": {
"durationMs": 86400000
}
},
{
"id": "17f6a873-ae48-4901-9334-ebb20b8bf08c",
"version": "KqlParameterItem/1.0",
"name": "include_known_vendors",
"label": "Include known vendors",
"type": 2,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "DeviceInfo\r\n// Set empty Vendor names to 'Unknown'\r\n| extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\r\n| distinct Vendor\r\n| sort by Vendor asc",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"showDefault": false
},
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "time_range",
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"value": [
"value::all"
]
},
{
"id": "a57d292f-c21e-4196-a44d-6407a9c9a0eb",
"version": "KqlParameterItem/1.0",
"name": "flag_unassigned_group",
"label": "Only flag unassigned group",
"type": 2,
"isRequired": true,
"typeSettings": {
"additionalResourceOptions": []
},
"jsonData": "[\"True\", \"False\"]\r\n",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "time_range",
"value": "False"
},
{
"id": "3bf2e7e4-f730-42e2-b8c3-5adc117dc1ff",
"version": "KqlParameterItem/1.0",
"name": "include_join_type",
"label": "Include join type",
"type": 2,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "DeviceInfo\r\n| where JoinType != \"\"\r\n| distinct JoinType",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"showDefault": false
},
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "time_range",
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
{
"id": "d6b6c2d9-1626-4c7e-820f-ee1799ff0945",
"version": "KqlParameterItem/1.0",
"name": "exclude_excluded_devices",
"label": "Exclude excluded devices",
"type": 2,
"isRequired": true,
"typeSettings": {
"additionalResourceOptions": []
},
"jsonData": "[\"True\", \"False\"]",
"value": "False"
},
{
"id": "fb9a3384-c75d-46fa-a47a-907483b19184",
"version": "KqlParameterItem/1.0",
"name": "include_device_category",
"label": "Include device category",
"type": 2,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "DeviceInfo\r\n| distinct DeviceCategory",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
]
},
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "time_range",
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"value": [
"value::all"
]
},
{
"id": "aa0885e8-4a0f-4027-b347-04d84421c50b",
"version": "KqlParameterItem/1.0",
"name": "include_networks",
"label": "Include Networks",
"type": 2,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "let historic_devices = DeviceInfo\r\n | where TimeGenerated between ({time_range:start} .. ago(1d))\r\n | distinct DeviceId;\r\nDeviceInfo\r\n| where TimeGenerated > ago(1d)\r\n| join kind=leftanti historic_devices on DeviceId\r\n// Get more network info of the device\r\n| join DeviceNetworkInfo on DeviceId, ReportId\r\n// Expand network names\r\n| mv-expand ConnectedNetworks\r\n// Set empty network names to 'Unkown'\r\n| extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\r\n| distinct ConnectedNetworkName",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"value": [
"value::all"
]
},
{
"id": "e37850ae-1134-48cf-be40-f9b0e4152580",
"version": "KqlParameterItem/1.0",
"name": "network_address",
"label": "Networks (watchlist)",
"type": 2,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "_GetWatchlist(\"networks\")\r\n| extend Network = strcat(NetworkAddress, \"/\", Prefix)\r\n| distinct Network, Name\r\n| sort by Name asc",
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"showDefault": false
},
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "time_range",
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"value": [
]
},
{
"id": "f9bdce47-08c5-4b21-8653-8acbc4c8b543",
"version": "KqlParameterItem/1.0",
"name": "hidden_tenant_lookup",
"type": 1,
"query": "{\"version\":\"ARMEndpoint/1.0\",\"data\":null,\"headers\":[],\"method\":\"GET\",\"path\":\"/tenants?api-version=2022-12-01\",\"urlParams\":[],\"batchDisabled\":false,\"transformers\":[{\"type\":\"jsonpath\",\"settings\":{\"tablePath\":\"$..tenantId\",\"columns\":[]}}]}",
"isHiddenWhenLocked": true,
"typeSettings": {
"multiLineText": true,
"editorLanguage": "json"
},
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "time_range",
"queryType": 12
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "parameters - 5"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "DeviceInfo\r\n| where TimeGenerated > {time_range:start}\r\n// Get more network info of the device\r\n| join DeviceNetworkInfo on DeviceId, ReportId\r\n// Remove unneeded data\r\n| project-away DeviceId1, Timestamp1, ReportId1, DeviceName1\r\n// Remove expected managed devices connecting after a long period\r\n| where JoinType in ({include_join_type}) or JoinType == \"\"\r\n// Exlude known vendor devices on the network\r\n| extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\r\n| where Vendor in ({include_known_vendors})\r\n// Remove excluded devices\r\n| where IsExcluded != 1 or {exclude_excluded_devices} == False\r\n// Only flag unassigned group devices\r\n| where MachineGroup == \"UnassignedGroup\" or {flag_unassigned_group} == false\r\n// Exlude certain device categories\r\n| where DeviceCategory in ({include_device_category})\r\n// Expand IP Addresses and networks\r\n| mv-expand IPAddresses\r\n| extend IPAddress = IPAddresses.IPAddress, SubnetPrefix = IPAddresses.SubnetPrefix, AddressType = IPAddresses.AddressType\r\n| mv-expand ConnectedNetworks\r\n// Set empty network names to 'Unkown'\r\n| extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\r\n// Only include filtered networks\r\n| where ConnectedNetworkName in ({include_networks})\r\n// Only include IP addresses found in network ranges from wathlist, or skip check when parameter is not set\r\n| where ipv4_is_in_any_range(tostring(IPAddress), dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n| distinct DeviceId, OnboardingStatus\r\n| summarize count() by OnboardingStatus",
"size": 0,
"title": "Onboarding State",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "piechart",
"chartSettings": {
"seriesLabelSettings": [
{
"seriesName": "Onboarded",
"color": "green"
},
{
"seriesName": "Unsupported",
"color": "orange"
},
{
"seriesName": "Can be onboarded",
"color": "red"
},
{
"seriesName": "Insufficient info",
"color": "gray"
}
],
"ySettings": {
"numberFormatSettings": {
"unit": 0,
"options": {
"style": "decimal",
"useGrouping": true
}
}
}
}
},
"customWidth": "50",
"name": "onboarding_state",
"styleSettings": {
"maxWidth": "50"
}
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Get all networks from watchlist\r\nlet networks = _GetWatchlist(\"networks\")\r\n | extend NetworkPrefix = strcat(NetworkAddress, \"/\", Prefix)\r\n | where NetworkPrefix in (dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n | extend joiner = 1;\r\nlet historic_devices = DeviceInfo\r\n | where TimeGenerated between (ago(30d) .. {time_range:start})\r\n | where OnboardingStatus == \"Onboarded\"\r\n | distinct DeviceId;\r\nDeviceInfo\r\n | where TimeGenerated > {time_range:start}\r\n | join kind=leftanti historic_devices on DeviceId\r\n // Get more network info of the device\r\n | join DeviceNetworkInfo on DeviceId, ReportId\r\n // Remove unneeded data\r\n | project-away DeviceId1, Timestamp1, ReportId1, DeviceName1\r\n // Rmeove expected managed devices connecting after a long period\r\n | where JoinType in ({include_join_type}) or JoinType == \"\"\r\n | where OnboardingStatus != \"Onboarded\"\r\n // Exlude known vendor devices on the network\r\n | extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\r\n | where Vendor in ({include_known_vendors})\r\n // Remove excluded devices\r\n | where IsExcluded != 1 or {exclude_excluded_devices} == False\r\n // Only flag unassigned group devices\r\n | where MachineGroup == \"UnassignedGroup\" or {flag_unassigned_group} == false\r\n // Exlude certain device categories\r\n | where DeviceCategory in ({include_device_category})\r\n // Expand IP Addresses and networks\r\n | mv-expand IPAddresses\r\n | extend IPAddress = tostring(IPAddresses.IPAddress), SubnetPrefix = tostring(IPAddresses.SubnetPrefix), AddressType = tostring(IPAddresses.AddressType)\r\n | mv-expand ConnectedNetworks\r\n // Set empty network names to 'Unkown'\r\n | extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\r\n // Only include filtered networks\r\n | where ConnectedNetworkName in ({include_networks})\r\n // Only include IP addresses found in network ranges from wathlist, or skip check when parameter is not set\r\n | where ipv4_is_in_any_range(IPAddress, dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n // Join networks\r\n | extend joiner = 1\r\n | join kind=inner networks on joiner\r\n // Check if IP in netwoork address\r\n | where ipv4_is_in_range(IPAddress, NetworkPrefix)\r\n // Group TimeGenerated by DeviceId, IPAddress, and MACAddress\r\n | summarize FirstSeen = min(TimeGenerated), LastSteen = max(TimeGenerated) by DeviceName, ConnectedNetworkName, DeviceCategory, IPAddress, Gateway, NetworkPrefix, Segment, SegmentType, VlanID, Location, MacAddress, Name, MachineGroup, Vendor, OSBuild, OSPlatform, OSVersion, OSVersionInfo, DeviceSubtype, Model, OnboardingStatus, IsExcluded, AddressType, DeviceId\r\n | distinct Name, DeviceId\r\n | summarize count() by Name, DeviceId\r\n",
"size": 0,
"title": "Unmanaged devices by network",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "piechart"
},
"customWidth": "50",
"name": "query - 7",
"styleSettings": {
"maxWidth": "50"
}
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Get all networks from watchlist\r\nlet networks = _GetWatchlist(\"networks\")\r\n | extend NetworkPrefix = strcat(NetworkAddress, \"/\", Prefix)\r\n | where NetworkPrefix in (dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n | extend joiner = 1;\r\nlet historic_devices = DeviceInfo\r\n | where TimeGenerated between (ago(30d) .. {time_range:start})\r\n | where OnboardingStatus == \"Onboarded\"\r\n | distinct DeviceId;\r\nDeviceInfo\r\n| where TimeGenerated > {time_range:start}\r\n| join kind=leftanti historic_devices on DeviceId\r\n// Get more network info of the device\r\n| join DeviceNetworkInfo on DeviceId, ReportId\r\n// Remove unneeded data\r\n| project-away DeviceId1, Timestamp1, ReportId1, DeviceName1\r\n// Rmeove expected managed devices connecting after a long period\r\n| where JoinType in ({include_join_type}) or JoinType == \"\"\r\n| where OnboardingStatus != \"Onboarded\"\r\n// Exlude known vendor devices on the network\r\n| extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\r\n| where Vendor in ({include_known_vendors})\r\n// Remove excluded devices\r\n| where IsExcluded != 1 or {exclude_excluded_devices} == False\r\n// Only flag unassigned group devices\r\n| where MachineGroup == \"UnassignedGroup\" or {flag_unassigned_group} == false\r\n// Exlude certain device categories\r\n| where DeviceCategory in ({include_device_category})\r\n// Expand IP Addresses and networks\r\n| mv-expand IPAddresses\r\n| extend IPAddress = tostring(IPAddresses.IPAddress), SubnetPrefix = tostring(IPAddresses.SubnetPrefix), AddressType = tostring(IPAddresses.AddressType)\r\n| mv-expand ConnectedNetworks\r\n// Set empty network names to 'Unkown'\r\n| extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\r\n// Only include filtered networks\r\n| where ConnectedNetworkName in ({include_networks})\r\n// Only include IP addresses found in network ranges from wathlist, or skip check when parameter is not set\r\n| where ipv4_is_in_any_range(tostring(IPAddress), dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n// Join networks\r\n| extend joiner = 1\r\n| join kind=inner networks on joiner\r\n// Check if IP in netwoork address\r\n| where ipv4_is_in_range(IPAddress, NetworkPrefix)\r\n// Group TimeGenerated by DeviceId, IPAddress, and MACAddress\r\n| summarize FirstSeen = min(TimeGenerated), LastSteen = max(TimeGenerated) by DeviceName, ConnectedNetworkName, DeviceCategory, IPAddress, Gateway, NetworkPrefix, Segment, SegmentType, VlanID, Location, MacAddress, Name, MachineGroup, Vendor, OSBuild, OSPlatform, OSVersion, OSVersionInfo, DeviceSubtype, Model, OnboardingStatus, IsExcluded, AddressType, DeviceId\r\n| distinct DeviceId, Vendor\r\n| summarize count() by Vendor",
"size": 1,
"title": "Found unmanaged devices by vendor",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "tiles",
"tileSettings": {
"titleContent": {
"columnMatch": "Vendor",
"formatter": 1
},
"leftContent": {
"columnMatch": "count_",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"style": "decimal",
"maximumFractionDigits": 2,
"maximumSignificantDigits": 3
}
}
},
"showBorder": true,
"sortCriteriaField": "count_",
"sortOrderField": 2,
"size": "auto"
},
"mapSettings": {
"locInfo": "LatLong",
"sizeSettings": "count_",
"sizeAggregation": "Sum",
"legendMetric": "count_",
"legendAggregation": "Sum",
"itemColorSettings": {
"type": "heatmap",
"colorAggregation": "Sum",
"nodeColorField": "count_",
"heatmapPalette": "greenRed"
}
}
},
"customWidth": "33",
"name": "query - 6"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Get all networks from watchlist\r\nlet networks = _GetWatchlist(\"networks\")\r\n | extend NetworkPrefix = strcat(NetworkAddress, \"/\", Prefix)\r\n | where NetworkPrefix in (dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n | extend joiner = 1;\r\nlet historic_devices = DeviceInfo\r\n | where TimeGenerated between (ago(30d) .. {time_range:start})\r\n | where OnboardingStatus == \"Onboarded\"\r\n | distinct DeviceId;\r\nDeviceInfo\r\n| where TimeGenerated > {time_range:start}\r\n| join kind=leftanti historic_devices on DeviceId\r\n// Get more network info of the device\r\n| join DeviceNetworkInfo on DeviceId, ReportId\r\n// Remove unneeded data\r\n| project-away DeviceId1, Timestamp1, ReportId1, DeviceName1\r\n// Rmeove expected managed devices connecting after a long period\r\n| where JoinType in ({include_join_type}) or JoinType == \"\"\r\n| where OnboardingStatus != \"Onboarded\"\r\n// Exlude known vendor devices on the network\r\n| extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\r\n| where Vendor in ({include_known_vendors})\r\n// Remove excluded devices\r\n| where IsExcluded != 1 or {exclude_excluded_devices} == False\r\n// Only flag unassigned group devices\r\n| where MachineGroup == \"UnassignedGroup\" or {flag_unassigned_group} == false\r\n// Exlude certain device categories\r\n| where DeviceCategory in ({include_device_category})\r\n// Expand IP Addresses and networks\r\n| mv-expand IPAddresses\r\n| extend IPAddress = tostring(IPAddresses.IPAddress), SubnetPrefix = tostring(IPAddresses.SubnetPrefix), AddressType = tostring(IPAddresses.AddressType)\r\n| mv-expand ConnectedNetworks\r\n// Set empty network names to 'Unkown'\r\n| extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\r\n// Only include filtered networks\r\n| where ConnectedNetworkName in ({include_networks})\r\n// Only include IP addresses found in network ranges from wathlist, or skip check when parameter is not set\r\n| where ipv4_is_in_any_range(tostring(IPAddress), dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n// Join networks\r\n| extend joiner = 1\r\n| join kind=inner networks on joiner\r\n// Check if IP in netwoork address\r\n| where ipv4_is_in_range(IPAddress, NetworkPrefix)\r\n// Group TimeGenerated by DeviceId, IPAddress, and MACAddress\r\n| summarize FirstSeen = min(TimeGenerated), LastSteen = max(TimeGenerated) by DeviceName, ConnectedNetworkName, DeviceCategory, IPAddress, Gateway, NetworkPrefix, Segment, SegmentType, VlanID, Location, MacAddress, Name, MachineGroup, Vendor, OSBuild, OSPlatform, OSVersion, OSVersionInfo, DeviceSubtype, Model, OnboardingStatus, IsExcluded, AddressType, DeviceId\r\n// Make output better\r\n| distinct DeviceId, ConnectedNetworkName\r\n| summarize count() by ConnectedNetworkName",
"size": 1,
"title": "Found unmanaged devices by connected network",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "tiles",
"tileSettings": {
"titleContent": {
"columnMatch": "ConnectedNetworkName",
"formatter": 1
},
"leftContent": {
"columnMatch": "count_",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
},
"showBorder": true,
"sortCriteriaField": "count_",
"sortOrderField": 2,
"size": "auto"
}
},
"customWidth": "33",
"name": "query - 7"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Get all networks from watchlist\r\nlet networks = _GetWatchlist(\"networks\")\r\n | extend NetworkPrefix = strcat(NetworkAddress, \"/\", Prefix)\r\n | where NetworkPrefix in (dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n | extend joiner = 1;\r\nlet historic_devices = DeviceInfo\r\n | where TimeGenerated between (ago(30d) .. {time_range:start})\r\n | where OnboardingStatus == \"Onboarded\"\r\n | distinct DeviceId;\r\nDeviceInfo\r\n| where TimeGenerated > {time_range:start}\r\n| join kind=leftanti historic_devices on DeviceId\r\n// Get more network info of the device\r\n| join DeviceNetworkInfo on DeviceId, ReportId\r\n// Remove unneeded data\r\n| project-away DeviceId1, Timestamp1, ReportId1, DeviceName1\r\n// Rmeove expected managed devices connecting after a long period\r\n| where JoinType in ({include_join_type}) or JoinType == \"\"\r\n| where OnboardingStatus != \"Onboarded\"\r\n// Exlude known vendor devices on the network\r\n| extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\r\n| where Vendor in ({include_known_vendors})\r\n// Remove excluded devices\r\n| where IsExcluded != 1 or {exclude_excluded_devices} == False\r\n// Only flag unassigned group devices\r\n| where MachineGroup == \"UnassignedGroup\" or {flag_unassigned_group} == false\r\n// Exlude certain device categories\r\n| where DeviceCategory in ({include_device_category})\r\n// Expand IP Addresses and networks\r\n| mv-expand IPAddresses\r\n| extend IPAddress = tostring(IPAddresses.IPAddress), SubnetPrefix = tostring(IPAddresses.SubnetPrefix), AddressType = tostring(IPAddresses.AddressType)\r\n| mv-expand ConnectedNetworks\r\n// Set empty network names to 'Unkown'\r\n| extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\r\n// Only include filtered networks\r\n| where ConnectedNetworkName in ({include_networks})\r\n// Only include IP addresses found in network ranges from wathlist, or skip check when parameter is not set\r\n| where ipv4_is_in_any_range(tostring(IPAddress), dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\r\n// Make output better\r\n| distinct DeviceId, DeviceCategory\r\n| summarize count() by DeviceCategory\r\n| render piechart",
"size": 1,
"title": "Found unmanaged devices by device category",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"visualization": "tiles",
"tileSettings": {
"titleContent": {
"columnMatch": "DeviceCategory",
"formatter": 1
},
"leftContent": {
"columnMatch": "count_",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
},
"showBorder": true,
"sortCriteriaField": "count_",
"sortOrderField": 2,
"size": "auto"
}
},
"customWidth": "33",
"name": "query - 8"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Get all networks from watchlist\nlet networks = _GetWatchlist(\"networks\")\n | extend NetworkPrefix = strcat(NetworkAddress, \"/\", Prefix)\n | where NetworkPrefix in (dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\n | extend joiner = 1;\nlet historic_devices = DeviceInfo\n | where TimeGenerated between (ago(30d) .. {time_range:start})\n | where OnboardingStatus == \"Onboarded\"\n | distinct DeviceId;\nDeviceInfo\n | where TimeGenerated > {time_range:start}\n | join kind=leftanti historic_devices on DeviceId\n // Get more network info of the device\n | join DeviceNetworkInfo on DeviceId, ReportId\n // Remove unneeded data\n | project-away DeviceId1, Timestamp1, ReportId1, DeviceName1\n // Rmeove expected managed devices connecting after a long period\n | where JoinType in ({include_join_type}) or JoinType == \"\"\n | where OnboardingStatus != \"Onboarded\"\n // Exlude known vendor devices on the network\n | extend Vendor = iff(Vendor == \"\", \"Unknown\", Vendor)\n | where Vendor in ({include_known_vendors})\n // Remove excluded devices\n | where IsExcluded != 1 or {exclude_excluded_devices} == False\n // Only flag unassigned group devices\n | where MachineGroup == \"UnassignedGroup\" or {flag_unassigned_group} == false\n // Exlude certain device categories\n | where DeviceCategory in ({include_device_category})\n // Expand IP Addresses and networks\n | mv-expand IPAddresses\n | extend IPAddress = tostring(IPAddresses.IPAddress), SubnetPrefix = tostring(IPAddresses.SubnetPrefix), AddressType = tostring(IPAddresses.AddressType)\n | mv-expand ConnectedNetworks\n // Set empty network names to 'Unkown'\n | extend ConnectedNetworkName = iff(ConnectedNetworks.Name == \"\", \"Unknown\", tostring(ConnectedNetworks.Name))\n // Only include filtered networks\n | where ConnectedNetworkName in ({include_networks})\n // Only include IP addresses found in network ranges from wathlist, or skip check when parameter is not set\n | where ipv4_is_in_any_range(IPAddress, dynamic([{network_address}])) or array_length(dynamic([{network_address}])) == 0\n // Join networks\n | extend joiner = 1\n | join kind=inner networks on joiner\n // Check if IP in netwoork address\n | where ipv4_is_in_range(IPAddress, NetworkPrefix)\n // Group TimeGenerated by DeviceId, IPAddress, and MACAddress\n | summarize FirstSeen = min(TimeGenerated), LastSteen = max(TimeGenerated) by DeviceName, ConnectedNetworkName, DeviceCategory, IPAddress, Gateway, NetworkPrefix, Segment, SegmentType, VlanID, Location, MacAddress, Name, MachineGroup, Vendor, OSBuild, OSPlatform, OSVersion, OSVersionInfo, DeviceSubtype, Model, OnboardingStatus, IsExcluded, AddressType, DeviceId\n | extend DeviceUrl = strcat(\"https://security.microsoft.com/machines/\", DeviceId, \"?tid=\", '{hidden_tenant_lookup:value}')\n | extend IPUrl = strcat(\"https://security.microsoft.com/ip/\", IPAddress, \"/overview?tid=\", '{hidden_tenant_lookup:value}')\n | sort by FirstSeen desc\n",
"size": 3,
"title": "Unmanaged devices",
"showExportToExcel": true,
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"gridSettings": {
"formatters": [
{
"columnMatch": "DeviceName",
"formatter": 1,
"formatOptions": {
"linkColumn": "DeviceUrl",
"linkTarget": "Url"
}
},
{
"columnMatch": "IPAddress",
"formatter": 1,
"formatOptions": {
"linkColumn": "IPUrl",
"linkTarget": "Url"
}
},
{
"columnMatch": "DeviceId",
"formatter": 1,
"formatOptions": {
"linkColumn": "DeviceUrl",
"linkTarget": "Url"
}
},
{
"columnMatch": "DeviceUrl",
"formatter": 5,
"formatOptions": {
"linkColumn": "DeviceName",
"linkTarget": "Url",
"linkLabel": "",
"linkIsContextBlade": false
}
},
{
"columnMatch": "IPUrl",
"formatter": 5
}
],
"rowLimit": 500,
"filter": true
},
"graphSettings": {
"type": 0,
"topContent": {
"columnMatch": "DeviceName",
"formatter": 1
},
"centerContent": {
"columnMatch": "OSBuild",
"formatter": 1,
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
}
},
"name": "query - 2"
}
]
},
"conditionalVisibility": {
"parameterName": "view",
"comparison": "isEqualTo",
"value": "overview"
},
"name": "group-overview"
}
],
"fallbackResourceIds": [
],
"fromTemplateId": "sentinel-UserWorkbook",
"$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}
Show on Github
Make sure to let me know what you think of it, and what you are still missing ?.
