Question on Log Analytic Workspace KQL query

Question

Question on Log Analytic Workspace KQL query

Shinde, Balaji 116

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.

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?

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Maxim Sergeev 6,586 Microsoft Employee

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

Shinde, Balaji 116 Reputation points

2022-08-31T01:48:40.69+00:00

Hi @Maxim Sergeev

Thanks for your response. Ideally your query should work since the some computers has Status key empty, i.e. they do not have any DWords in it. But somehow the query is returning me all the computers reporting to workspace.

Maxim Sergeev 6,586 Microsoft Employee

Actually, we could change the query by applying another filter

 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" |  where isnull(ValueName ) or isnull(ValueData) | distinct Computer )  
 | project Computer, ValueName, ValueData

Shinde, Balaji 116 Reputation points

2022-08-31T10:27:04.917+00:00

@Maxim Sergeev thanks for your help so far. There was a small typo in the query at line 6. it needed to be == instead of = . But the query is not working, its not giving me any computer names. I also tried running the second query separately as below, but that won't give any computer names as well.

ConfigurationData | where RegistryKey == "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Advanced Threat Protection\Status" | where ValueName == "OnboardingState" | where isnull(ValueName) | distinct Computer
| project Computer
Maxim Sergeev 6,586 Reputation points Microsoft Employee

2022-08-31T17:54:05.617+00:00
@Shinde, Balaji ,

Sorry, I don't have environment like yours, I wrote the queries without testing :)
Can you exclude where ValueName == "OnboardingState" ? You mentioned that it doesn't exist
Run this one

ConfigurationData | where RegistryKey = "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows Advanced Threat Protection\\Status" | where isnull(ValueName ) | distinct Computer

If this shows nothing, it means Change Tracking solution doesn't find any VMs with the registry key. If the registry doesn't exist in the VMs, so you have to figure out how to discover the VMs in another way (service name, folder path or other options)
Maxim Sergeev 6,586 Reputation points Microsoft Employee

2022-09-04T02:45:49.687+00:00

Please don't hesitate to mark the post as answered if it is. This helps us and our community

Share via

Question on Log Analytic Workspace KQL query

0 additional answers

Your answer