Ctrl+K

ROADTools GPT

ROADTools GPT#

Reference: https://python.langchain.com/en/latest/modules/agents/toolkits/examples/sql_database.html

Import Langchain Modules#

from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
from langchain.llms.openai import OpenAI
from langchain.agents import AgentExecutor

Initialize Agent Executor#

import os
from dotenv import load_dotenv
import openai

# Get your key: https://platform.openai.com/account/api-keys
load_dotenv()
openai.api_key = os.getenv("OPENAI_API_KEY")

from langchain import OpenAI
llm = OpenAI(temperature=0)

db = SQLDatabase.from_uri("sqlite:///db/roadrecon.db")

toolkit = SQLDatabaseToolkit(db=db,llm=llm)

agent_executor = create_sql_agent(
    llm=OpenAI(temperature=0),
    toolkit=toolkit,
    verbose=True
)

agent_executor.run("List all tables")

> Entering new AgentExecutor chain...
Action: list_tables_sql_db
Action Input: ""
Observation: lnk_au_member_user, lnk_group_owner_user, Policys, lnk_au_member_group, ExtensionPropertys, Contacts, AdministrativeUnits, EligibleRoleAssignments, Users, lnk_application_owner_serviceprincipal, lnk_device_owner, RoleDefinitions, lnk_application_owner_user, lnk_group_member_group, AuthorizationPolicys, RoleAssignments, lnk_group_owner_serviceprincipal, lnk_group_member_serviceprincipal, ApplicationRefs, Groups, lnk_group_member_user, DirectoryRoles, ServicePrincipals, lnk_group_member_contact, AppRoleAssignments, Devices, lnk_serviceprincipal_owner_serviceprincipal, TenantDetails, lnk_group_member_device, lnk_au_member_device, DirectorySettings, lnk_role_member_user, lnk_serviceprincipal_owner_user, Applications, lnk_role_member_serviceprincipal, OAuth2PermissionGrants, lnk_role_member_group
Thought: I now know the tables in the database
Final Answer: lnk_au_member_user, lnk_group_owner_user, Policys, lnk_au_member_group, ExtensionPropertys, Contacts, AdministrativeUnits, EligibleRoleAssignments, Users, lnk_application_owner_serviceprincipal, lnk_device_owner, RoleDefinitions, lnk_application_owner_user, lnk_group_member_group, AuthorizationPolicys, RoleAssignments, lnk_group_owner_serviceprincipal, lnk_group_member_serviceprincipal, ApplicationRefs, Groups, lnk_group_member_user, DirectoryRoles, ServicePrincipals, lnk_group_member_contact, AppRoleAssignments, Devices, lnk_serviceprincipal_owner_serviceprincipal, TenantDetails, lnk_group_member_device, lnk_au_member_device, DirectorySettings, lnk_role_member_user, lnk_serviceprincipal_owner_user, Applications

> Finished chain.

'lnk_au_member_user, lnk_group_owner_user, Policys, lnk_au_member_group, ExtensionPropertys, Contacts, AdministrativeUnits, EligibleRoleAssignments, Users, lnk_application_owner_serviceprincipal, lnk_device_owner, RoleDefinitions, lnk_application_owner_user, lnk_group_member_group, AuthorizationPolicys, RoleAssignments, lnk_group_owner_serviceprincipal, lnk_group_member_serviceprincipal, ApplicationRefs, Groups, lnk_group_member_user, DirectoryRoles, ServicePrincipals, lnk_group_member_contact, AppRoleAssignments, Devices, lnk_serviceprincipal_owner_serviceprincipal, TenantDetails, lnk_group_member_device, lnk_au_member_device, DirectorySettings, lnk_role_member_user, lnk_serviceprincipal_owner_user, Applications'

agent_executor.run("What Service Principals have AD Roles?")

