Contents

Deep Dives

Is Windows Defender Enough to Pass SOC 2?

Osquery Makes the Built-In Antivirus in Windows Audit Ready

Jason Meller
This article is just about Windows devices. Want to know our perspective on third-party AV for macOS? Check out β€œDo Macs Need Third-Party Antivirus for SOC 2 Compliance?.”

Want to skip the preamble? Go right to the osquery SQL at the end of this article.

Third-party malware detection and prevention (what we used to call antivirus over a decade ago) is not every Windows administrator’s cup of tea. Some have bigger fish to fry (e.g., getting endpoint visibility, for starters); for others, they are content with the built-in anti-malware capabilities of Windows and thus have no plans to deploy AV on its merits.

Unfortunately, SOC 2 and other similar audits are forcing both types of Windows IT Admins to purchase and deploy antivirus-like software earlier and earlier in the organization’s lifecycle. When I ask IT Admins who weren’t psyched about deploying AV why they did it anyway, their responses generally fall into two buckets:

  1. They don’t believe Windows has sufficient anti-malware capabilities to pass a SOC2 audit.1
  2. They cannot pass compliance audits like SOC 2 without enterprise reporting features around malware protection.

In this article, we’ll challenge both of these assumptions. Most importantly, I want to show that with open-source tools, you can pass a SOC2 audit with the built-in anti-malware capabilities of Windows (Windows Defender) while also being able to β€œdefend” (no pun intended) that position to senior leadership and auditors. To do that, I hope you’ll indulge me in shoving the third-party AV industry around a bit in the process.

Holistically, Windows Built-In Security Is Better Than Third-Party Antivirus

Ideally, before you face a SOC 2 audit, you genuinely believe you’ve made the best decisions possible regarding the security of your Windows devices with the resources you have available. For example, as a security practitioner, I do actually believe that many organizations are better off relying on the built-in security capabilities of Windows Defender without a third-party supplement. How can that be?

Well, for starters, let’s first acknowledge that the most basic and cursory research around third-party AV portends a horror show of tangible consequences that include: tanking an endpoint’s performance, regularly blocking legitimate software, indiscriminately selling users’ data to undisclosed parties, and even the software itself becoming the source of major compromise.

Okay, but not every vendor is equally afflicted by these problems, so it’s not fair to indict the entire third-party AV industry on just those anecdotes.

So now, let’s talk about what we mean by β€œbetter.” The most myopic and flawed way to ascertain the quality of antivirus software is only to measure how good it is at stopping bad things from happening. These measurements include:

  • How fast can the AV detect novel/new threats?
  • How many real-time executions of bad things did the AV stop?
  • How many novel areas of visibility can it obtain?

It’s no wonder that AV security companies build their entire pitch based on these measurements. Unfortunately, these measurements fail to consider the costs paid (usually by the end-user) for the marginal improvements across these metrics. Or put another way:

The end-user misery generated by third-party AV is typically only addressed when it becomes so egregious that it can be easily linked to a significant adverse financial event. Misery is unbounded for everything that falls short of that bar. Accounting for this, we need to adjust how we accurately measure the AV’s actual performance.

Here is one way. Instead of just looking for the best antivirus performance at any cost, we need antivirus performance per unit of yuck, where yuck is defined as the qualitative degradation of the device’s user experience.

So who is better incentivized to give us maximum AV performance per yuck? In my view, it’s clearly OS vendors (like Microsoft), and here are the reasons why:

  1. OS vendors are financially impacted if users think their OS runs like junk. Third-party AV vendors, on the other hand, are incentivized to portray the OS vendor as incompetent to position themselves as unique experts.

  2. OS vendors rely on a thriving third-party ecosystem of useful and fun software to drive the adoption of the OS itself. That means they must care deeply about how OS security impacts the viability of software. Third-party AV does not have any incentive to care about the viability of other software until their customers notice (and then rectify it by just adding it to an allowlist).

  3. OS vendors can use vertical integration or partnerships with hardware OEMs to develop highly efficient security systems deep in the kernel of the OS itself and rely on the existence of sophisticated security hardware like a TPM. Third-party vendors cannot hook in at this deep level (safely), and they cannot successfully advocate for dedicated hardware within the device that makes their technology better.

