Browse Live Discover and Response Queries by Category
Under Review

Query Local Administrators / Endpoint Query / DataLake Query

Hello Community!

I'm looking for a solution to make use of the DateLake data (I'm still XDR / LiveDiscover newbie). I would like to query all local administrators of computers that do not have the default names.

For this I already have a small query, which works fine as an endpoint query. Is there a technical possibility to run this query also in the DataLake?

My query:

SELECT username, groupname, type, u.UID, g.GID, Description, comment
FROM users u
JOIN user_groups ug ON ug.UID = u.UID
JOIN groups g ON g.GID = ug.GID
WHERE g.group_sid = "S-1-5-32-544"
and username != "Administrator"
and username != "testadmin"

Thanks for your help!

Florian

Florian Garrecht

  • I was able to locate a good sample query from the following Sophos schema page by navigating to the "Data Lake" section and looking into the "Table name: user_accounts"

    SELECT
uid, gid, username, description, directory, shell, type, uuid
FROM
xdr_data
WHERE
query_name = 'user_accounts'
limit 10

    Let me know if this works for you.

Unfiltered HTML