Audit and Remediation: Error: "Table file was queried without a required column in the WHERE clause"

Products

• Carbon Black Cloud Endpoint Standard (formerly Cb Defense)

Issue/Introduction

Error is displayed from OSQuery result saying:

"Table file was queried without a required column in the WHERE clause"

Environment

• Carbon Black Cloud: All Versions
  • Audit and Remediation (formerly CB LiveOps)
• OS Query: 2.11.2 - Current

Cause

• Incorrect column specified for table in WHERE clause.  Example:

SELECT * FROM file WHERE (bad column here) LIKE 'c:\temp\%';

• A built-in OS Query failsafe prevents the use of a single column to be queried.  Example:

SELECT * FROM file WHERE filename LIKE '%.txt';

Resolution

1. If a column is not specified or specified incorrectly then one should be appended using the WHERE clause and one of the approved operators.  Example:

   SELECT * FROM file WHERE path LIKE 'C:\Temp\%';

2. If a WHERE clause is being used only with a field such as "filename" then append another field to be queried, such as "path".  Example:

   SELECT path, type FROM file WHERE path like 'C:\Temp\%' AND filename like '%.txt';

Additional Information

• At this time there is not a list of known "failsafes" from OS Query.
• For a list of available columns and tables that can be queried please see OSQuery's Schema.