Roadrecon SQL Queries

This post discusses an alternative way of analyzing data collected with Roadtools’ Roadrecon module and provides example queries that may assist in further analysis. Roadtools (short for Rogue Office 365 and Azure (active) Directory tools) is a famous framework to interact with Microsoft Entra ID. The framework was introduced in [1] and since then many cybersecurity professionals use it for various purposes. Roadrecon [2] is a tool from the framework that collects information from a provided Entra ID tenant leveraging the Azure Active Directory Graph API (graph.windows.net).

The Roadrecon output is stored by default in a database file named roadrecon.db on disk. The database can then be presented in a nice graphical interface on a browser using the command roadrecon gui. However, opening the database using a tool such as SQLiteBrowser [3] can further increase the efficiency of the analysis and allow an operator to perform custom and to some extent intelligent SQL queries to extract certain information.

The post is divided in the following sections:

• SQL Queries List
  • Return groups that contain a specific string in their description
  • Return enabled applications that do not require role assignment
  • Return users that are members of a specific group
  • Return the groups a specific device is member of
  • Return groups linked to SharePoint resources available to authenticated users
  • Return groups assignable to roles
  • Return a list of users that have an Entra role
• References

SQL Queries List

This section provides sample SQL queries that can be performed on a Roadrecon database.

Return groups that contain a specific string in their description

SELECT displayName, objectId FROM Groups WHERE LOWER(description) LIKE LOWER(‘%sample%’);

This query returns the names and the object ID of user groups that contain the case-insensitive string sample in their description. Same logic can be applied to fetch results from other tables.

Return enabled applications that do not require role assignment

SELECT displayName, replyUrls FROM ServicePrincipals WHERE accountEnabled = 1 AND servicePrincipalType = ‘Application’ AND appRoleAssignmentRequired = 0 AND LOWER(replyUrls) LIKE LOWER(‘%https://%’);

This query returns the name and the redirect URL(s) of active application service principals that do not require role assignment. It is particularly helpful in identifying SSO applications tenant users have access to.

Return users that are members of a specific group

SELECT u.displayName FROM Users u INNER JOIN lnk_group_member_user gu ON u.objectId = gu.”User” WHERE gu.”Group” = ‘groupID’ ORDER BY u.displayName ASC;

List users that are members of the provided group.

Return the groups a specific device is member of

SELECT d.displayName, g.displayName, g.dirSyncEnabled, g.description, g.isAssignableToRole from lnk_group_member_device gd inner join Devices d on d.objectId = gd.”Devices” on d.objectId = gd.”Device” inner join Groups g on g.objectId = gd.”Group” where d.displayName like ‘DEVICE_NAME’ order by g.displayName asc;

List the groups an Entra device is member of.

Return groups linked to SharePoint resources available to authenticated users

SELECT displayName, description, securityEnabled, isPublic from Groups where (securityEnabled = 0 and isPublic = 1 and sharepointResources not like ‘[]’) order by displayName asc;

List public groups that are not security-enabled and are linked to SharePoint resources.

Return groups assignable to roles

SELECT displayName, description from Groups where isAssignableToRole = 1 order by displayName asc;

List all groups (and their descriptions) that can be assigned to Entra roles.

Return a list of users that have an Entra role

SELECT u.displayName as ‘UserDisplayName’, r.displayName as ‘RoleDisplayName’, u.objectId as ‘UserObjectID’, r.objectId as ‘DirectoryRoleObjectId’ from lnk_role_member_user rg left join Users u on u.objectId = rg.”User” left join DirectoryRoles r on rg.”DirectoryRole” = r.objectId where u.accountEnabled = 1 and rg.DirectoryRole is not null order by u.DisplayName asc;

List users along with their Entra role.

References

[1] https://dirkjanm.io/introducing-roadtools-and-roadrecon-azure-ad-exploration-framework/

[2] https://github.com/dirkjanm/ROADtools

[3] https://sqlitebrowser.org/