REVIEWED by Sophos
While thinking about other useful queries, for example checking where UAC is disabled on Windows computers:
select data 'EnableLUA' from registry where key='HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System' AND name='EnableLUA' AND data=0;
Maybe coupling that with where the logged on console user is a member of the local administrators group, as a starting point:
select count(*) 'Number of Console logged on admins' from logged_in_users as liu join users as u on u.username=liu.user join user_groups as ug on ug.uid=u.uid join groups as g on g.gid = ug.gid where liu.tty='Console' and g.groupname='Administrators';
It became clear that there is probably a wealth of online resources using osquery, just a quick search for EnableLUA and osquery returned:
https://github.com/osquery/osquery/blob/master/packs/windows-hardening.conf from there: https://github.com/osquery/osquery/blob/master/packs/windows-attacks.conf
I think the biggest challenge here will be to organize the useful queries and bookmarking sites related to osquery and threat hunting!