Auditing Connections

Comments

5 comments

  • Official comment
    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;
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • 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
    Comment actions Permalink
  • Brian Junio

    Jeff,

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

    Thanks!

    0
    Comment actions Permalink

Please sign in to leave a comment.