osquery Templates

UAC_disabled

-- platform: windows
-- description: Controls UAC. A setting of 0 indicates that UAC is disabled.
SELECT *
FROM registry
WHERE path='HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Policies\System\EnableLUA' AND data=0;

Windows Update history

-- platform: windows
-- description: List Windows Update history.
select title, datetime(date, 'unixepoch', 'localtime')
from windows_update_history;

Registry Run entries

-- platform: windows
-- description: List startup entries under Run keys.
select *
from registry
where key like
'HKEY_USERS\%\SOFTWARE\Microsoft\Windows\CurrentVersion\Run'
or key like 'HKEY_USERS\%\SOFTWARE\Microsoft\Windows\CurrentVersion\RunOnce'
or key like 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run%\%';

Services that start automatically

-- platform: windows
-- description: List installed services that start automatically
SELECT name,display_name,user_account,path
FROM services
WHERE start_type = 'AUTO_START'
      AND path NOT LIKE 'C:\Windows\system32\svchost.exe -k %';

Unusual Cron entries

-- Unexpected crontab entries
--
-- references:
--   * https://attack.mitre.org/techniques/T1053/003/ (Scheduled Task/Job: Cron)
--   * https://github.com/chainguard-dev/osquery-defense-kit/blob/main/detection/persistence/unexpected-cron-entries.sql
--
-- false positives:
--   * crontab entries added by the user
--
-- tags: persistent filesystem state
-- platform: posix
SELECT
  *
FROM
  crontab
WHERE
  command NOT LIKE 'root%run-parts%'
  AND command NOT LIKE '%freshclam%'
  AND command NOT LIKE '%clamscan%'
  AND command NOT LIKE '%e2scrub%'
  AND command NOT LIKE '%zfs-linux%'
  AND command NOT LIKE '%anacron start%'
  AND command NOT LIKE '%/usr/lib/php/sessionclean%'
  AND command NOT LIKE 'root command -v debian-sa1%'

Launched items not signed by Apple

-- description: Find launchd entries which purport to be by Apple, but point to binaries that are not signed by Apple.
--
-- references:
--   * https://attack.mitre.org/techniques/T1543/004/ (Create or Modify System Process: Launch Daemon)
--   * https://posts.specterops.io/hunting-for-bad-apples-part-1-22ef2b44c0aa
--   * https://github.com/chainguard-dev/osquery-defense-kit/blob/main/detection/persistence/fake-apple-launchd.sql
--
-- false positives:
--   * none have been observed
--
-- platform: darwin
-- tags: persistent launchd state
SELECT
  *
FROM
  launchd
  LEFT JOIN file ON launchd.path = file.path
  LEFT JOIN signature ON launchd.program_arguments = signature.path
WHERE
  launchd.name LIKE 'com.apple.%'
  -- Optimization, assumes SIP
  AND file.directory NOT IN (
    '/System/Library/LaunchAgents',
    '/System/Library/LaunchDaemons',
    '/Library/Apple/System/Library/LaunchDaemons',
    '/Library/Apple/System/Library/LaunchAgents'
  )
  AND launchd.run_at_load = 1
  AND signature.authority != 'Software Signing'

Processes running no binary on the disk

-- description: Find processes that are running whose binary has been deleted from the disk.
SELECT name, path, pid FROM processes WHERE on_disk = 0;

Scheduled Task with Temp path reference

-- description: List scheduled tasks where Temp directory is contained in Action path.
SELECT name, action FROM scheduled_tasks WHERE action LIKE '%\Temp\%';

List all local Users

-- description: List all local Users on the system.
select * from users where type = 'local';

List logged users

-- description: List logged users.
select * from logged_in_users;

List users with Administrative privileges

-- description: List all the users with Administrative privileges.
select users.uid,users.gid,users.username,users.directory from users JOIN user_groups ON users.uid=user_groups.uid where user_groups.gid=544;

Check the security status of the system

-- description: Check the security status of the system.
select * from windows_security_center;
select * from windows_security_products;

List processes running from CMD (with hash value)

-- description: List processes running from cmd (with a hash value)
select p.name,p.path,p.pid,p.parent,h.md5,pp.path as parentpath from processes p JOIN hash h on p.path=h.path JOIN processes pp ON p.parent=pp.pid where pp.path like '%cmd%';