====== osquery queries======
[[osquery:osquery|{{ :osquery:icon.jpg?120|}}]]

===== context=====
dit document geeft een overzicht van enkele nuttige queries

===== gebruikers=====
==== gebruikers en hun groepen ====

<code>SELECT usr.username, usr.uid, grp.groupname,grp.gid from users AS usr
LEFT JOIN user_groups AS usrgrp ON usr.uid = usrgrp.uid 
JOIN groups AS grp ON grp.gid = usrgrp.gid
ORDER BY usr.username;
</code>

===== processen=====
==== processen, pid, path en parent ====

<code>SELECT datetime(p.start_time,'unixepoch','localtime') AS 'START TIME', 
p.pid AS PID,p.name AS PROCESSNAME, p.path AS PATH, 
p.parent AS PPID, p2.name AS 'PROCESSNAME PARENT' FROM processes p 
JOIN processes p2 ON p.parent = p2.pid 
ORDER BY p.start_time;</code>
==== processen met hash ====
<code>SELECT p.pid,p.name,u.username,p.path,h.sha1 FROM processes p 
JOIN hash h ON p.path = h.path 
GROUP BY h.sha1 
ORDER BY p.pid ASC;</code>
<bootnote>GROUP BY zal alle processen met eenzelfde hash samenvoegen. \\
Je ziet dus niet alle processen, enkel die met unieke hash</bootnote>
==== processen met hash en user ====
<code>SELECT p.pid,p.name,u.username,p.path,h.sha1 FROM processes p 
JOIN hash h ON p.path = h.path 
JOIN users u ON u.uid = p.uid 
GROUP BY h.sha1 
ORDER BY p.pid ASC;</code>

==== boot time ====
<code>SELECT p.pid AS PID, p.parent AS PPID, p.name AS PROCESSNAME, 
p.path AS PATH, datetime (start_time, 'unixepoch','localtime') AS STARTTIME FROM processes p 
ORDER BY p.start_time ASC LIMIT 1;</code>
==== laatste 10 actieve processen ====
<code>SELECT p.pid AS PID, p.parent AS PPID, p.name AS PROCESSNAME, 
p.path AS PATH, datetime (start_time, 'unixepoch','localtime') AS STARTTIME FROM processes p 
ORDER BY p.start_time DESC LIMIT 10;</code>


==== zoeken op procesnaam (firefox, in dit geval)====
<code>SELECT proc.pid, proc.name, usr.username, proc.path, proc.parent, proc2.name FROM processes AS proc 
JOIN processes AS proc2 ON proc.parent = proc2.pid 
JOIN users AS usr ON proc.uid = usr.uid 
WHERE proc.name like '%firefox%';
ORDER BY proc.pid;</code>

===== hashing =====
<code>SELECT path, sha1 from hash 
WHERE path = 'padNaarBestand';</code>
voorbeeld: <code>SELECT path, sha1 from hash where path IN ('C:\windows\system32\ping.exe', 'C:\windows\system32\cmd.exe');</code>

===== file =====
==== timestamps ====

<code>SELECT path, datetime(atime, 'unixepoch','localtime') AS 'Acces time', 
datetime(mtime, 'unixepoch','localtime') AS 'Modified time',
datetime(btime, 'unixepoch','localtime') AS 'Created time' from file 
WHERE path = 'padNaarBestand';</code>
voorbeeld: <code>SELECT path, datetime(atime, 'unixepoch','localtime') AS 'Acces time', 
datetime(mtime, 'unixepoch','localtime') AS 'Modified time',
datetime(btime, 'unixepoch','localtime') AS 'Created time' from file 
WHERE path is 'C:\Windows\System32\winevt\Logs\system.evtx';</code>
==== alle bestandseigenschappen ====
<code>.mode line
SELECT * from file 
WHERE path = 'padNaarBestand';</code>
voorbeeld: <code>SELECT * from file 
WHERE path = 'C:\windows\system32\cmd.exe';</code>

===== autorun =====
==== met target path (in geval van lnk-files) ====

<code>SELECT su.name, su.path, su.args, su.username, sc.local_path, hash.md5 from startup_items AS su
JOIN hash AS hash ON hash.path = su.path 
LEFT JOIN shortcut_files AS sc ON sc.path = su.path
WHERE su.path not like '%desktop.ini%';
</code>

===== timestamp =====
<code> datetime(start_time, 'unixepoch','localtime') AS start_time </code>
===== netwerk verbindingen=====
==== met sha1 van process ====
<code>SELECT p.pid, p.name, p.path, h.sha1, os.local_address, os.local_port, os.remote_address, os.remote_port, datetime(start_time,'unixepoch','localtime') FROM processes p 
JOIN process_open_sockets os ON p.pid = os.pid 
LEFT JOIN hash h ON h.path = p.path WHERE os.state = 'ESTABLISHED' AND os.remote_address NOT LIKE '127.0.0.1' 
GROUP BY h.sha1;</code>
===== meer info =====

{{tag>osquery}}