If it uses JDBC you should be able to access it the same way the Sailpoint VA accesses it, right?
Either through a database management tool like DBeaver or similiar, or through a script. Here is a sample one in Python (SSH-tunneling through the VA since I can’t access the DB, however the VA can).
import os
import jaydebeapi
from sshtunnel import SSHTunnelForwarder
ssh_host = '{VA_IP}'
ssh_port = {VA_SSH_PORT}
ssh_username = 'sailpoint'
ssh_password = '{VA_PASSWORD}'
remote_bind_address = '{DATABASE_IP}'
remote_bind_port = {DATABASE_PORT}
jdk_path = '{PATH TO JDK}' #Ignore if JAVA_HOME is populated
jdbc_url_template = "jdbc:mysql://localhost:{}/{DATABASE_NAME}"
driver_class = "{JDBC_DRIVER_CLASS}"
driver_path = "{PATH_TO_JDBC_DRIVER}"
db_username = '{DATABASE_USERNAME}'
db_password = '{DATABASE_PASSWORD}'
# The SQL query to execute
query = """SELECT CAST(NULL AS CHARACTER) as ACTION_ID, RTRIM(CHAR(MNKEY)) AS RESOURCE_ID, RTRIM(MNAPPL)||‘-’||RTRIM(MNFMT)||‘-’||RTRIM(MNOPTN) AS INTERNAL_KEY, ‘MENU OPTION’ AS ENTITLEMENT_TYPE, RTRIM(MNAPPL) AS ENTITY_TYPE
FROM table WHERE MNKEY IS NOT NULL
UNION
SELECT DISTINCT CASE WHEN FSPMFG = ‘N’ AND FSAUFG = ‘Y’ THEN ‘AUTHORITY’
WHEN FSPMFG = ‘Y’ AND FSAUFG = ‘N’ THEN ‘PRIMARY’
ELSE ‘ERROR’
END AS ACTION_ID,
FSSVCD AS RESOURCE_ID,
CAST(NULL AS CHARACTER) AS INTERNAL_KEY,
‘OFFICE CODE’ AS ENTITLEMENT_TYPE,
CAST(NULL AS CHARACTER) AS ENTITY_TYPE
FROM table WHERE FSSVCD IS NOT NULL
UNION
SELECT DISTINCT RTRIM(SAPGSC) AS ACTION_ID, RTRIM(SAPGCD) AS RESOURCE_ID, CAST(NULL AS CHARACTER) AS INTERNAL_KEY, ‘PRODUCT CODE’ AS ENTITLEMENT_TYPE, CAST(NULL AS CHARACTER) AS ENTITY_TYPE
FROM table WHERE SAPGCD IS NOT NULL;"""
def execute_query_through_ssh(ssh_host, ssh_port, ssh_username, ssh_password, remote_bind_address, remote_bind_port,
jdbc_url_template, driver_class, driver_path, db_username, db_password, query):
with SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_username,
ssh_password=ssh_password,
remote_bind_address=(remote_bind_address, remote_bind_port),
local_bind_address=('localhost', 0)
) as tunnel:
local_port = tunnel.local_bind_port
jdbc_url = jdbc_url_template.format(local_port)
# Ignore if JAVA_HOME is already setup properly.
os.environ['JAVA_HOME'] = jdk_path
os.environ['PATH'] = os.environ['JAVA_HOME'] + '\\bin;' + os.environ['PATH']
conn = jaydebeapi.connect(driver_class, jdbc_url, [db_username, db_password], driver_path)
curs = conn.cursor()
try:
curs.execute(query)
results = curs.fetchall()
column_names = [desc[0] for desc in curs.description]
return column_names, results
finally:
curs.close()
conn.close()
column_names, results = execute_query_through_ssh(ssh_host, ssh_port, ssh_username, ssh_password,
remote_bind_address, remote_bind_port, jdbc_url_template,
driver_class, driver_path, db_username, db_password, query)
print("Columns:", column_names)
for row in results:
print(row)
