Title
#fleet
p

Philip Royer

09/26/2022, 7:57 PM
We seem to be having some issues with the distributed query called
fleet_detail_query_software_windows
. In particular, we might be hitting a CPU utilization limit with the Python package listing on some Windows systems. Is there a way to change that query or prevent it from running?
s

sharvil

09/27/2022, 1:56 PM
hey @Philip Royer, do you know if these windows hosts are domain controllers by any chance? and is it possible to share the logs around it, thanks
p

Philip Royer

09/27/2022, 2:04 PM
These are not domain controllers. I am afraid I cannot share the whole log but I think the relevant portion is here:
I0926 14:18:31.796871 28964 distributed.cpp:131] Executing distributed query: fleet_detail_query_software_windows: WITH cached_users AS (WITH cached_groups AS (select * from groups)
 SELECT uid, username, type, groupname, shell
 FROM users LEFT JOIN cached_groups USING (gid)
 WHERE type <> 'special' AND shell NOT LIKE '%/false' AND shell NOT LIKE '%/nologin' AND shell NOT LIKE '%/shutdown' AND shell NOT LIKE '%/halt' AND username NOT LIKE '%$' AND username NOT LIKE '\_%' ESCAPE '\' AND NOT (username = 'sync' AND shell ='/bin/sync' AND directory <> ''))
SELECT
  name AS name,
  version AS version,
  'Program (Windows)' AS type,
  'programs' AS source
FROM programs
UNION
SELECT
  name AS name,
  version AS version,
  'Package (Python)' AS type,
  'python_packages' AS source
FROM python_packages
UNION
SELECT
  name AS name,
  version AS version,
  'Browser plugin (IE)' AS type,
  'ie_extensions' AS source
FROM ie_extensions
UNION
SELECT
  name AS name,
  version AS version,
  'Browser plugin (Chrome)' AS type,
  'chrome_extensions' AS source
FROM cached_users CROSS JOIN chrome_extensions USING (uid)
UNION
SELECT
  name AS name,
  version AS version,
  'Browser plugin (Firefox)' AS type,
  'firefox_addons' AS source
FROM cached_users CROSS JOIN firefox_addons USING (uid)
UNION
SELECT
  name AS name,
  version AS version,
  'Package (Chocolatey)' AS type,
  'chocolatey_packages' AS source
FROM chocolatey_packages
UNION
SELECT
  name AS name,
  version AS version,
  'Package (Atom)' AS type,
  'atom_packages' AS source
FROM cached_users CROSS JOIN atom_packages USING (uid)
UNION
SELECT
  name AS name,
  version AS version,
  'Pack
age (Python)' AS type,
  'python_packages' AS source
FROM python_packages;
W0926 14:19:20.678611 18952 watcher.cpp:397] osqueryd worker (636) stopping: Maximum sustainable CPU utilization limit exceeded: 48
2:06 PM
I narrowed that search down by running one portion at a time, and it appears the python_packages part is the one with the performance issue on about 5% of our machines. Our workaround for now is to use global agent config to disable the
python_packages
table entirely. But ideally we would have some more control over that query so we could dial it in.
s

sharvil

09/27/2022, 2:09 PM
gotcha, I am double checking, but I believe that query is run as a part of software inventory — I wonder if setting
enable_software_inventory: false
would help narrow things down a bit
2:21 PM
can you share what osquery version are you running on?
p

Philip Royer

09/27/2022, 2:36 PM
Most are on 5.3 with a few on 5.4