Given the above realities, it’s easy to see why Microsoft has invested heavily into Windows’ built-in security capabilities considerably since the Windows XP days of yore.

Windows Defender antivirus

Initially released in 2009 (under the name Microsoft Security Essentials), Windows Defender Antivirus has evolved into a fully-featured and well-regarded antivirus app that is included in all versions of Windows (including 10 & 11).

A graphic depicting the 5 key features of Windows Defender.

Windows Defender offers sufficient protection against malware, ransomware, adware, trojan, and spyware. It can block exploits, prevent network-based attacks, and flag phishing sites. It also has advanced features such as real-time threat protection, cloud-based updates, offline scanning, and limited periodic scanning.

A screenshot depicting several key settings of Windows Defender.

Another component called SmartScreen promotes secure internet browsing on Edge, and Microsoft has extended the protection to other browsers such as Chrome and Firefox.

Microsoft Defender also lists detected threats in security reports, which you can review on the Windows Security app.

A screenshot of the Windows Security app

Additionally, the security software uses machine learning, big-data analytics, threat resistance research, and more to protect endpoints from known viruses and zero-day attacks. The features are on-par with paid antivirus software, with the added benefit of being part of the operating system, so you don’t have to do extra work to install and maintain the application.

Investing in Prevention Has Quickly Diminishing Returns. Instead, Focus on What You Will Do When That Prevention Eventually Fails

AV vendors’ common arguments to justify their products despite Microsoft’s comprehensive built-in security are all about splitting hairs around detection efficacy.

The playbook generally involves the third-party AV vendor pointing to specific malware variants that their product can detect and that Microsoft failed to add to their signature lists promptly (or at all).

In my view, this is a foolish argument to mount. It’s easy to enumerate many successful malware campaigns that no antivirus vendor could detect in a timely manner. Since perfect detection/prevention is impossible, we must consider the trust cost we wish to pay for guaranteed performance degradation, false positives, and other attack surfaces to get marginal improvements. If users are keeping a tight ship, applying updates, and not disabling UAC, that strongly correlates with a very low chance that those differences in protection impact them.

Expanding upon the idea that prevention eventually fails, at some point, it makes sense to find a reasonable baseline of preventative antivirus and shift focus and resources into building a computer incident response plan. That means when (not if) a Windows PC does become compromised, the organization can better react to mitigate the potentially severe impacts of that compromise going unchecked. The prevention game is one with diminishing returns per dollar spent. On the other hand, incident response development is one of the best security investments you can make.

But There’s a Missing Piece: Compiling Data To Meet Audit Requirements

As we saw above, Microsoft is incentivized and does a reasonable job protecting Windows PC users from malware.

That’s great news! But there’s one problem.

You still need to collect data to compile reports for your compliance audit. Microsoft doesn’t offer a way to achieve that level of fleet visibility without purchasing their suite of Endpoint Management and Security tools (essentially the same thing you would be getting with third-party AV).

That’s where osquery comes to the rescue.

You might have heard of using osquery to take device inventory, but did you know it’s also a handy tool for compiling data to meet SOC 2 reporting requirements?

How Osquery Supports SOC 2 Compliance

Osquery is an open-source tool that allows users to query operating systems. For example, IT can use osquery to gain visibility into macOS, Windows, and Linux devices.

You can use osquery to check all the devices in your fleet. This allows you to ensure that they follow platform-specific rules based on your company’s data security policy and compliance standards (e.g., disk encryption, firewall status, OS updates, etc.)

Osquery can accumulate and log compliance data to support the SOC 2 reporting and the auditing process. You can see aggregated metrics or drill down to specifics using various filters to demonstrate that users’ devices are compliant with SOC 2 requirements.

A graphic depicting how osquery works.

Many IT professionals favor osquery because it’s simple, reliable, and extensible. Since it works for all three operating systems, you can collect data on every device in your fleet without using different tools.

How To Use Data Collected By Osquery to Support SOC 2

To pass your SOC 2 audit, you must create documentation to demonstrate that your systems and processes meet specific requirements.