> Entering new AgentExecutor chain...
Action: list_tables_sql_db
Action Input: ""
Observation: lnk_au_member_user, lnk_group_owner_user, Policys, lnk_au_member_group, ExtensionPropertys, Contacts, AdministrativeUnits, EligibleRoleAssignments, Users, lnk_application_owner_serviceprincipal, lnk_device_owner, RoleDefinitions, lnk_application_owner_user, lnk_group_member_group, AuthorizationPolicys, RoleAssignments, lnk_group_owner_serviceprincipal, lnk_group_member_serviceprincipal, ApplicationRefs, Groups, lnk_group_member_user, DirectoryRoles, ServicePrincipals, lnk_group_member_contact, AppRoleAssignments, Devices, lnk_serviceprincipal_owner_serviceprincipal, TenantDetails, lnk_group_member_device, lnk_au_member_device, DirectorySettings, lnk_role_member_user, lnk_serviceprincipal_owner_user, Applications, lnk_role_member_serviceprincipal, OAuth2PermissionGrants, lnk_role_member_group
Thought: I should look at the schema of the tables that might be relevant.
Action: schema_sql_db
Action Input: "ServicePrincipals, RoleAssignments"
Observation: 
CREATE TABLE "ServicePrincipals" (
	"objectType" TEXT, 
	"objectId" TEXT NOT NULL, 
	"deletionTimestamp" DATETIME, 
	"accountEnabled" BOOLEAN, 
	"addIns" TEXT, 
	"alternativeNames" TEXT, 
	"appBranding" TEXT, 
	"appCategory" TEXT, 
	"appData" TEXT, 
	"appDisplayName" TEXT, 
	"appId" TEXT, 
	"applicationTemplateId" TEXT, 
	"appMetadata" TEXT, 
	"appOwnerTenantId" TEXT, 
	"appRoleAssignmentRequired" BOOLEAN, 
	"appRoles" TEXT, 
	"authenticationPolicy" TEXT, 
	"disabledByMicrosoftStatus" TEXT, 
	"displayName" TEXT, 
	"errorUrl" TEXT, 
	homepage TEXT, 
	"informationalUrls" TEXT, 
	"keyCredentials" TEXT, 
	"logoutUrl" TEXT, 
	"managedIdentityResourceId" TEXT, 
	"microsoftFirstParty" BOOLEAN, 
	"notificationEmailAddresses" TEXT, 
	"oauth2Permissions" TEXT, 
	"passwordCredentials" TEXT, 
	"preferredSingleSignOnMode" TEXT, 
	"preferredTokenSigningKeyEndDateTime" DATETIME, 
	"preferredTokenSigningKeyThumbprint" TEXT, 
	"publisherName" TEXT, 
	"replyUrls" TEXT, 
	"samlMetadataUrl" TEXT, 
	"samlSingleSignOnSettings" TEXT, 
	"servicePrincipalNames" TEXT, 
	tags TEXT, 
	"tokenEncryptionKeyId" TEXT, 
	"servicePrincipalType" TEXT, 
	"useCustomTokenSigningKey" BOOLEAN, 
	"verifiedPublisher" TEXT, 
	PRIMARY KEY ("objectId")
)

