Osquery makes the built-in antivirus in Windows audit ready.
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). Others are content with the built-in anti-malware capabilities of Windows, and thus have no plans to deploy AV on its own 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:
They don’t believe Windows has sufficient anti-malware capabilities to pass a SOC2 audit.*
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 SOC 2 audit with the built-in anti-malware capabilities of Windows (Microsoft Defender). I’ll also explain how you can “defend” (no pun intended) that position to senior leadership and auditors.
*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.
Holistically, Windows' security is better than third-party AV
Ideally, before you face a SOC 2 audit, you should genuinely believe you’ve made the best decisions possible regarding the security of your Windows devices. For example, as a security practitioner, I do actually believe that many organizations are better off relying on the built-in security capabilities of Microsoft 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.” Most AV security companies build their entire pitch based on a few measurements:
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?
Unfortunately, these measurements fail to consider the costs paid (usually by the end-user) for marginal improvements across these metrics.
But the end-user misery of third-party AV isn’t typically addressed until it becomes so egregious that it can be linked to a significant adverse financial event. To account for every form of misery that falls short of that bar, we need to adjust how we 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’s why:
OS vendors are financially impacted if users think their OS runs like junk.
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 other 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 adding it to an allowlist).
OS vendors can use vertical integration 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, and they cannot successfully advocate for dedicated hardware within the device to make 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.
Microsoft Defender antivirus
Initially released in 2009 (under the name Microsoft Security Essentials), Microsoft Defender Antivirus has evolved into a fully featured and well-regarded antivirus app that is included in all versions of Windows (including 10 & 11), as part of Microsoft’s broader family of security products.
Microsoft Defender Antivirus 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.
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 Microsoft Defender portal.
Additionally, the security software uses machine learning, big-data analytics, threat resistance research, and more to protect endpoints from known viruses and zero-day cyberattacks. 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.
Perfect detection isn’t possible
When pitted against Microsoft’s comprehensive built-in security, AV vendors' common arguments to justify their products come down to 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. It’s just as easy to find successful malware campaigns that no antivirus vendor could detect in a timely manner. Perfect detection/prevention is not possible, so we have to consider the other costs we pay. For instance, the trust we lose if we subject end-users to guaranteed performance degradation, false positives, and additional attack surface. If users are keeping a tight ship, applying updates, and not disabling UAC, there’s very little chance that any other marginal improvements in protection will 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 cybersecurity investments you can make.
Compiling data to meet audit requirements
As we saw above, Microsoft 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. And Microsoft doesn’t offer a way to achieve that level of fleet visibility without purchasing their suite of Endpoint Detection, Management, and Security tools (which is 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 also 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.
Many IT professionals favor osquery because it’s simple, reliable, and extensible. And since it works for all three operating systems, you can collect data on every device in your fleet without needing different tools.
How To Use Data Collected By Osquery to Support SOC 2
To pass your SOC 2 audit, you must create documentation demonstrating that your systems and processes meet specific requirements.
For instance, to show that you have the appropriate defense against malware and viruses according to Common Criteria 6.8, you need a report describing 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 for malware and other unauthorized software.
Putting osquery into action For SOC 2 compliance
Microsoft 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 (or “Windows Security").
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 high-level insight that we need.
And, lucky for you, we’ve already contributed a table to osquery for querying 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 Microsoft 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, 1Password® Extended Access Management’s device trust 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.
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 1Password Extended Access Management, 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 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 |
+────────────────────+─────────────────────+─────────────────────+─────────────────────+──────────────────────+────────────────────────────+─────────────────────────────────────+────────────────────────────────+────────────────────+──────────────────────────+───────────────────────────────────+──────────────────────────────+───────────────────────────+───────────────────────────────────────+─────────────────+────────────────+──────────────────────────+────────────────────────+─────────────────────────+──────────────+─────────────────────+────────────────────+─────────────────────────────+────────────────────────+───────────────────────────────+─────────────────+────────────────────────────+────────────────────────────+───────────────────────────────+───────────────────────────+
SQL: Windows Defender detected threats
There’s 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 1Password Extended Access Management’s WMI to osquery bridge.
With the same techniques we used with the SQL from the previous 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 for 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, 1Password Extended Access Management can get you up and running fast. Our device trust solution automatically gives you native osquery installers for Mac, Windows, and Linux. Once the agent runs, 1Password Extended Access Management will automatically collect all the pertinent info, aggregate it, and visualize it within your admin dashboard.
1Password Extended Access Management can also give you API access and full documentation about the data it collects. And if your company uses Microsoft Entra as your SSO provider, we have even further integrations.
On top of that, we solve for another question vanilla osquery doesn’t have an answer for: 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?
You can buy a Windows Device Management product, and apply policies to force certain settings to be turned on. But not everything can be automated this way. There is no way to enable Secure Boot remotely without the user’s help.
Again, 1Password Extended Access Management can run checks against your Windows PC to verify that these services are enabled. If they aren’t, users are blocked from accessing company resources until they’ve fixed the issue.
We achieve this through end-user remediation, instructing users on how to re-enable those features (while explaining why it’s important to keep them that way).
From there, users have a deadline on when they need to remediate the issue, or else they’ll be locked out of company systems. And unlike MDM, you can apply this approach to device trust to unmanaged, BYOD devices.
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 than any AV scan ever could on its own.
To see how 1Password Extended Access Management can secure your fleet and achieve 100% compliance, reach out for a demo.
Jason Meller
VP, Product Management
Tweet about this post