Threat hunting with Osquery, Part 3 – Interaction with user accounts

  • Tuesday, Aug 3, 2021
Singel-post cover image

Part 3 – Interaction with user accounts

Frequent attacker strategical goal is to gain access to sensitive organization data and partial tactical goal to gain control over key infrastructure components. For this purpose attacker routinely attempt to gain control over user accounts. These can be different depending on the phase of the attack – the first one is usually the account in whose context the initial compromise took place. Then the attacker can attempt to escalate their privileges or to steal credentials of a highly privileged account. Obtained accounts are then used for reconnaissance and lateral movement across the infrastructure. If the attacker gains admin privileges, they can create new accounts for themselves. The Osquery queries listed below can help us with identifying user accounts with unusual activity.

You can read more about Osquery in our short blog post.


The starting point is a query that returns the inventory of user account present on all devices. Every device returns its own entries for local and domain accounts and that is why we can then process the summary data and answer questions, such as:

  • Are all user accounts in the list legitimate?

  • Is any account present – that is, was is logged in at least once and not purged – on more devices than it should or than it is usual?

  • Did any account access a device, for which it has no business or technical reason to do so?

  • On which devices is the suspicious account present?

  • What are the privileges of the suspicious account?

In case of suspicious findings it is good to remember about other information sources such as Windows Security logs from the endpoints and domain controllers, in case of a domain environment.

FROM users AS u
    JOIN groups AS g USING(gid); 

Last logon sessions

In case we identify an unusual account, we need to examine its activity more closely. The query listed below returns information about last logins of a particular user on all devices the query is run against. This can help in assessing the spread of attacker’s lateral movement. Pay close attention to those values in the logon_type column, which mark remote logons – Remote Interactive (using Terminal Services, RDP) or Network (e.g. mapping of a shared network drive). Information about the time of the last logon can help us put the activity on a timeline. In case we are querying all devices, we can see the order in which the logons took place.

    datetime(ls.logon_time, "unixepoch") AS logon_time,  
FROM logon_sessions AS ls  
    JOIN users AS u ON u.uuid = ls.logon_sid  
    JOIN groups AS g ON g.gid = u.gid 
WHERE ls.user = <user_name>; 

Logged in users

The following query returns a list of open sessions at the time of processing the query. This helps us to find out where the suspicious activity is taking place in the current moment. This information can help us assess the possible intentions of the attacker, which is important for choosing the response strategy. Removing the WHERE clause will return sessions of all users.

    datetime(liu.time, "unixepoch") AS login_time,
FROM logged_in_users AS liu
    JOIN users AS u ON liu.sid = u.uuid
    JOIN groups AS g ON u.gid = g.gid 
WHERE user = <user_name>; 

Shellbags artifacts

In the investigation phase, where we have identified one or more suspicious accounts, it is very useful in terms of corporate data protection and assessing the spread of the compromise, to find out which folders did the account access. If the user is logged in interactively using a GUI and he browses the filesystem using Windows Explorer, the visited paths are being logged in so-called shellbags. The query below collects those from devices along with MAC timestamps. However, if the attacker browsed the file system using other means than Windows Explorer, his activity will not be in these records.

    datetime(s.modified_time, "unixepoch") AS modified_time,
    datetime(s.created_time, "unixepoch") AS created_time,
    datetime(s.accessed_time, "unixepoch") AS accessed_time,
FROM shellbags AS s
    JOIN users AS u ON s.sid = u.uuid
WHERE u.username = <user_name>; 

Userassist artifacts

Userassist artifacts can serve as a supplement to the shellbags. They contain records about programs launched by the user exclusively using the GUI. For example, such records can be created if the attacker opens any found files directly on the victim device using an associated local GUI application, but not if he used a command-line tool to view the contents. We might be able to see the launch of the command prompt window itself if it was launched via GUI.

    datetime(ua.last_execution_time, "unixepoch") AS last_execution_time,
FROM userassist AS ua
    JOIN users AS u ON ua.sid = u.uuid
WHERE u.username = <user_name>;