Osquery Queries from various sources

gistfile1.txt

SELECT p.pid, p.name, p.state, u.username, lp.*
FROM processes p
INNER JOIN listening_ports lp
ON lp.pid = p.pid
INNER JOIN users u
ON u.uid = p.uid;

SELECT u.username,
g.groupname
FROM users u
INNER JOIN user_groups ug
ON u.uid = ug.uid
INNER JOIN groups g
ON g.gid = ug.gid;

select s.uid,
s.command,
s.history_file,
u.username
from shell_history s
join users u on u.uid=s.uid;

select s.uid,
s.command,
s.history_file,
s.time,
u.username
from shell_history s
join users u on u.uid=s.uid;

select s.uid,
s.command,
s.history_file,
s.time,
u.username
from users u 
join shell_history s on s.uid=u.uid
where s.time > ((select strftime('%s','now')) - 86400);