Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Project Audit Trail
Sometimes auditors are asking to produce a full audit trail of the project. This usually happens when they can't find anything bad in the process but still have hopes to leave at least some comments/remarks.
In order to resolve this, here's the complete project audit trail report:ESQL
SELECT HIST.CHANGESET_ID, HIST.CHANGE_DATE, HIST.USER_NAME AS CHANGED_BY, HIST.ARTIFACT_TYPE_NAME, HIST.ARTIFACT_ID, HIST.ARTIFACT_DESC AS ARTIFACT_NAME, HIST.CHANGETYPE_NAME AS CHANGE_TYPE, HIST.SIGNATURE_HASH FROM SpiraTestEntities.R_HistoryChangeSets AS HIST WHERE HIST.PROJECT_ID = ${ProjectId}
XSLT
<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <h2>Project Audit Trail</h2> <table class="DataGrid" style="width:100%"> <tr> <th>Change ID</th> <th>Change Date</th> <th>Changed By</th> <th>Artifact Type</th> <th>Artifact ID</th> <th>Artifact Name</th> <th>Change Type</th> <th>Signed</th> </tr> <xsl:for-each select="//*[CHANGESET_ID]"> <xsl:sort select="CHANGESET_ID" data-type="number" order="descending" /> <tr> <td><xsl:value-of select="CHANGESET_ID"/></td> <td><xsl:value-of select="CHANGE_DATE"/></td> <td><xsl:value-of select="CHANGED_BY"/></td> <td><xsl:value-of select="ARTIFACT_TYPE_NAME"/></td> <td><xsl:value-of select="ARTIFACT_ID"/></td> <td><xsl:value-of select="ARTIFACT_NAME"/></td> <td><xsl:value-of select="CHANGE_TYPE"/></td> <td> <xsl:choose> <xsl:when test="string-length(SIGNATURE_HASH) > 0">Signed</xsl:when> <xsl:otherwise>Not Signed</xsl:otherwise> </xsl:choose> </td> </tr> </xsl:for-each> </table> </xsl:template> </xsl:stylesheet>
As usual, please let me know if you find some bugs in the report. Your feedback is appreciated!
Hello Vlad,
Excellent report, it seems that you can cover usual pain points.
What I would change:1. Add a Date Filter - it is highly recommended to add a date filter so to avoid database timeout issues due to pulling tens of thousands of rows (for large and old products).2. Formatting the Date - currently, the CHANGE_DATE will output in Spira's raw, unformatted XML date structure (e.g., 2023-10-27T14:30:00Z). To make it look clean for the auditor, you can format it using Spira's built-in date helper, something like:
<td><xsl:value-of select="substring-before(CHANGE_DATE, 'T')"/> <xsl:text> </xsl:text> <xsl:value-of select="substring-before(substring-after(CHANGE_DATE, 'T'), '.')"/></td>
Please let us know if that helped.
Regards,Victoria -
Hi Victoria,
Good point!
The only thing is - I am not sure how to implement the date filtering. Can you help?
Thanks,
Vlad
Hello!
I think it can be a basic filter in WHERE statement:
WHERE HIST.PROJECT_ID = ${ProjectId} AND HIST.CHANGE_DATE >= '2026-01-01'
Or use DiffDays(start_date, end_date) to make it more dynamic.Here is a practical example of using DiffDays function: https://www.inflectra.com/Support/KnowledgeBase/KB890.aspx
And here you can see other options to format dates:https://www.inflectra.com/Support/KnowledgeBase/KB710.aspx
OK, meanwhile I've changed your suggested line to this:
<td><xsl:value-of select="substring-before(CHANGE_DATE, 'T')"/> <xsl:text disable-output-escaping="yes"> <![CDATA[ ]]></xsl:text> <xsl:value-of select="substring-after(CHANGE_DATE, 'T')"/></td>
It produces the date/time in the following format:2026-04-23 17:48:43
Question:
Say I want to select a date range. For this there should be a parameter on the Report page (perhaps in Custom section Filter?).
Something like this:Date from: DD-MM-YYYYDate to: DD-MM-YYYY
How do I introduce such filtering?
Also, in case when a user wants to output everything and not bothered selecting the dates, how can I ignore the fields and output everything?
Hello,
1. Filtering: as you may know, there is a limitation to display 10000 rows per query. So, you will need to use a pagination in this case. Here's the solution: KB792 - How to extract large amount of artifacts from Spira using Excel or OData
Regarding the filtration, you can try to use JavaScript to add a date picker on the report page, so to filter the output based on date parameters.
To view the audit trail for a specific timeframe, use the Start and End dates.To view the entire history, set the Start Date to e.g. 01-Jan-2000 and the End Date to e.g. 31-Dec-2099.
2. If this is something you may need on permanent basis I would think about API & Automation Integration and consider to build a custom integration using the Spira REST API instead, so to write a small script that automatically exports the audit logs you need.
And if you have any questions, please email or call us at +1 (202) 558-6885
