Audit Reporting

How to use Audit Reports to show particular changes to records, such as which records were deleted last week, how many Opportunities have been won, or how long a record has been in its current state.

To report on audited records you can either create a report based on Audit Records or use a report based on another object type, such as Opportunities, and link to audit information. (To find audit information use the breadcrumb on More Columns.) For more information on Audit in general, click here.

Below are some examples of Audit Report that you might find useful. Reporting is not limited to these examples and you may have other reporting requirements.

Report to show which records were deleted last week

This is a simple list report, showing as many columns as you want, with criteria applied to narrow down which records are returned.

• Create a new report based on Audit Records and give it a name.
• Add columns for: Action; Record Type; Reference; User Agent and any other columns you might be interested in.
• Add a criteria to show only those records where the Action is delete.
• Add a second criteria to show only those records that were deleted last week. To do this, click Add criteria from Audit Records. Using the dropdown picklist select Date. Apply an Operator of ‘is in calendar range‘ and use the Calendar range picklist to select last week.

The resulting report will look something like the one in the screenshot below. Notice that there are multiple rows for the same request (eg, request 9261643, which is outlined). This is because the single action of deleting a record deleted multiple records.

Return to top

Report to show how many Opportunities have been won this quarter

This report calculates how many Opportunities have been won this quarter, summarised by salesperson. It assumes that the Assigned To field is used to capture the salesperson’s name and uses audit to identify the date that an Opportunity changed to (4) Won, regardless of what stage it was at previously. You probably use different Opportunity Stages to identify won deals, but the same theory can be applied.

• Create a report based on Opportunities and give it a name.
• Add columns for: Salesperson (Assigned to name), Opportunity reference and any other columns you may want.
• Create a Summary View with a grouping column grouped by Salesperson and a Summarised Column counting Opportunity reference.
• Add a criteria to limit the report to records where the field’s new value is (4) Won. (To do this, choose Audit records > Field Changes > New Value; add an Operator of ‘is’ and add (4) Won to the text field.)
• Add another criteria to limit the report to those records where the Opportunity was moved to Stage 4 in the current calendar quarter.
• Finally, summarise the report by Salesperson to give you one row per person, as shown below.

Return to top

Report to show how long a record has been in its current state

Imagine that you have a Case status value of In Progress and you want to build a list report showing how long the Cases that currently have an In Progress value have been in that state.

There are several aspects to this report; you need to limit the results to those records that currently have a status of In Progress. In addition, you also need to limit the results to those where the change to the record was to change the Status to In Progress (irrespective of what the Status was prior to this). Once you have this subset of data, you want to identify the most recent time that the Status changed to In Progress (because in theory it could move in and out of this Status multiple times), and then calculate the time difference between now and when it was most recently changed to In Progress.  Finally, you will need to summarise the report to make sure you’re only looking at one row per Case.

Begin by creating a report based on Case records.  Add columns for Case Reference, Audit records > Date along with any other columns you would want.

Criteria

Next, apply the criteria you want to use to narrow down the results. (Because auditing logs every change to a record, the resulting reports may contain a large volume of data. For this reason we recommend that you apply your criteria before adding any calculated columns to limit any delays in displaying the report.)

• Add a criteria to show records for which the Status is In Progress.
• Add a second criteria to identify Cases where the audited change shows that the new value in the Status field is In Progress.  (To do this, open the Criteria tab, click Add criteria, choose Audit records > Field Changes > New Value, apply an Operator of is and enter In Progress in the Text box.

Columns

• Within a Summary View, add a grouping column for the Case number (which is the Object Reference).
• Add a calculated column to show the most recent time that the Status field was changed to In Progress by using the following formula:

MAX( PARENT('Date'))

• Add a calculated column to display the difference in days between today (now) and the date that the record was changed by using the following formula:

DATEDIFF( CURDATE(), MAX( PARENT('Date')))

You don’t need to keep the column that calculates the most recent time the Status field was changed so you can delete it or hide it.

If you click Refresh preview your resulting report should look something like the one below (click to enlarge).

 Return to top