To show that you have the appropriate defense against malware and viruses according to Common Criteria 6.8, you must create a report to describe your processes for file integrity monitoring (FIM) and endpoint security management.

Your documentation should demonstrate that:

  • You can track updates made to software and configuration files and changes in endpoint protection statuses and events.

  • You have implemented controls to prevent, detect, and act upon unauthorized or malicious software introduced into your infrastructure.

  • Only authorized individuals can install applications and software on devices connected to your network.

  • You have processes to detect changes that could indicate the presence of unauthorized or malicious software.

  • There’s a management-defined change control process to monitor the implementation of software and applications.

  • Antivirus and anti-malware software is implemented and maintained to detect and remediate malware.

  • You follow procedures to scan information assets in your custody to detect malware and other unauthorized software.

Below is an example of the documentation we provide customers upon request to help them pass these criteria for their SOC 2 compliance with Kolide and Microsoft Windows’ built-in protection.

An example of the documentation we used to pass this criteria for our own SOC 2 compliance, with Kolide and Windows tools

Putting osquery into action For SOC 2 compliance

Microsoft Windows with Defender can satisfy the technical requirements for SOC 2 certification, and you don’t need to use third-party antivirus. But it’s challenging to compile device data and report at scale. This is where osquery comes in to provide fleet visibility, monitor activities, and collect the data you need to prove fleet compliance for SOC 2 audit and reporting.

Osquery SQL: Windows security center

To establish that the overall malware prevention apparatus of Windows is operational, we need to use the built-in reporting that comes with Windows itself, the Windows Security Center.

Windows Security Center Information Visualized in Kolide

Introduced back in Windows XP SP2, the Windows Security Center APIs give us a complete health report of the state of the critical security features of Windows. Fast-forward almost two decades, and these APIs still give us some high-level insight that we need.

Lucky for us, Kolide contributed a table to osquery that allows us to query this API. It’s called windows_security_center.

SELECT * FROM windows_security_center;
osquery> select * from windows_security_center;
                       firewall = Good
                     autoupdate = Good
                      antivirus = Good
              internet_settings = Good
windows_security_center_service = Good
           user_account_control = Good

While this provides us with a singular health grade for both the antivirus and anti-spyware protection on the Windows device, we can use another osquery table called windows_security_products to get an even deeper look.

SELECT * FROM windows_security_products;
                 type = Firewall
                 name = Windows Firewall
                state = On
      state_timestamp = NULL
     remediation_path = %windir%\system32\firewall.cpl
signatures_up_to_date = 1

                 type = Antivirus
                 name = Microsoft Defender Antivirus
                state = On
      state_timestamp = Sun, 01 May 2022 04:33:50 GMT
     remediation_path = windowsdefender://
signatures_up_to_date = 1

This table tells us which products are currently responsible for both the Antivirus and application layer firewall and if the included signatures are up to date.

Bridging osquery and WMI for more details

As you can see above, osquery can help collect essential details about the state of Windows built-in malware and virus protection. Unfortunately, this isn’t quite enough information. For example, we are missing information about Windows Defender’s configuration, and we have no idea of the results of Defender’s scanning.

To get that information, we need to go beyond the built-in capabilities of osquery. Fortunately, Kolide’s open-source agent extends osquery’s so that it can bridge into Windows Management Instrumentation API (WMI). This is precisely what we need to complete our data gathering story.

Kolide Launcher SQL: Windows Defender configuration

In the WMI API, Microsoft offers the MSFT_MpComputerStatus class, which allows us to grab all the pertinent details about the current state of Windows Defender.

While the WMI query (which also uses SQL) will look something like SELECT * FROM MSFT_MpComputerStatus with Kolide, we need to be a bit more explicit:

