Auditing Connections
I am trying to create a workbook that brings together information about all of the connections that have been created to reduce redundancy. (e.g. people who have created a new connection but didn't use a pre-existing one or whom have created one without setting the "ask for credentials" option enabled.
I have reviewed through dap_file,job_configuration, job_execution, data_store, db_driver as well as the HUM DB Data Combiner job.
Would prefer not to have to parse through all the connection JSON items outside of an existing 'index' service.
First, obtaining information about the Connection job.
1. DATA STORE Object ID (to provide a URL link like http://blah.datameer.blah:8080/connection/139). Attempted to link the DAP_FILE.id of DATA STORE objects to JOB_CONFIGURATION but no information is there.
2. DATASTORE USAGE. [ex: IMPORT/EXPORT]
3. DATASTORE URL [ex: jdbc:mysql://blah.datameer.blah:3306/dap...] This will let me strip off the database name
4. DIRECTORY. While dap.file_folder_fk exists, I can't find a similar table with the primary key.
5. DATASTORE Username. To determine if credentials have been embedded, and if so - who?
Second, I would like to obtaining how much data has been processed by the connection in question. Assuming that would have to poll through hte last Job_execution, dap_file, data_store to get that full count. Perhaps there already exists a report?
-
Official comment
Jeff,
Good morning!
After some extended effort, I believe we have the proper query for your request:
SELECT permission.owner, permission.shared, data_store_configuration_property.property_key, data_store_configuration_property.property_value, data_store_configuration_property.secure, data_store_configuration_property.configuration_fk, data_store.type_id, dap_file.name, dap_file.creation_date, dap_file.description FROM data_store_configuration_property INNER JOIN data_store ON data_store_configuration_property.configuration_fk = data_store.id INNER JOIN dap_file ON data_store.dap_file__id = dap_file.id INNER JOIN permission ON dap_file.permission_fk = permission.id;Comment actions -
Jeff,
Good afternoon!
I have touched on your bullet points and created the following query:
SELECT
dap_file.id,
dap_file.creation_date,
dap_file.name,
permission.owner,
data_store_configuration_property.property_key,
data_store_configuration_property.property_value
FROM
data_store
INNER JOIN
dap_file ON data_store.dap_file__id = dap_file.id
INNER JOIN
permission ON dap_file.permission_fk = permission.id
INNER JOIN
data_store_configuration_property ON dap_file.id = data_store_configuration_property.configuration_fkThe above should return the following Connection details; Connection ID, Creation Date, Name, Owner, and all Key:Value pair information about the Connection.
From this query, you'll need to parse out the desired information from the "property_key" values, such as "user_name". This will provide the indication if the Connection has been configured to retain authentication credentials or if it will be asking every time.
To address your secondary request concerning the volume through each Connection, this won't be possible. The Connection itself does not pull data, it is simply the door to said data.
Cheers!
-
Thanks Brian,
Not sure if the config property join is working, perhaps through joining with job_configuration? Perhaps something is not updating in MySql?
I have had a look at it by creating new data links to the dataset within DataMeer and seems not everything may be there for all data store entries [ftp, sftp, jdbc, Custom].
Basically out of 134 DATA_STORE entries from dap_file I had 7 matches but the configuration details didn't make sense (e.g. a MS SQL connection where a Teradata connection should be) - even after reducing the join.
data_store_configuration_property ON dap_file.id
= data_store_configuration_property.configuration_fk
I have configuration_fk entries from 1 to 178 rather than 1 to 24926 which are shown in dap_file which are data stores.
Please sign in to leave a comment.
Comments
5 comments