SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 4 of 4
  1. #1
    Apprentice
    Join Date
    Jan 2014
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Requirements History SQL

    Hi,

    Does anyone know of an SQL query that extracts to Excel the history of changes to the QC Requirement Type field, including the old Requirement Type values, new Requirement Type values, the date(s) the changes were made and who changed them?

    Many thanks,

    Colm

  2. #2
    Apprentice
    Join Date
    Jul 2010
    Location
    SF Bay Area, CA, US
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    This query works on both MS SQL Server and Oracle databases:

    SELECT RQ_REQ_NAME, AU_USER, AU_TIME, AP_PROPERTY_NAME, AU_ACTION, AP_OLD_VALUE, AP_NEW_VALUE
    FROM AUDIT_LOG
    join AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID
    left join REQ on RQ_REQ_ID = AU_ENTITY_ID
    WHERE AU_ACTION = 'UPDATE'
    and AU_ENTITY_TYPE = 'REQ'
    and AP_FIELD_NAME = 'RQ_TYPE_ID'
    ORDER BY 1, 3
    I use HP ALM QC reports from www.rbreporting.com

  3. #3
    Apprentice
    Join Date
    Jan 2014
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    Quote Originally Posted by qarom View Post
    This query works on both MS SQL Server and Oracle databases:

    SELECT RQ_REQ_NAME, AU_USER, AU_TIME, AP_PROPERTY_NAME, AU_ACTION, AP_OLD_VALUE, AP_NEW_VALUE
    FROM AUDIT_LOG
    join AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID
    left join REQ on RQ_REQ_ID = AU_ENTITY_ID
    WHERE AU_ACTION = 'UPDATE'
    and AU_ENTITY_TYPE = 'REQ'
    and AP_FIELD_NAME = 'RQ_TYPE_ID'
    ORDER BY 1, 3

    Thanks for that. Can this information be got back based on an accidental re-naming of a Requirement Type in the Tools >Customize >Requirements Types area, which changes all of the historical requirements from the old type to the new re-named type? The above query covers any changes to the requirement type within the Requirements module whenever a requirement type value is changed but not when a requirement type is re-named in the Tools >Customize >Requirements Types area.

  4. #4
    Advanced Member
    Join Date
    Aug 2004
    Location
    Wellington, New Zealand
    Posts
    834
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0
    Hi IT38,

    The audit logs in QCE/ALM only contain information for fields that have History enabled (Settings>Customise>Project Entities...)
    If you have history enabled for RQ_REQ_NAME, then changes to a Requirement name will be logged and you'll be able to query the Audit tables to find the old value.

    The query above is only going to show you changes to REQ_TYPE because of this line:

    and AP_FIELD_NAME = 'RQ_TYPE_ID'
    If you remove it, then you'll see all changes logged for Requirements.
    Regards,

    Alex
    ... just another Tester ...

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.40 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 9.68%
vBulletin Optimisation provided by vB Optimise v2.7.1 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.3.0 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 04:24 PM.

Copyright BetaSoft Inc.