SELECT * FROM kolide_wmi
  WHERE class = 'MSFT_MpComputerStatus'
  AND namespace = '\root\Microsoft\Windows\Defender'
  AND properties = 'ComputerID,ComputerState,AMProductVersion,AMServiceVersion,AntispywareSignatureVersion,AntispywareSignatureAge,AntispywareSignatureLastUpdated,AntivirusSignatureVersion,AntivirusSignatureAge,AntivirusSignatureLastUpdated,NISSignatureVersion,NISSignatureAge,NISSignatureLastUpdated,FullScanStartTime,FullScanEndTime,FullScanAge,LastQuickScanSource,LastFullScanSource,RealTimeScanDirection,QuickScanStartTime,QuickScanEndTime,QuickScanAge,AMEngineVersion,AMServiceEnabled,OnAccessProtectionEnabled,IoavProtectionEnabled,BehaviorMonitorEnabled,AntivirusEnabled,AntispywareEnabled,RealTimeProtectionEnabled,NISEngineVersion,NISEnabled'
+────────────────────────────────────+──────────────────────────────────+─────────+────────+───────────────────────────────────────+──────────────+
| fullkey                            | key                              | parent  | query  | value                                 | whereclause  |
+────────────────────────────────────+──────────────────────────────────+─────────+────────+───────────────────────────────────────+──────────────+
| 0/ComputerState                    | ComputerState                    | 0       | *      | 0                                     | ""           |
| 0/AntispywareSignatureVersion      | AntispywareSignatureVersion      | 0       | *      | 1.363.1657.0                          | ""           |
| 0/AntispywareSignatureAge          | AntispywareSignatureAge          | 0       | *      | 0                                     | ""           |
| 0/QuickScanEndTime                 | QuickScanEndTime                 | 0       | *      | 20220507001933.450000+000             | ""           |
| 0/NISEnabled                       | NISEnabled                       | 0       | *      | true                                  | ""           |
| 0/AMServiceVersion                 | AMServiceVersion                 | 0       | *      | 4.18.2203.5                           | ""           |
| 0/AntispywareSignatureLastUpdated  | AntispywareSignatureLastUpdated  | 0       | *      | 20220509023536.000000+000             | ""           |
| 0/AntivirusSignatureVersion        | AntivirusSignatureVersion        | 0       | *      | 1.363.1657.0                          | ""           |
| 0/IoavProtectionEnabled            | IoavProtectionEnabled            | 0       | *      | true                                  | ""           |
| 0/AntivirusSignatureLastUpdated    | AntivirusSignatureLastUpdated    | 0       | *      | 20220509023536.000000+000             | ""           |
| 0/QuickScanAge                     | QuickScanAge                     | 0       | *      | 2                                     | ""           |
| 0/AntispywareEnabled               | AntispywareEnabled               | 0       | *      | true                                  | ""           |
| 0/NISSignatureVersion              | NISSignatureVersion              | 0       | *      | 1.363.1657.0                          | ""           |
| 0/NISSignatureAge                  | NISSignatureAge                  | 0       | *      | 0                                     | ""           |
| 0/FullScanAge                      | FullScanAge                      | 0       | *      | '-1                                   | ""           |
| 0/NISEngineVersion                 | NISEngineVersion                 | 0       | *      | 1.1.19200.5                           | ""           |
| 0/RealTimeScanDirection            | RealTimeScanDirection            | 0       | *      | 0                                     | ""           |
| 0/AMServiceEnabled                 | AMServiceEnabled                 | 0       | *      | true                                  | ""           |
| 0/ComputerID                       | ComputerID                       | 0       | *      | 9802EC57-A4BB-4137-BB73-51516631CDF9  | ""           |
| 0/AMProductVersion                 | AMProductVersion                 | 0       | *      | 4.18.2203.5                           | ""           |
| 0/BehaviorMonitorEnabled           | BehaviorMonitorEnabled           | 0       | *      | true                                  | ""           |
| 0/RealTimeProtectionEnabled        | RealTimeProtectionEnabled        | 0       | *      | true                                  | ""           |
| 0/AntivirusSignatureAge            | AntivirusSignatureAge            | 0       | *      | 0                                     | ""           |
| 0/QuickScanStartTime               | QuickScanStartTime               | 0       | *      | 20220507001822.844000+000             | ""           |
| 0/AMEngineVersion                  | AMEngineVersion                  | 0       | *      | 1.1.19200.5                           | ""           |
| 0/NISSignatureLastUpdated          | NISSignatureLastUpdated          | 0       | *      | 20220509023536.000000+000             | ""           |
| 0/LastQuickScanSource              | LastQuickScanSource              | 0       | *      | 2                                     | ""           |
| 0/LastFullScanSource               | LastFullScanSource               | 0       | *      | 0                                     | ""           |
| 0/OnAccessProtectionEnabled        | OnAccessProtectionEnabled        | 0       | *      | true                                  | ""           |
| 0/AntivirusEnabled                 | AntivirusEnabled                 | 0       | *      | true                                  | ""           |
+────────────────────────────────────+──────────────────────────────────+─────────+────────+───────────────────────────────────────+──────────────+

