Does anyone use osquery to detect abnormal server ports and processes? Share my query statement, welcome everyone to discuss the feasibility Abnormal port: select p.name as process_name, p.path, lp.port, lp.address, lp.protocol from listening_ports lp LEFT JOIN processes p ON lp.pid = p.pid WHERE lp.port != 0 AND p.name != '' Abnormal proccess: select distinct(pr.name), usr.username, pr.path, pr.cmdline from processes pr LEFT JOIN users usr ON pr.uid = usr.uid WHERE pr.cmdline != '' AND pr.name not like '%systemd%' AND pr.name not like '%nslcd%' AND pr.name not like '%filebeat%' AND pr.name not like '%agetty%' AND pr.name not like '%node_exporter%' AND pr.name not like '%zabbix%' AND pr.name not like '%dns%' AND pr.name not like '%dockerd%' AND pr.name not like '%redis%' AND pr.name not like '%sleep%' AND pr.name not like '%chronyd%'

i havent really used osquery to detect suspicious/abnormal processes or server ports but a few things that i would consider adding on to the queries u posted are file and hash entries ex: for listening_ports timestamps can be helpful to determine when the binary was modified,created,accessed. Hashes could be useful to check if the binary is known on other online services (virustotal, etc). File size may also be useful

select datetime(f.btime, 'unixepoch'), datetime(f.mtime,'unixepoch'), datetime(f.atime, 'unixepoch'), datetime(f.ctime, 'unixepoch'), h.md5,  p.name as process_name, p.path, lp.port, lp.address, lp.protocol from listening_ports lp LEFT JOIN processes p ON lp.pid = p.pid join file f on p.path = f.path join hash h on p.path = h.path WHERE lp.port != 0 order by f.ctime;

for processes in addition to excluding specific process names u could try search for all processes spawning outside '%/bin/%' or '%/sbin/% paths ex:

select p.path, datetime(f.btime, 'unixepoch'), datetime(f.mtime,'unixepoch'), datetime(f.atime, 'unixepoch'), datetime(f.ctime, 'unixepoch'), h.md5 from processes p join file f on p.path = f.path join hash h on h.path = f.path where p.path not like '%bin%' order by f.ctime;

if u want to include just files in the bin paths just remove the 'not' another idea for listening_ports is use process_open_sockets instead which shows active connections by processes also for processes u could also try stack counting and look for low freq occurrences a basic example below:

select path, name,count(path) as countname from processes group by name order by countname;

so for example if u run this query on 100 systems and 98 of them return return the python process running that is likely normal but 2 of them return java running, that could be considered abnormal another idea for detecting abnormal processes is using osquery to reconstruct process trees, there are a handful of online resources that can assist with that query. though it can return alot of data im not a sql expert so there could be more advance things u could try

Thank you very much for your comments and opinions, I completely agree with your ideas, and thank you very much for your answers👍

No, I only work on/use osquery personally I don't use it in a business environment