8 Live Queries That Will Speed Up Your Next PCI Audit

ID CARBONBLACK:BB7CE57652DBA4F933753D369F707474
Type carbonblack
Reporter Katie DeMatteis
Modified 2019-05-08T17:00:39


It’s no secret that kicking off any kind of compliance audit can be a slow, tedious project. This is especially true when it comes to performing a pre-assessment gap analysis for PCI-DSS. Ask any qualified security assessor (QSA), and they’ll tell you that the data gathering, scoping, and gap analysis exercises can often form a large portion of the audit and assessment process.

But this doesn’t have to be the case. At Carbon Black, we have created programs utilizing automated solutions that can proactively collect system attributes and configuration metrics that are required as part of the PCI gap analysis phase. Use of these solutions have helped Carbon Black customers reduce audit time by 60-80%.

There are dozens of different tools that security, IT teams, and assessors could use to automate this data collection process, but some of the most powerful and flexible solutions that I’ve had success using to accelerate the front-end audit process are the live endpoint query tools, such as osquery, that are fairly well known among QSAs and becoming increasingly popular with security teams.

For anyone who has never heard of osquery, it’s an open source project that allows security & IT teams to use SQL commands (SQLite, to be more specific) to query data about the current, resting state of their endpoints. This means that, rather than starting from scratch or relying on months-old data, these live endpoint query tools make it extremely easy for teams to pull back specific sets of data about the current state of their endpoints exactly when they need it.

Osquery was originally built with security use cases in mind, but over the years it has continued to evolve to cover IT hygiene, compliance, incident response, vulnerability management, help desk operations, and containers use cases across Windows, Mac, and Linux machines.

Below you’ll find a handful of queries that my colleague, Jon Nelson, and I built for osquery - and for our own CB LiveOps which was built on top of osquery - that map directly to some of the core data gathering metrics called for in many PCI-DSS requirements. Feel free to copy these for your own use to help reduce the time of pre-assessment during your audits. And for any other osquery fans out there, please send over any other queries you’ve built to map with additional PCI requirements.

Detecting Disabled Windows Firewall

PCI 1.4: Personal firewall or equivalent functionality is actively running.


WHEN 0 THEN “True”

WHEN 1 THEN “False”

END “Firewall Disabled”,datetime(mtime, “unixepoch”, “localtime”) AS “Last Modified”

FROM registry

WHERE key = “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile”

AND name = “EnableFirewall”

AND data = 0;

Status of Windows Defender Firewall Service
PCI 1.4.b: Inspect a sample of company and/or employee-owned devices to verify that personal firewall or equivalent functionality is installed.

SELECT display_name,status,start_type

FROM services

WHERE lower(name) = “mpssvc”;

Access to Compliance Systems

PCI 1.3.6: Examine firewall and router configurations to verify that system components that store cardholder data are on an internal network zone.

SELECT u.username AS User, net.remote_address AS “PCI Systems”

FROM users AS u


(SELECT pos.remote_address,pos.pid,p.uid

FROM processes AS p

JOIN process_open_sockets AS pos

ON p.pid = pos.pid

WHERE remote_address

NOT IN (“0”,”::”,””)

AND remote_address IN (“”,””)

) AS net

ON net.uid = u.uid;

Find File Shares

PCI 2.2.2: Enable only necessary services, protocols, daemons, etc. as required for the function of the system.

SELECT name,path

FROM shared_resources

WHERE type = 0;

Find TLS 1.0 Systems

PCI 2.2.3: Implement additional security features for any required services, protocols, or daemons that are considered to be insecure.

PCI 4.1: Use strong cryptography and security protocols to safeguard sensitive cardholder data during transmission over open, public networks.


FROM registry

WHERE key = “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Server”

AND type = “REG_DWORD”

AND data = 1;

Search Host Files with Entries & Rogue DNS Servers

PCI 2.2.5.a: Select a sample of system components and inspect the configurations to verify that all unnecessary functionality is removed.

Search Files with Entries


FROM etc_hosts

GROUP BY address

HAVING count(address) > 0;

Rogue DNS Servers

SELECT dns_server_search_order

FROM interface_details

WHERE dns_server_search_order != “”

AND dns_server_search_order


Hash A Directory

PCI 10.5.5: Use file integrity monitoring or change detection software on logs to ensure that existing log data cannot be changed without generating alerts.

PCI 11.5.a: Verify the use of change-detection mechanism by observing system settings and monitored files, as well as reviewing results from monitoring activities.

SELECT path,sha256

FROM hash

WHERE path in

(SELECT path

FROM file

WHERE size < 50000000

AND path like “/Users/%/Documents/%%”);

See Also:

The post 8 Live Queries That Will Speed Up Your Next PCI Audit appeared first on Carbon Black.