Question on Log Analytic Workspace KQL query

Shinde, Balaji 116 Reputation points
2022-08-30T14:00:28.947+00:00

Hi All,

I am looking to find the list of computers which are not onboarded to MDE(Microsoft Defender for Endpoint) using below KQL query.

ConfigurationData
| where RegistryKey == "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Advanced Threat Protection\Status"
| where ValueName == "OnboardingState" and ValueData != 1
| distinct Computer, ValueName, ValueData
| project Computer, ValueName, ValueData

I do get the computer names which has DWord OnboardingState and its value is not 1. as shown below.
236146-image.png
But there are computers which does not have the DWord "OnboardingState" at all. I need to find those computers as well, tried many different approaches but unable to get those. Any help here please?

Azure Monitor
Azure Monitor
An Azure service that is used to collect, analyze, and act on telemetry data from Azure and on-premises environments.
2,799 questions
Azure Automation
Azure Automation
An Azure service that is used to automate, configure, and install updates across hybrid environments.
1,114 questions
0 comments No comments
{count} votes

Accepted answer
  1. Maxim Sergeev 6,566 Reputation points Microsoft Employee
    2022-08-30T21:16:20.127+00:00

    Hi, @Shinde, Balaji

    There are multiple options, one of them is using "union" operator

    ConfigurationData  
    | where RegistryKey == "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows Advanced Threat Protection\\Status"   
    | where ValueName == "OnboardingState" and ValueData != 1  
    | distinct Computer, ValueName, ValueData  
    | union withsource=ConfigurationData kind=outer   
       (ConfigurationData | where RegistryKey != "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows Advanced Threat Protection\\Status" | distinct Computer )  
    | project Computer, ValueName, ValueData  
    

0 additional answers

Sort by: Most helpful