While this is the data we want, it’s not quite in a format that is easy to read. Using EAV transform techniques we learned from another blog post , we can rewrite the query to get a single row containing each property.

The Final SQL:

WITH wmi_raw AS (
  SELECT * FROM kolide_wmi
  WHERE class = 'MSFT_MpComputerStatus'
  AND namespace = '\root\Microsoft\Windows\Defender'
  AND properties = 'ComputerID,ComputerState,AMProductVersion,AMServiceVersion,AntispywareSignatureVersion,AntispywareSignatureAge,AntispywareSignatureLastUpdated,AntivirusSignatureVersion,AntivirusSignatureAge,AntivirusSignatureLastUpdated,NISSignatureVersion,NISSignatureAge,NISSignatureLastUpdated,FullScanStartTime,FullScanEndTime,FullScanAge,LastQuickScanSource,LastFullScanSource,RealTimeScanDirection,QuickScanStartTime,QuickScanEndTime,QuickScanAge,AMEngineVersion,AMServiceEnabled,OnAccessProtectionEnabled,IoavProtectionEnabled,BehaviorMonitorEnabled,AntivirusEnabled,AntispywareEnabled,RealTimeProtectionEnabled,NISEngineVersion,NISEnabled'
), microsoft_windows_defender_config AS (
SELECT
  MAX(CASE WHEN key = 'AMEngineVersion' THEN value END) AS am_engine_version,
  MAX(CASE WHEN key = 'AMProductVersion' THEN value END) AS am_product_version,
  MAX(CASE WHEN key = 'AMServiceEnabled' THEN value END) AS am_service_enabled,
  MAX(CASE WHEN key = 'AMServiceVersion' THEN value END) AS am_service_version,
  MAX(CASE WHEN key = 'AntispywareEnabled' THEN value END) AS antispyware_enabled,
  MAX(CASE WHEN key = 'AntispywareSignatureAge' THEN value END) AS antispyware_signature_age,
  MAX(CASE WHEN key = 'AntispywareSignatureLastUpdated' THEN value END) AS antispyware_signature_last_updated,
  MAX(CASE WHEN key = 'AntispywareSignatureVersion' THEN value END) AS antispyware_signature_version,
  MAX(CASE WHEN key = 'AntivirusEnabled' THEN value END) AS antivirus_enabled,
  MAX(CASE WHEN key = 'AntivirusSignatureAge' THEN value END) AS antivirus_signature_age,
  MAX(CASE WHEN key = 'AntivirusSignatureLastUpdated' THEN value END) AS antivirus_signature_last_updated,
  MAX(CASE WHEN key = 'AntivirusSignatureVersion' THEN value END) AS antivirus_signature_version,
  MAX(CASE WHEN key = 'BehaviorMonitorEnabled' THEN value END) AS behavior_monitor_enabled,
  MAX(CASE WHEN key = 'ComputerID' THEN value END) AS computer_id,
  MAX(CASE WHEN key = 'ComputerState' THEN value END) AS computer_state,
  MAX(CASE WHEN key = 'FullScanAge' THEN value END) AS full_scan_age,
  MAX(CASE WHEN key = 'IoavProtectionEnabled' THEN value END) AS ioav_protection_enabled,
  MAX(CASE WHEN key = 'LastQuickScanSource' THEN value END) AS last_quick_scan_source,
  MAX(CASE WHEN key = 'LastFullScanSource' THEN value END) AS last_full_scan_source,
  MAX(CASE WHEN key = 'NISEnabled' THEN value END) AS nis_enabled,
  MAX(CASE WHEN key = 'NISEngineVersion' THEN value END) AS nis_engine_version,
  MAX(CASE WHEN key = 'NISSignatureAge' THEN value END) AS nis_signature_age,
  MAX(CASE WHEN key = 'NISSignatureLastUpdated' THEN value END) AS nis_signature_last_updated,
  MAX(CASE WHEN key = 'NISSignatureVersion' THEN value END) AS nis_signature_version,
  MAX(CASE WHEN key = 'OnAccessProtectionEnabled' THEN value END) AS on_access_protection_enabled,
  MAX(CASE WHEN key = 'QuickScanAge' THEN value END) AS quick_scan_age,
  MAX(CASE WHEN key = 'QuickScanEndTime' THEN value END) AS quick_scan_end_time,
  MAX(CASE WHEN key = 'QuickScanStartTime' THEN value END) AS quick_scan_start_time,
  MAX(CASE WHEN key = 'RealTimeProtectionEnabled' THEN value END) AS real_time_protection_enabled,
  MAX(CASE WHEN key = 'RealTimeScanDirection' THEN value END) AS real_time_scan_direction
  FROM wmi_raw GROUP BY parent
)

