Hello all!
I have a query using the apps table, a...
# fleetm
Mike S.
02/12/2025, 3:05 PMHello all!
I have a query using the apps table, and I would like to see the users that are associated with the apps that are found. From what I can tell, the only piece of information that's the same across the apps table and the users or logged_in_users table is the Host column, which doesn't seem to be query-able. Is there a way to include the users associated with the apps?
u
Unthread
02/12/2025, 4:09 PMHi @Mike S. (osquery) ! How are you using this data? Are you running live queries or scheduled queries?
m
Mike S.
02/12/2025, 4:09 PMHi @Kathy Satterlee Right now it's live queries
u
Unthread
02/12/2025, 4:13 PMAre you looking to narrow down which applications are system wide vs user specific, or just wanting to have a quick view of the users on the host in the query results?
m
Mike S.
02/12/2025, 4:21 PMHere's the query -
SELECT bundle_executable,last_opened_time,
datetime(last_opened_time, 'unixepoch') AS last_opened_time FROM apps
WHERE bundle_executable LIKE 'redacted'
AND last_opened_time > strftime('%s', 'now', '-7 days');
Basically we'd want to see the user(s) associated with the host that ran the application we're looking for without running a separate query.
u
Unthread
02/12/2025, 4:23 PMYou could certainly join the users table, I'd likely do a group concat on that so that you don't get back a million rows. Let me throw something together.
m
Mike S.
02/12/2025, 4:24 PMAwesome, thank you!
k
Kathy Satterlee
02/12/2025, 6:42 PMI haven't forgotten about you @Mike S.
m
Mike S.
02/12/2025, 6:43 PM@Kathy Satterlee No worries, take your time.
k
Kathy Satterlee
02/12/2025, 7:05 PMThis looks like a good starting place:
Copy code
WITH local_users AS (
SELECT
GROUP_CONCAT (username, ', ') AS user_list
FROM users
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 '%$'
)
SELECT bundle_executable,
datetime(last_opened_time, 'unixepoch') AS last_opened_time,
user_list
FROM apps
JOIN local_users
WHERE apps.last_opened_time > strftime('%s', 'now', '-7 days');m
Mike S.
02/12/2025, 7:10 PMPerfect, thank you!
Mike S.
02/12/2025, 7:13 PMI just threw in the bundle_executable name at the bottom and I'm golden.
k
Kathy Satterlee
02/12/2025, 7:16 PMBrilliant. The user query might need some tweaking, but it seems to get the job done fairly well as is.