Forum Discussion

NicoleBruno's avatar
NicoleBruno
Valued Contributor
2 days ago

Audit tracking: How do you track changes in OS and link with approvals?

Hello! 

What's your company's way of tracking OS changes and pairing them with approvals?

Do you do it manually with a report out of OS that you put in excel and numbered to match the saved approval email in a shared drive? This is how I did it in a previous role where the changes were more manageable :) 

Do you create a custom database table in OS and add a field to tag to those OS change reports to a ticketing system you're managing outside of OS? 

Trying to brainstorm a more efficient way that meets auditors expectations but doesn't involve creating a full time role to pair changes with approvals. Any suggestions on how your company accomplishes this would be very much appreciated! 

Thanks, 
Nicole

Methodology

1 Reply

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    3 hours ago

    We have a BR that reads the Audit tables for each of the elements. It takes a start and end time range and prints that information to an Excel spreadsheet. It has a check sheet to show what has been reviewed:

    And then each element has a sheet where the changes are listed. The amount of information varies by element: Business Rules

    Metadata:

    This is all available from the Audit tables. When you have worked out how to read one table the others are much easier. This is an example of the SQL for Business Rules:

    sql.AppendLine("SELECT ")
sql.AppendLine("(Case  ")
sql.AppendLine("When AuditInsUpdateDel = 0 Then 'Add'  ")
sql.AppendLine("When AuditInsUpdateDel = 1 Then 'Update'  ")
sql.AppendLine("When AuditInsUpdateDel = 2 Then 'Delete'  ")
sql.AppendLine("Else 'Unknown'  ")
sql.AppendLine("End) As 'Change Type' ")
sql.AppendLine(", BusinessRuleType ")
sql.AppendLine(", Name as 'BusinessRuleName' ")
sql.AppendLine(", AuditUser as 'ChangedBy' ")
sql.AppendLine(", AuditTime ")
sql.AppendLine(", FORMAT(AuditTime, 'dd/MMM/yyyy') AS ChangeDate ")
sql.AppendLine(", FORMAT(AuditTime, 'HH:mm') AS ChangeTime ")
sql.AppendLine(" FROM AuditBusinessRule ")
sql.AppendLine($" WHERE AuditTime >= '{formattedStartDate} 00:00:00' AND AuditTime <= '{formattedEndDate} 23:59:59' ")
sql.AppendLine(" ORDER BY BusinessRuleType, Name, AuditTime ")