Decorators not updating in results log- linter should catch decorator ALIAS table

[pacamaster]

Fleet version: <!-- Copy this from the "My account" page in the Fleet UI, or run fleetctl --version --> 4.47.3 current stable fleetd agent (osquery 5.11.0, Orbit 1.22.0) Web browser and operating system: Current macOS and Chrome browser

---

💥  Actual behavior

Decorators do not seem to be updating for query result logs. Using a query that ingests the ATC table in the above config works, but the decorators are not being updated AS serial_number. The AS serial_number was in the agent options config before the ATC table was added to the config. The data is modeled for serial_number but displays as hardware_serial and the client has ended up with a mix of data with serial_number and hardware_serial. These keys are important to tag in their ingestion and when expecting a value gets set to null and is extra work for them. Confirmed this host is part of the "no team" so the above agent options should apply.

🧑‍💻  Steps to reproduce

1. update decorators in the agent config
2. notice results log the decorators are not what is set in agent options

🕯️ More info (optional)

Overlooked that was setting alias to the table and not columns.

~~The hunch is that decorators when changed in config is not updating on load. In the meantime, we had them change decorators to always on a canary team to see if this loads. They did state they had set it to hardware_serial at first and saw this same trouble. Changed decorator to serial_number, then they redeployed the agent package with --enable_scripts for a separate issue, saw the decorators update on their No Team devices- but now it seems to have converted back to the decorator for hardware_serial.~~

Also confirmed with tagged client that there is not some other platform managing their osquery config (Chef, Ansible, etc).

Currently waiting for a scheduled query to run and see if data from the canary team is valid. If this is the case then would suggest an interval setting with 6-12 hr

🛠️ To fix

• Decorator was calculated for table not column. Should be SELECT hardware_serial AS serial_number FROM system_info;
• Additionally, would be nice to have a way to check the health and validate the config fleetd is running currently

[pacamaster]

After thinking this over more, think mysql syntax is not correct. It is setting Alias for the table, not the column. Correct decorators-

SELECT uuid AS host_uuid FROM system_info;
SELECT hostname AS hostname FROM system_info;
SELECT hardware_serial AS serial_number FROM system_info;

[JoStableford]

Related to a Slack conversation

[nonpunctual]

This would require linting SQL statements INSIDE the yaml file.

[getvictor]

@sharon-fdm @pacamaster Looks like this issue changed from a bug to a feature request: Lint SQL statements inside agent options

Should we take it through feature fest?

[nonpunctual]

@getvictor The original statement from customer was "I had an error the linter didn't catch" but I don't really think this is a bug.

The actual state was: the linter IS validating yaml in this file. The decorator takes a SQL query. The linter IS NOT validating SQL IN the yaml file.

To fulfill this request we would need to have validation of both the yaml & any SQL strings in key/vals IN the yaml. That seems like it would be hard but we kind of need an level of effort estimate to know if my hunch is correct. Thanks.

[getvictor]

SQL linting would be useful for any API/fleetctl interactions that save queries/policies. For example, recently we put in a misformatted policy query via gitops flow and didn't catch it for a few days.

[sharon-fdm]

Assigning @noahtalerman to prioritize.

[nonpunctual]

I am all for it if you think this kind of validation is valuable. Thanks!

[noahtalerman]

Thanks all!

Removing the bug label because we decided this is a feature request.

[nonpunctual]

@noahtalerman We may want to make the title more general as this isn't (per @getvictor) really a problem specific to decorators or logs. It's more about adding additional SQL parsing / linting for yaml configuration file inputs. Thanks.

[noahtalerman]

@nonpunctual I think let's keep it specific to decorators and file separate stories for adding parsing to other areas.

This way, we can work on the decorators fix first.

At Fleet, we want to make iterative improvements so that we can ship value quickly.