SELECT * FROM microsoft_windows_defender_config;
+────────────────────+─────────────────────+─────────────────────+─────────────────────+──────────────────────+────────────────────────────+─────────────────────────────────────+────────────────────────────────+────────────────────+──────────────────────────+───────────────────────────────────+──────────────────────────────+───────────────────────────+───────────────────────────────────────+─────────────────+────────────────+──────────────────────────+────────────────────────+─────────────────────────+──────────────+─────────────────────+────────────────────+─────────────────────────────+────────────────────────+───────────────────────────────+─────────────────+────────────────────────────+────────────────────────────+───────────────────────────────+───────────────────────────+
| am_engine_version  | am_product_version  | am_service_enabled  | am_service_version  | antispyware_enabled  | antispyware_signature_age  | antispyware_signature_last_updated  | antispyware_signature_version  | antivirus_enabled  | antivirus_signature_age  | antivirus_signature_last_updated  | antivirus_signature_version  | behavior_monitor_enabled  | computer_id                           | computer_state  | full_scan_age  | ioav_protection_enabled  | last_full_scan_source  | last_quick_scan_source  | nis_enabled  | nis_engine_version  | nis_signature_age  | nis_signature_last_updated  | nis_signature_version  | on_access_protection_enabled  | quick_scan_age  | quick_scan_end_time        | quick_scan_start_time      | real_time_protection_enabled  | real_time_scan_direction  |
+────────────────────+─────────────────────+─────────────────────+─────────────────────+──────────────────────+────────────────────────────+─────────────────────────────────────+────────────────────────────────+────────────────────+──────────────────────────+───────────────────────────────────+──────────────────────────────+───────────────────────────+───────────────────────────────────────+─────────────────+────────────────+──────────────────────────+────────────────────────+─────────────────────────+──────────────+─────────────────────+────────────────────+─────────────────────────────+────────────────────────+───────────────────────────────+─────────────────+────────────────────────────+────────────────────────────+───────────────────────────────+───────────────────────────+
| 1.1.19200.5        | 4.18.2203.5         | true                | 4.18.2203.5         | true                 | 0                          | 20220509023536.000000+000           | 1.363.1657.0                   | true               | 0                        | 20220509023536.000000+000         | 1.363.1657.0                 | true                      | 08FB414B-6118-4183-B65E-3FBA345670EF  | 0               | '-1            | true                     | 0                      | 2                       | true         | 1.1.19200.5         | 0                  | 20220509023536.000000+000   | 1.363.1657.0           | true                          | 6               | 20220502134713.979000+000  | 20220502134622.525000+000  | true                          | 0                         |
+────────────────────+─────────────────────+─────────────────────+─────────────────────+──────────────────────+────────────────────────────+─────────────────────────────────────+────────────────────────────────+────────────────────+──────────────────────────+───────────────────────────────────+──────────────────────────────+───────────────────────────+───────────────────────────────────────+─────────────────+────────────────+──────────────────────────+────────────────────────+─────────────────────────+──────────────+─────────────────────+────────────────────+─────────────────────────────+────────────────────────+───────────────────────────────+─────────────────+────────────────────────────+────────────────────────────+───────────────────────────────+───────────────────────────+

