Simon Kaufmann Absent Member.
Absent Member.
5789 views

Custom report: using the AUDIT_LOG table to find when a defect comment was last updated

I'm trying to create a custom excel report which identifies bugs which have not had their comments updated within the last X days.

I'm joining the BUG table with the AUDIT_LOG but I'm not sure how to identify the action id (?) or entity id (BUG_ID?) within the audit log table which signify that a comment was modified or added.

Any advice or suggested SQL?

cheers
Simon
0 Likes
5 Replies
Knowledge Partner
Knowledge Partner

Re: Custom report: using the AUDIT_LOG table to find when a defect comment was last updated

Here's the basic call:

'========================================
bugID = '12345'
"Select * From AUDIT_LOG Where AU_ENTITY_TYPE = 'BUG' AND AU_ACTION = 'UPDATE' " & " AND AU_ENTITY_ID = '" & bugID & "'"
'========================================

Whether you want to include the 'last X days' in the SQL or just interrogate the returned records.

You also need to figure out how you can tell it is a comments update, which you might be able to do from the AU_DESCRIPTION field.

Mark Smith.




0 Likes
Absent Member.. WW479296 Absent Member..
Absent Member..

Re: Custom report: using the AUDIT_LOG table to find when a defect comment was last updated

I use this query in the Excel Report Generator. Give it a try:

Select a.au_user
, a.au_time
, a.au_action
, b.ap_table_name
, b.ap_field_name
, b.ap_property_name
, b.ap_old_value
, b.ap_new_value
from AUDIT_LOG a
, AUDIT_PROPERTIES b
where a.au_action_id = b.ap_action_id
and a.AU_entity_type = 'BUG'
and a.au_time > '9/7/2010'
0 Likes
Simon Kaufmann Absent Member.
Absent Member.

Re: Custom report: using the AUDIT_LOG table to find when a defect comment was last updated

Thank you both.


I've worked up a query that seems to do what I what, but I'm having difficulty with the date comparison. Here's the query - any advice about how to handle the AUDIT_LOG.AU-TIME comparison? I need to truncate to just the date, dicarding the time component and then compare with either today's date or a configurable number of days back.

select

BG_BUG_ID as 'Defect ID',
BG_CLOSING_DATE as 'Closing Date',
BG_CLOSING_VERSION as 'Closed in Version',
BG_DESCRIPTION as 'Description',
BG_DETECTED_BY as 'Detected By',
BG_DETECTION_DATE as 'Detected on Date',
BG_DETECTION_VERSION as 'Detected in Version',
BG_DEV_COMMENTS as 'Comments',
BG_ESTIMATED_FIX_TIME as 'Estimated Fix Time',
BG_PLANNED_CLOSING_VER as 'Planned Closing Version',
BG_PRIORITY as 'Priority',
BG_PROJECT as 'Project',
BG_REPRODUCIBLE as 'Reproducible',
BG_RESPONSIBLE as 'Assigned To',
BG_SEVERITY as 'Severity',
BG_STATUS as 'Status',
BG_SUBJECT as 'Subject',
BG_SUMMARY as 'Summary',
BG_USER_01 as 'Application',
BG_USER_02 as 'Root Cause',
BG_USER_03 as 'Defect Type',
BG_USER_04 as 'Estimated Fix Date',
BG_USER_05 as 'External Reference',
BG_USER_06 as 'Severity Set By Business',
BG_USER_07 as 'Environment',
BG_USER_08 as 'Test Phase',
BG_USER_09 as 'Days Open',
BG_USER_10 as 'In Release Notes',
BG_USER_11 as 'Rejection\Deferral Reasons',
BG_USER_12 as 'Release - Reports Only',
BG_USER_13 as 'Fixed In Build #',
BG_USER_14 as 'Found In Build #',
BG_USER_15 as 'Not Yet In Use',
BG_VTS as 'Modified',
BG_DETECTED_IN_RCYC as 'Detected in Cycle',
BG_DETECTED_IN_REL as 'Detected in Release',
BG_TARGET_RCYC as 'Target Cycle',
BG_TARGET_REL as 'Target Release'

from

BUG

LEFT OUTER JOIN AUDIT_LOG ON BG_BUG_ID = AUDIT_LOG.AU_ENTITY_ID
AND
AUDIT_LOG.AU_ACTION = 'UPDATE'
AND
DATE(AUDIT_LOG.AU_TIME) < '16/12/2010'
LEFT OUTER JOIN AUDIT_PROPERTIES ON AP_FIELD_NAME = 'BG_DEV_COMMENTS'
where
(BG_STATUS not like 'Closed' AND BG_STATUS not like 'Deferred' AND BG_STATUS not like 'Rejected' )

order by BG_BUG_ID
0 Likes
Imran.Ansari_1 Absent Member.
Absent Member.

Re: Custom report: using the AUDIT_LOG table to find when a defect comment was last updated

Hi Simon, did you try using to_date oracle funtion on Au_Action_Time field?
0 Likes
sarsukanth Absent Member.
Absent Member.

Re: Custom report: using the AUDIT_LOG table to find when a defect comment was last updated

Hi All,

We are preparing a report in MS-Excel with metrics like "Next Day Retest Rate", "Defect Documentation" and etc...

Here we need the fields like when (Date and Time) the retest is started and ended and when (Date and Time) defect moved to "info-needed" state and when it ended.

So as we are preparing the Excel based Template, can you please provide us the VBA code to download the same.

And its very thankfull of your support as i am strugling with this issue from last 3 weeks.

please Help us...

Regards - Raghukanth

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.