See cached query results

[noahtalerman]

Goal

As a user, early on a Monday morning when only 20% of my productivity endpoints are online, I want to use Fleet to explore, ask questions, obtain insights, and plan automations, without having to go into another tool (my log destination).

Parent epic

• #6716

Figma

https://www.figma.com/file/hdALBDsrti77QuDNSzLdkx/%F0%9F%9A%A7-Fleet-EE-(dev-ready%2C-scratchpad)?node-id=9648%3A317838

Child issues

• UI (interface)
• API (interface)
• Data storage (platform)

[noahtalerman]

@mike-j-thomas can you please help me with wireframes for the below user stories?

As a user, I want to be able to modify frequency and platforms when I save a query.

As a user, I want to understand the frequency and platform concepts.

As a user, I want to be able to tell Fleet to not run the query when I save the query.

As a user, I want to be able to modify frequency and platforms for an existing query.

[noahtalerman]

@mike-j-thomas I assigned myself and removed the issue from the digital experience board.

Also, I moved your screens that included improvements to the query console (gray pencil icons and "Save" dropdown) to a 🧼 Query console improvements Figma page here: https://www.figma.com/file/hdALBDsrti77QuDNSzLdkx/%F0%9F%9A%A7-Fleet-EE-(dev-ready%2C-scratchpad)?node-id=9648%3A330604

[noahtalerman]

I added several states to the Query page here (vertically stacked): https://www.figma.com/file/hdALBDsrti77QuDNSzLdkx/%F0%9F%9A%A7-Fleet-EE-(dev-ready%2C-scratchpad)?node-id=9655%3A332302

• No response
• No data
• Only errors
• No frequency
• Too much data

@chris-mcgillicuddy can you please review and help me with copy for all of the above states?

@mike-j-thomas can you please help me with illustrations for the "Only errors" and "Too much data" states?

[noahtalerman]

@lukeheath we are de-prioritizing this. I removed your assignment. I removed the issue from the roadmap board.

The "Combine schedule and query features" is still prioritized.

[zayhanlon]

Hey @mikermcneil. We're deprioritizing this issue as we won't be able to deliver it in the next 6 weeks. As this item is part of the larger #6716 initiative, we will bring it back onto the board when we know it will fit into an upcoming sprint.

[sharon-fdm]

@mikermcneil @zhumo @rachaelshaw Question: queries with automation have an option called differential (Saving the diff from last result). Do we want a similar thing for caching the results?

Reason: some queries are sent very frequently (e.g. every 1 min) and may overload the customer network if we will send the full result for caching.

cc: @zwass @lucasmrod @mna @mostlikelee

[zhumo]

Hi @sharon-fdm, what is the user experience impact of doing it one way or the other?

[sharon-fdm]

@zhumo per my understanding some queries are being scheduled very frequently (1 min) in order to catch some changes.

In those cases, only the changes are being sent back to the server (most of the times nothing is sent). If we would now send the full results in order to cache them, it could mean a lot of additional load on the customer's network.

Later edit: This is to say that we will need to think well about how we set the default value of caching. To consider:

• When migrating, do we want to set all existing scheduled queries to be cached by default? (Not discarded)
• Maybe just those that have automation set to snapshot?
• Consider creating a mechanism for differential results (Same as for automation)

[sharon-fdm]

Additional discussion

[zhumo]

Hi @mikermcneil, we were not able to get to this story as part of this sprint.

[rachaelshaw]

Heads up: since we don't estimate sub-tasks, I'll be removing the estimated points from individual sub-task issues and adding an estimate to this user story instead.

Here's a breakdown of how we arrived at 35 points for this one:

Sub-task issue	Estimate
Agree on all APIs for caching query results #13469	2
Define test plan for the whole feature #13491	1
Run load tests #13492	5
Implement API #13489	3
Implement logic to discard cached results from DB in several scenarios #13488	3
Implement logic to capture scheduled queries results and push to DB incase all prerequisites apply #13486	5
Add Database tables and columns #13485	3
Create a QA Wolf ticket to test the caching frontend changes #13475	2
Caching Feature Flag for caching #13474	1
Create modals to warn when we discard data due to 1-changing the SQL 2-pressing "discard data" #13473	2
Create the details table logic (Part of the details page) #13472	3
Create "Query details page" #13471	3
Implement the "Discard Data" checkbox on "Save new Query" and "edit query" #13470	2
TOTAL	35

cc @sharon-fdm

Edit: Just realized this info is actually supposed to be in the issue description as a checklist. Will also add it there.

[zhumo]

From @mostlikelee: @rachaelshaw @zhumo: we have an arbitrary number of columns to display, do we want to enable filter and sort on any of these columns?