Kolide Launcher SQL: Windows Defender detected threats

There is another important piece of data we need, has Windows Defender detected any threats across my devices? Again, there is a WMI class called MSFT_MpThreatDetection (docs) which we can tap into using Kolide’s WMI to osquery bridge.

Expanding on everything we’ve learned in the last section, we can query this WMI class the same way and produce a single row for each newly detected threat.

Here is the final SQL:

WITH wmi_raw AS (
  SELECT *, SPLIT(parent, '/', 0) AS unique_id FROM kolide_wmi
  WHERE class = 'MSFT_MpThreatDetection'
  AND namespace = '\root\Microsoft\Windows\Defender'
  AND properties = 'DetectionID,ThreatID,ProcessName,DomainUser,DetectionSourceTypeID,Resources,InitialDetectionTime,LastThreatStatusChangeTime,RemediationTime,CurrentThreatExecutionStatusID,ThreatStatusID,ThreatStatusErrorCode,CleaningActionID,AMProductVersion,ActionSuccess,AdditionalActionsBitMask'
), microsoft_windows_defender_threats AS (
SELECT
  MAX(CASE WHEN key = 'DetectionID' THEN value END) AS detection_id,
  MAX(CASE WHEN key = 'ThreatID' THEN value END) AS threat_id,
  MAX(CASE WHEN key = 'ProcessName' THEN value END) AS process_name,
  MAX(CASE WHEN key = 'DomainUser' THEN value END) AS domain_user,
  MAX(CASE WHEN key = 'DetectionSourceTypeID' THEN value END) AS detection_source_type_id,
  GROUP_CONCAT(CASE WHEN fullkey LIKE '%Resources%' THEN value END, ', ') AS resources,
  MAX(CASE WHEN key = 'InitialDetectionTime' THEN value END) AS initial_detection_time,
  MAX(CASE WHEN key = 'LastThreatStatusChangeTime' THEN value END) AS last_threat_status_change_time,
  MAX(CASE WHEN key = 'RemediationTime' THEN value END) AS remediation_time,
  MAX(CASE WHEN key = 'CurrentThreatExecutionStatusID' THEN value END) AS current_threat_execution_status_id,
  MAX(CASE WHEN key = 'ThreatStatusID' THEN value END) AS threat_status_id,
  MAX(CASE WHEN key = 'ThreatStatusErrorCode' THEN value END) AS threat_status_error_code,
  MAX(CASE WHEN key = 'CleaningActionID' THEN value END) AS cleaning_action_id,
  MAX(CASE WHEN key = 'AMProductVersion' THEN value END) AS am_product_version,
  MAX(CASE WHEN key = 'ActionSuccess' THEN value END) AS action_success,
  MAX(CASE WHEN key = 'AdditionalActionsBitMask' THEN value END) AS additional_actions_bit_mask
  FROM wmi_raw GROUP BY unique_id)

