Roadrecon SQL Queries

This post discusses an alternative way of analyzing data collected with Roadtools’ Roadrecon module and provides example queries that may assist during 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
• 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.

References

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

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

[3] https://sqlitebrowser.org/