Using KQL in Microsoft Defender to Query files on user computers

Question

Using KQL in Microsoft Defender to Query files on user computers

APTOS 221

Hello, can anyone help me with querying all computers (Windows 10 and 11) in our organization to find the location of files with a specific extension *.ref using KQL in Advanced Hunting? Is it possible to base this query on the Organizational Unit (OU) of the computers in Active Directory? Regards.

JamesTran-MSFT 36,906 Reputation points Microsoft Employee Moderator

2024-02-29T21:43:14.8066667+00:00

@APTOS
I just wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.

1 answer

Your answer

JamesTran-MSFT 36,906 Reputation points Microsoft Employee Moderator

2024-02-29T21:43:14.8066667+00:00

@APTOS
I just wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.

Answer 1

Givary-MSFT 35,621 Microsoft Employee Moderator

@APTOS Thank you for reaching out to us, As I understand you are looking for KQL query to find location of files with a specific extension *.ref using advance hunting.

As far i am aware, Microsoft Defender for Endpoint (MDE) collects events based on curated decisions, typically comprising signals deemed valuable by threat researchers.

You can use the below KQL query -

let MyDevices =
    (
        DeviceInfo | where OnboardingStatus == "Onboarded" and OSPlatform in ("Windows10","Windows10") | distinct DeviceId
    );
DeviceFileEvents
| join MyDevices on DeviceId
| where Timestamp >= ago(7d)
| where FileName endswith ".csv"
| project Timestamp, DeviceName, FileName, FolderPath

Not sure, if MDE collect insights for "ref" extension, but you can give it a try using the above query.

We can't define OU path/as we don't have that info within MDE, you can set a device tag based on GPO. for example: GPO applied to "OU-A" ------> tag Devices-A

Let me know if you have any further questions, feel free to post back.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.

APTOS 221 Reputation points

2024-02-20T10:32:32.5933333+00:00

Hello ,

Thanks.it's very helpful

i have added filesize to project but i get value in KB.
it's possible to have value in MB for example or Gb ?

Regards
APTOS 221 Reputation points

2024-02-20T10:55:04.9366667+00:00

@Givary-MSFT
Thanks a lot. It's helpful. I need to know if the data collected from MDE is in real-time. If not, how many hours/days does the collection take from the devices? Regards
Givary-MSFT 35,621 Reputation points Microsoft Employee Moderator

2024-02-20T11:42:23.2966667+00:00

@APTOS As far I know data collected from MDE is in real-time - https://learn.microsoft.com/en-us/microsoft-365/security/office-365-security/real-time-detections?view=o365-worldwide Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.
APTOS 221 Reputation points

2024-02-21T15:10:23.47+00:00

Hello @Givary-MSFT the query is functional. Is it possible for you to assist me in obtaining more information? For each device, I need to identify the most recent logon user to determine the computer owner. Additionally, I would like to sort the system tags. The system tags of a device are stored in the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows Advanced Threat Protection\DeviceTagging

it's role give you an information about your computer location

Thanks in advance
Andrew Blumhardt 10,051 Reputation points Microsoft Employee

2024-02-23T14:56:39.4533333+00:00
You can run a file extension query in the main menu search at the top. Just seach .ref without the wildcard. You can see logged on users on each device overview page.

Registry collection is less dependable in the device logs. The following query only returns the user SID.

let logons = DeviceLogonEvents | where ActionType == "LogonSuccess" | summarize LatestLogon=max(Timestamp) by DeviceName, AccountName; logons | join DeviceFileEvents on DeviceName | where FileName endswith ".exe"

I recommend using ChatGPT or Microsoft Copilot to further refine.
Givary-MSFT 35,621 Reputation points Microsoft Employee Moderator

2024-03-01T08:10:59.6833333+00:00

@APTOS Just checking in to see if above information was helpful. If you have any further updates on this issue, please feel free to post back.

Share via

Using KQL in Microsoft Defender to Query files on user computers

1 answer

Your answer