Does anyone have a clever way of finding AzureAD accounts in the Local Administrator Group on Windows? I can find the AzureAD account by using the logged_in_users table but can't see it when I list all accounts in the users/groups/user_groups tables.

Hello JJ, interesting question. Let me take a look and see what I can find out. In the meantime can you tell me what version of Fleet you are running? And what's the osquery/fleetd version on the windows machines?

I've done something similar but for the current logged in user, can probably just add a where clause to check if the user is an AzureAD account `WHERE liu.user LIKE 'AzureAD\%`':

SELECT 
    liu.user AS current_user
FROM 
    logged_in_users liu
LEFT JOIN 
    users u 
    ON liu.user = u.username
LEFT JOIN 
    user_groups ug 
    ON u.uid = ug.uid
LEFT JOIN 
    groups g 
    ON ug.gid = g.gid
WHERE 
    liu.tty = 'Console' 
    AND (g.groupname IS NULL OR g.groupname = 'Administrators');

Thanks, Alex. Any idea why FleetDM says there's a syntax error when using this?

did you copy/paste the query as is?

Yes

maybe theres some unexpected white space? no errors in my fleet console:

Did some adjustment and got the query working but the problem is that AzureAD users just don't show up in the Administrators group for some reason despite being there. They're present in the users table but invisible in Administrators group.

Hello @JJ wanted to check in and see if you had any luck getting the results you were looking for? I was able to do a little bit of research, and found some different answers from it can take a little while to sync to the admin group, to attempting to do a compare with osquery results of the user group and a list gathered from azure AD. I can send some of those links if you think it'll help.