How to filter defects on ALM that were assigned to me in the past?

Question

I have defects on ALM that were assigned to be in the past, but then I've re-assigned them to other users.

Is there anyway, I can create a filter on ALM that tells me which defects, were assigned to me by checking the History column on the defect?

I would need an output table with all the defects that has been pass through me.

I need this for statistic.

Answer 1

You need to use SQL to join the BUG table with the audit log table and audit properties table. You can create an Excel under Analysis to execute the SQL as follows:

SELECT
    BUG.BG_PRIORITY as 'Prio',
    BUG.BG_SEVERITY as 'Sev',
    BUG.BG_BUG_ID as 'Defect ID',
    BUG.BG_DETECTION_DATE as 'Detected',
    BUG.BG_STATUS as 'Status',
    BUG.BG_SUMMARY as 'Summary',
    BUG.BG_DETECTED_BY as 'Detected By',
    BUG.BG_RESPONSIBLE as 'Responsible',
    AUDIT_LOG.AU_TIME as 'When',
    AUDIT_PROPERTIES.AP_PROPERTY_NAME,
    AUDIT_PROPERTIES.AP_NEW_VALUE
FROM BUG
LEFT JOIN AUDIT_LOG 
    ON BUG.BG_BUG_ID = AUDIT_LOG.AU_ENTITY_ID
INNER JOIN AUDIT_PROPERTIES 
    ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
WHERE (AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' OR AUDIT_LOG.AU_ENTITY_TYPE IS NULL)
    AND (AUDIT_LOG.AU_ACTION='UPDATE' OR AUDIT_LOG.AU_ACTION IS NULL)
    AND (AUDIT_PROPERTIES.AP_PROPERTY_NAME = 'Assigned To'
    AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'hughk')
ORDER BY BUG.BG_PRIORITY, BUG.BG_SEVERITY, BUG.BG_BUG_ID ASC;

Note that the user is on on the last line but one is specified as hughk.

The result will be a list of all defects and where they are now with when they were assigned to 'hughk'.