Audit tracking attribute - get detail of the changes via eQL or SQL

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,

Julie

Parents
  • 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 . . .

    UNION ALL

    SELECT doc.prefix, doc.revision, doc.title, 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 documents doc WITH (NOLOCK) ON oai.object_id = doc.document_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 = 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 | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test

    Answer Verified By: julie_beck 

Reply
  • 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 . . .

    UNION ALL

    SELECT doc.prefix, doc.revision, doc.title, 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 documents doc WITH (NOLOCK) ON oai.object_id = doc.document_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 = 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 | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test

    Answer Verified By: julie_beck 

Children