I use the following in an Automation policy that gets sent out every day. I am sure you can (if not I can help) change it to only show Retired. Right now it will do all statuses.
select --vri.Guid,
vri.name,
i.name 'Asset Type',
ins.name 'New Value',
del.name 'Old Value',
ins.ChangeDate,
a.UserId
from vResourceItem vri
inner join (
select ParentResourceGuid,
ChildResourceGuid,
ChangeType,
ChangeDate,
name
from ResourceAssociationHistoryDelta
inner join Item
on Guid = ChildResourceGuid
where ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
and ChangeType = 'I'
and datediff(HOUR, [ChangeDate], getdate()) < 24
) ins
on ins.ParentResourceGuid = vri.Guid
inner join (
select ParentResourceGuid,
ChildResourceGuid,
ChangeType,
ChangeDate,
name
from ResourceAssociationHistoryDelta
inner join Item
on Guid = ChildResourceGuid
where ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
and ChangeType = 'D'
and datediff(HOUR, [ChangeDate], getdate()) < 24
) del
on del.ParentResourceGuid = vri.Guid
inner join Item i
on i.Guid = vri.ResourceTypeGuid
left join Inv_Audit a
on (
a._ResourceGuid = ins.ParentResourceGuid
and a.AuditDate = ins.ChangeDate
)
where ins.ChangeDate = del.ChangeDate
order by ins.ChangeDate,
i.name