Auditing Connections

3 followers
0
Avatar

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?

Jeff Weidner

Official comment

  • Avatar
    Brian Junio

    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;
    0

5 comments

  • Avatar
    Brian Junio

     

    Jeff,

     

    Good morning!

     

    There is not a currently existing report to pull what you're looking for.  Please allow me some time and I'll see if I can return a solution for you.  

     

    Cheers,

     

    Brian

    0
  • Avatar
    Brian Junio

    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_fk

     

    The 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!

    0
  • Avatar
    Jeff Weidner

    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.

     

    0
  • Avatar
    Brian Junio

    Jeff,

    I'll take a peek at the query and see if I can spot the error.  

    Thanks!

    0
Please sign in to leave a comment.