Hi Bentley and the forum,
I have switched audit tracking on an attribute and that works great in Director. However, I would now like to run a report in eQL to show the audit info, who changed the attribute, from what and to what etc. How can this be done please? Or can it be done via SQL? Either are OK for me. At the moment I can only see the basic details from eQL, saying who made a change and when, but it doesn't show the info in the sub-table, which is what I really need.
Many thanks in advance,
This can be done with SQL. You are correct that the "from-to" information is not available in an eQL report.
Here is an example (MSSQL syntax) query that extracts information from grouped virtual items, as in your screen shot and another object type (documents):
* * * *
SELECT vi.vitem_code, NULL as ver, vi.description, oat.description action, p.person_code,
COALESCE(apd.field_name, '') field,
COALESCE(ap.old_value, '') old_value,
COALESCE(ap.new_value, '') new_value
FROM object_audit_info oai WITH (NOLOCK)
INNER JOIN vitem_grp_members vgm WITH (NOLOCK) ON oai.object_id = vgm.gvitem_id
INNER JOIN vitems vi WITH (NOLOCK) ON vgm.vitem_id = vi.vitem_id
INNER JOIN object_action_types oat WITH (NOLOCK) ON oai.action_type = oat.action_type_id
INNER JOIN persons p WITH (NOLOCK) ON oai.person_id = p.person_id
LEFT JOIN audit_properties ap WITH (NOLOCK) ON oai.audit_entry_id = ap.audit_entry_id
LEFT JOIN audit_property_defs apd WITH (NOLOCK) ON ap.property_id = apd.property_id
WHERE oai.object_type = 123 -- AND oai.object_id = [vgm.gvitem_id]
-- . . . UNION more object types if desired for example, documents . . .
SELECT doc.prefix, doc.revision, doc.title, oat.description action, p.person_code,
INNER JOIN documents doc WITH (NOLOCK) ON oai.object_id = doc.document_id
WHERE oai.object_type = 3 -- AND oai.object_id = [doc.document_id]
* * * *
Grouped virtual items are not-like-the-others because they are linked to virtual items from which they get their code and description. The second query, into document audits is more typical.
I included (NOLOCK) in this case because the audit tables are hit frequently in normal processing and this will minimize the impact of reporting on the audit tables.
Eric Rajala | Professional Services Consultant
Bentley Systems, Incorporated
Answer Verified By: julie_beck
Thanks Eric for this. Gintautas just provided some code like this too. Cheers again :)