Service Virtualization

  • Private Community

  • 1.  What is the SQL query to retrieve the user creation date from devtest database

    Posted Oct 29, 2018 03:44 AM

    We have integrated devtest with LDAP , and we have assigning users to the devtest role (SVP,SuperUser,AdminUser) , we would like to see the date of user assinged/created in devtest database.

     

    DevTest 10.3 version

    External Database (MSSQL Server 2008 R2 database)

     

     

    Thanks,

    Balamurugan.



  • 2.  Re: What is the SQL query to retrieve the user creation date from devtest database

    Broadcom Employee
    Posted Oct 30, 2018 02:43 AM

    Hi,

     

    Please check the tables ACL_USERS* tables and ACL_ROLE* tables.
    But I do not think these table include date or time type columns for creating/updating records.

     

    Thank you,



  • 3.  Re: What is the SQL query to retrieve the user creation date from devtest database

    Posted Nov 01, 2018 01:30 AM

    Hi tsuyu04,

     

    Yes  ACL_USERS and ACL_ROLE table doesn't have the date column it doesn't help for my scenario.

     

    Thanks,

    Balamurugan.



  • 4.  Re: What is the SQL query to retrieve the user creation date from devtest database

    Broadcom Employee
    Posted Nov 01, 2018 03:12 AM

    Hi,

     

    I mentioend "Please check the tables ACL_USERS* tables and ACL_ROLE* tables."
    This means to check the tables which names include ACL_USERS and ACL_ROLE  not only to check the ACL_USERS and ACL_ROLE tables.
    But I could not find any creation/modification date/time related columns in these tables.
    So I do not think your scenario can realized with current DevTest version.

    Then if you need this type of information to the table in DevTest database, please create an idea as a feature enhancement request in this community and write your requirement to the idea.

     

    Thank you,



  • 5.  Re: What is the SQL query to retrieve the user creation date from devtest database

    Posted Nov 01, 2018 05:27 AM

    Thanks for the details.But just wondering how enterprise dashboard able to retrieve the usage audit data for specific timeline with all the user details "Component By User" tab

     

     

    Regards,

    Balamurugan.



  • 6.  Re: What is the SQL query to retrieve the user creation date from devtest database
    Best Answer

    Broadcom Employee
    Posted Nov 02, 2018 01:47 AM

    That is a good remark.

     

    Usage audit data by User actually comes from the Enterprise Dashboard database.

     

    This database only has a log of access times by users, So, you can query and find out when a user first logged into DevTest but that's not the same as when he was created. Also updates of user data, or the deletion of a user would not be retrievable.

     

    Cheers,

    Danny



  • 7.  Re: What is the SQL query to retrieve the user creation date from devtest database

    Broadcom Employee
    Posted Nov 01, 2018 03:14 AM

    Would it be an option to go to the database level and activate auditing on these 2 tables? The audit trail table would have the timestamp when a record is created, or updated.

     

    Just a thought right now, not sure if it would work, also depending on your database type the auditing features will be different.

     

    Cheers,

    Danny



  • 8.  Re: What is the SQL query to retrieve the user creation date from devtest database

    Posted Nov 01, 2018 05:19 AM

    Thanks for your suggestion