SELECT * FROM microsoft_windows_defender_threats;
+─────────────────+──────────────────────────────+─────────────────────+─────────────────────+─────────────────────────────────────+─────────────────────────────────────────+───────────────────────────+────────────────────────+────────────────────────────+─────────────────────────────────+───────────────+────────────────────────────+──────────────────────────────────────────────────────────────────────────────────────────+────────────+───────────────────────────+───────────────────+
| action_success  | additional_actions_bit_mask  | am_product_version  | cleaning_action_id  | current_threat_execution_status_id  | detection_id                            | detection_source_type_id  | domain_user            | initial_detection_time     | last_threat_status_change_time  | process_name  | remediation_time           | resources                                                                                | threat_id  | threat_status_error_code  | threat_status_id  |
+─────────────────+──────────────────────────────+─────────────────────+─────────────────────+─────────────────────────────────────+─────────────────────────────────────────+───────────────────────────+────────────────────────+────────────────────────────+─────────────────────────────────+───────────────+────────────────────────────+──────────────────────────────────────────────────────────────────────────────────────────+────────────+───────────────────────────+───────────────────+
| true            | 0                            | 4.18.2203.5         | 9                   | 0                                   | {041A2E1E-54BB-477F-A953-EDD187B66CC7}  | 1                         | DESKTOP-2HFBS8U\jason  | 20220430211822.148000+000  | 20220501044223.930000+000       | Unknown       | 20220501044223.930000+000  | "file:_C:\Users\jason\Downloads\eicar(1).com, file:_C:\Users\jason\Downloads\eicar.com"  | 2147519003 | 0                         | 106               |
| true            | 0                            | 4.18.2203.5         | 2                   | 0                                   | {08341268-342B-469E-A826-B9B3A90D1037}  | 2                         | NT AUTHORITY\SYSTEM    | 20220501043200.985000+000  | 20220501043227.380000+000       | Unknown       | 20220501043227.380000+000  | "file:_C:\Users\jason\Downloads\eicar(1).com, file:_C:\Users\jason\Downloads\eicar.com"  | 2147519003 | 0                         | 3                 |
+─────────────────+──────────────────────────────+─────────────────────+─────────────────────+─────────────────────────────────────+─────────────────────────────────────────+───────────────────────────+────────────────────────+────────────────────────────+─────────────────────────────────+───────────────+────────────────────────────+──────────────────────────────────────────────────────────────────────────────────────────+────────────+───────────────────────────+───────────────────+

How Do I Centralize the Data and Show It to Auditors?

The question now becomes, how do you best aggregate the data collected via osquery and show it to auditors?

Osquery out of the box emits logs that can be aggregated by third-party SIEMs and log aggregation tools. Using their native reporting functions, you can build a dashboard that will get you through your audit and give you incredible visibility.

If you don’t want to build all this yourself, Kolide can get you up and running fast. Kolide’s product automatically gives you native installers for Macs, Windows, and Linux that install osquery. Once the agent runs, Kolide will automatically collect all the pertinent info, aggregate it, and visualize it. Within a few minutes, you could be looking at a dashboard like this:

Kolide's Inventory automatically aggregates information you need to show auditors for SOC2.

Kolide’s Inventory automatically aggregates information you need to show auditors for SOC2.

Additionally, Kolide can give you API access and full documentation about the data it collects.

Another question vanilla osquery doesn’t have an answer for is remediation. For example, if you find Windows Secure Boot is disabled (which helps ensure the integrity of the underlying Defender system), how do you fix it? One approach is to buy a Windows Device Management product and apply policies to force these settings on. While that can work, not everything can be automated this way. There is no way to enable Secure Boot without the user’s help remotely.

Again, Kolide can run checks against your Windows PC to verify that these services are enabled. If they aren’t, Kolide’s Device Trust solution notifies integrates with Slack to message end-users and directs them on how to re-enable those features (and explain why they are important to keep them that way).

A screenshot of Kolide notifying an end-user about the need to turn on Secure Boot with step-by-step instructions on how to do it.

Kolide can reach out to an end user directly to let them know that Secure Boot was turned off and help them get it back on as soon as possible.

End-user notifications are a part of our Honest Security philosophy. We believe that teaching end-users how to keep their devices secure nets better and more complete security over simply locking the machine down.

To see how Kolide can secure your fleet and achieve 100% compliance, watch our on-demand demo today.


  1. Compliance auditors get annoyed when you use binary terms like β€œpass” or β€œfail” to describe the outcome of an audit. Instead they use terms like β€œmodified” or β€œqualified”. When I use the word β€œpass” in this article, I mean that you have obtained a SOC 2 report without negative qualifications. ↩

Share this story:

More articles you
might enjoy:

Changelog
New Inventory: Windows Defender and XProtect Reports
Kolide
Deep Dives
The 10 Minute Guide to SOC 1 vs SOC 2
Kenny Najarro
Deep Dives
Do Macs Need Third-Party Antivirus for SOC 2 Compliance?
Jason Meller
Watch a Demo
Watch a Demo