demonbhao
04/02/2021, 9:49 AMpuffycid
04/03/2021, 12:08 AMselect 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 trydemonbhao
04/07/2021, 2:36 AMpuffycid
05/18/2021, 12:11 PM