/*
3 rows from ServicePrincipals table:
objectType	objectId	deletionTimestamp	accountEnabled	addIns	alternativeNames	appBranding	appCategory	appData	appDisplayName	appId	applicationTemplateId	appMetadata	appOwnerTenantId	appRoleAssignmentRequired	appRoles	authenticationPolicy	disabledByMicrosoftStatus	displayName	errorUrl	homepage	informationalUrls	keyCredentials	logoutUrl	managedIdentityResourceId	microsoftFirstParty	notificationEmailAddresses	oauth2Permissions	passwordCredentials	preferredSingleSignOnMode	preferredTokenSigningKeyEndDateTime	preferredTokenSigningKeyThumbprint	publisherName	replyUrls	samlMetadataUrl	samlSingleSignOnSettings	servicePrincipalNames	tags	tokenEncryptionKeyId	servicePrincipalType	useCustomTokenSigningKey	verifiedPublisher
ServicePrincipal	0216d9c0-cc2e-4329-a8d0-cd2ab31fb2a6	None	True	[]	[]	None	None	None	M365 License Manager	aeb86249-8ea3-49e2-900b-54cc8e308f85	None	None	f8cdef31-a31e-4b4a-93e4-5f571e91255a	False	[{"allowedMemberTypes": ["Application"], "description": "Enables the client to read all Licensed Pro	None	None	M365 License Manager	None	None	{"termsOfService": null, "support": null, "privacy": null, "marketing": null}	[]	None	None	True	[]	[{"adminConsentDescription": "Access the M365 License Manager service as the signed-in user", "admin	[]	None	None	None	Microsoft Services	["https://admin.microsoft.com", "https://licensing.m365.microsoft.com"]	None	None	["aeb86249-8ea3-49e2-900b-54cc8e308f85"]	[]	None	Application	None	{"displayName": null, "verifiedPublisherId": null, "addedDateTime": null}
ServicePrincipal	0286820a-b6d0-46e4-b55f-f27a29c33272	None	True	[]	[]	None	None	None	Azure Multi-Factor Auth Client	981f26a1-7f43-403b-a875-f8b09b8cd720	None	None	f8cdef31-a31e-4b4a-93e4-5f571e91255a	False	[]	None	None	Azure Multi-Factor Auth Client	None	None	{"termsOfService": null, "support": null, "privacy": null, "marketing": null}	[]	None	None	True	[]	[]	[]	None	None	None	Microsoft Services	["https://adnotifications.windowsazure.com/StrongAuthenticationService.svc/Client"]	None	None	["981f26a1-7f43-403b-a875-f8b09b8cd720"]	["disableLegacyUserImpersonationResource", "disableLegacyUserImpersonationClient"]	None	Application	None	{"displayName": null, "verifiedPublisherId": null, "addedDateTime": null}
ServicePrincipal	089d8835-e775-4778-9190-9b8728f3abc4	None	True	[]	[]	None	None	None	Azure MFA StrongAuthenticationService	b5a60e17-278b-4c92-a4e2-b9262e66bb28	None	None	f8cdef31-a31e-4b4a-93e4-5f571e91255a	False	[]	None	None	Azure MFA StrongAuthenticationService	None	None	{"termsOfService": null, "support": null, "privacy": null, "marketing": null}	[]	None	None	True	[]	[]	[]	None	None	None	Microsoft Services	[]	None	None	["b5a60e17-278b-4c92-a4e2-b9262e66bb28"]	[]	None	Application	None	{"displayName": null, "verifiedPublisherId": null, "addedDateTime": null}
*/


CREATE TABLE "RoleAssignments" (
	id TEXT NOT NULL, 
	"principalId" TEXT, 
	"resourceScopes" TEXT, 
	"roleDefinitionId" TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY("roleDefinitionId") REFERENCES "RoleDefinitions" ("objectId")
)

/*
3 rows from RoleAssignments table:
id	principalId	resourceScopes	roleDefinitionId
lAPpYvVpN0KRkAEhdxReEK4IFEkR7WBCsLrhSfclTPE-1	491408ae-ed11-4260-b0ba-e149f7254cf1	["/"]	62e90394-69f5-4237-9190-012177145e10
lAPpYvVpN0KRkAEhdxReEImP-wOG0dRLuLevqQc4AUo-1	03fb8f89-d186-4bd4-b8b7-afa90738014a	["/"]	62e90394-69f5-4237-9190-012177145e10
3ywjKSOT_UKt4h0JevPk3oiAl12BYNFJjgeHXkPpRG4-1	5d978088-6081-49d1-8e07-875e43e9446e	["/"]	29232cdf-9323-42fd-ade2-1d097af3e4de
*/
Thought:

Retrying langchain.llms.openai.completion_with_retry.<locals>._completion_with_retry in 4.0 seconds as it raised RateLimitError: The server had an error while processing your request. Sorry about that!.

 I should query the RoleAssignments table to get the Service Principals with AD Roles.
Action: query_sql_db
Action Input: SELECT ServicePrincipals.displayName, RoleDefinitions.displayName FROM ServicePrincipals INNER JOIN RoleAssignments ON ServicePrincipals.objectId = RoleAssignments.principalId INNER JOIN RoleDefinitions ON RoleAssignments.roleDefinitionId = RoleDefinitions.objectId LIMIT 10
Observation: [('DataSwitch', 'Exchange Administrator'), ('CloudSentry', 'Application Developer'), ('CloudMapper', 'Intune Administrator'), ('CloudFlow', 'Security Operator'), ('CloudPulse', 'Network Administrator'), ('CodePipeline', 'Attack Simulation Administrator')]
Thought: I now know the final answer
Final Answer: The Service Principals with AD Roles are DataSwitch, CloudSentry, CloudMapper, CloudFlow, CloudPulse, and CodePipeline.

> Finished chain.

'The Service Principals with AD Roles are DataSwitch, CloudSentry, CloudMapper, CloudFlow, CloudPulse, and CodePipeline.'
Contents