[mostlikelee]

Kickoff doc: https://docs.google.com/document/d/14zqrBb8duyIx1veoJHD5ahHkXNYO5aa6R0Xs7fsdfzA/edit#heading=h.vxhh1dz2gfd4

[rachaelshaw]

From Slack:

Reed Haynes :bento: 1 hour ago A few product / cx questions re: the cache monet work @Mo Zhu @Rachael Shaw : What is the expected outcome of a host returning data for a query that no longer exists? (I believe currently the results are tossed; not saved in the DB) I believe there are cases where a query might return different columns based on the OS (ex: chrome vs windows). There is also an edge case where if the SQL of a query is changed the columns might change and a host with an older result might report to the server before a host with the new SQL returns results to the server. My understanding is we were to hide mismatched results (when columns don't match) and whichever result arrived at the server first would take precedence. Attached is a screenshot of the dev note for this case. I just wanted to confirm hiding results is the desired behavior and that in cases where different OS's return different columns, this "hiding" functionality should also be present. Taking this further, if the host that had returned the old SQL returns new data with the updated SQL, there are cases where the hidden results would now be visible since the not matching column data has been removed. This last case is kinda weird to write out.. I'm happy to jump into a quick call to discuss if that is needed. image.png

:eyes: 1

3 replies

Rachael Shaw 1 hour ago asdsadsadsa I didn't even think of different platforms returning different columns, is that pretty common? If I'd been thinking of that, probably would have handled the different-column-structure edge case differently, by displaying all the columns from the different results on the screen and showing "---" for any that don't exist on a row. It's too late to change for this release, but I'll add a request to next Feature Fest to handle this better.

Reed Haynes :bento: 37 minutes ago cc @Rachel Perkins ^^

Rachael Shaw 4 minutes ago If this is a common thing, then we're probably already handling having different column structures the live query results, aren't we? Wish I noticed that before the last week of the sprint, we totally should have copied whatever we're doing there.

FYI just got clarification that we should handle this before the release and not next sprint— talked through the change (no hidden columns, empty states for results where a column isn't included) and the design is approved, making the change in Figma right now. @xpkoala @jacobshandling @RachelElysia will let you know as soon as the screen is updated.

[noahtalerman]

Zach: Have we updated the permissions documentation to show what each user type can do with this feature?

@lucasmrod did we do this?

UPDATE: Lucas: As implemented, if a user can “read” a query, it can read its results.

[lucasmrod]

Have we updated the permissions documentation to show what each user type can do with this feature?

We haven't, we'll submit a PR today.

[noahtalerman]

From "UI sweep of the upcoming release" call:

Fleet version: commit 637430f442b27618107922370173b9a8df40923e

• Bug: Inconsistent spacing above tooltip line:

• Bug: Spacing above tooltip line doesn't match Figma:

• Bug: Shadow doesn't appear above the input:

• Feature: Sometimes the watchdog kills a query but we don't tell the user (empty or clipped): After 1 day, osquery will try to run the query again. Live query triggers the watchdog

• Bug: Inconsistent empty states: some are italic and some aren't Expected behavior: Just dim (no italic).

• Bug: Inconsistent alignment: left align header and center text:

• Bug: Re-sizing pages w/ a card isn't consistent:

  • OS updates is different than OS settings

• Bug: Padding is inconsistent above and below empty state:

• Bug: Width of Status column jumps when a script goes from Ran to Pending state:

TODO Noah: File an issue for the feature

DONE Reed: Add this to chaos engineering agenda

cc @mikermcneil @xpkoala @sharon-fdm @sabrinabuckets

[lucasmrod]

Feature: Sometimes the watchdog kills a query but we don't tell the user (empty or clipped):

We can get the scheduled queries that have been denylisted with the denylisted column:

SELECT name, denylisted FROM osquery_schedule;

(We can do so when ingesting scheduled query stats, which we do once per hour.)

[noahtalerman]

C&C: Hey @rachaelshaw when you get the chance, can you please take on the remaining doc changes for this story?

Please feel free to cut any if they're not reference changes.

• [ ] Documentation changes:
  • [x] REST API (Draft PR: https://github.com/fleetdm/fleet/pull/13484)
    • [x] Queries API changes
    • [x] Configuration API changes (org settings)
  • [ ] Configuration > Configuration files
    • [ ] Update queries YAML example
    • [x] Update config YAML example (org settings)
  • [ ] Using Fleet > Fleet UI - update content about queries UI to

[noahtalerman]

C&C: @rachaelshaw to close this story when docs PR is merged here: https://github.com/fleetdm/fleet/pull/14977

[fleet-release]

Cache of queries last, Like cloud city's mirrored past, Insight unsurpassed.