> For the complete documentation index, see [llms.txt](https://documentation.connexica.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://documentation.connexica.com/reporting-guides/audit-reporting-guide.md).

# 04. Audit Reporting Guide

## 03. Audit Reporting Guide

\[CXAIR]\(doc:Technical Documentation.CXAIR.WebHome) includes a comprehensive auditing feature through the use of an audit Index, where usage data for a number of key functionality areas is automatically recorded. To modify what is captured and when, navigate to the Audit tab in the System Settings.

To view the contents of the CXAIR Audit Index, it must be added to a Search Engine.

This guide outlines the key steps needed to create a number of reports from the captured data, facilitated by the changes present in the \[CXAIR 2019.1 release]\(doc:Release Notes.CXAIR 2019.1.WebHome).

## Query Utilisation

When creating reports against the CXAIR Audit Index, filtering the data to records of interest plays a key role in ensuring the best possible performance while maintaining the integrity of the report.

This is due to the sequential nature of how \[Crosstab]\(doc:Technical Documentation.CXAIR.User Guide.02. Reporting.2c. Crosstabs.WebHome) reports are rendered. When the **Run Report** tab is clicked, the specified rows and columns are grouped accordingly before being passed to the parameters set in the **Totals** tab.

By adding saved queries to a row, all subsequently nested values are filtered by the query before any calculations are made. This has the potential to dramatically reduce rendering time as all calculations now only run against the relevant rows of data.

Using saved queries also serves to maintain report integrity, with the query not able to be changed when the report is running as it can when displayed in the query bar. Furthermore, by pre-defining a set of agreed queries, other report creators can quickly leverage the correct syntax rather than attempting to re-create complex queries that may result in an error.

Saved query usage can be taken a step further by using the **Visibility** icon to hide the level:

!\[Screenshot 2019-04-05 at 11.48.37.png]\(image:Screenshot 2019-04-05 at 11.48.37.png||height="91" width="631")

All filtering still takes place, but there is now no indication of its inclusion in the output. This technique is used in a number of reports using the queries detailed below.

### Key Queries

Before creating any reports, a number of key queries are required to filter the data to only the rows of interest. These queries will then be used to drive the report as part of the axes.

Save the following queries run against the CXAIR Audit Index:

| Name                       | Syntax                                                                                                                                                |
| -------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- |
| Created Reports            | `Initial:"true" +FolderPath:"public\*" +RecordType:"LOADANDSAVE" +Undo:"false" -QueryType:"USER"`                                                     |
| Amended Reports            | `+Amended:"true" +FolderPath:"public\*" +RecordType:"LOADANDSAVE"`                                                                                    |
| Report Deletions           | `+Remove:"true" +FolderPath:"public\*" +RecordType:"LOADANDSAVE"`                                                                                     |
| Report Exports             | `~(~(~((-ExportType:" ") -RecordType: ("SCHEDULEEXECUTION" OR "UNIQUEVALUES" OR "LOADANDSAVE"~)~)~) -Username:" ") +FolderPath:"public\*"`            |
| Report Schedule Executions | `+FolderPath:"public\*" +RecordType:"SCHEDULEEXECUTION"`                                                                                              |
| Report Schedule Amendments | `+Amended:"true" +FolderPath:"public\*" +RecordType:"REPORTSCHEDULES"`                                                                                |
| Report Schedule Deletions  | `+Remove:"true" +FolderPath:"public\*" +RecordType:"REPORTSCHEDULES"`                                                                                 |
| Loaded Reports             | `+FolderPath:"public\*" +RecordType:"LOADANDSAVE" -ReportGuid:" " +Undo:"false" +Initial:"false" +Amended:"false" +LoadType:"USER" +QueryType:"USER"` |

{% hint style="info" %}
The above queries use the **+FolderPath:"public\*"** syntax to filter down to public reports. To also include reports residing in home folders, simply remove this syntax from the query.
{% endhint %}

## User Auditing

There are a number of key fields that can be leveraged to track user activity across the system.

### Weekly User Logins

The following report displays a list of users and counts the number of logins across multiple weeks:

This was created with **Record Type** and **Username** used as row values, and **Timestamp Week** used as the column value:

A single value, ‘SUCCESSFULLOGIN’, was selected from the Record Type field using the **…** icon:

This will filter the report to only records that detail successful login information. Rather than displaying this entry against every row, the **Visibility** icon was used to hide the level.

The default Count **Total Type** was used to display a count of records pertaining to user logins.

Finally, the report name was added to the header of the report using the **Layout** tab options:

### User Login Tracking

The following report displays a list of users, the last time they logged in and the number of days since their last login:

This was created with Record Type and Username used as row values:

As detailed in the previous report, the ‘SUCCESSFULLOGIN’ value was selected from the Record Type field using the … icon and the level was hidden from the report to prevent the same line being output against every row.

The report was created using two totals.

The first total has been labelled ‘Last Login Date’, has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

The second total, labelled ‘Days Since Last Login’, uses a cell calculation with the following logic applied:

`Daysbetween('Last Login Date',NOW)-1`

This calculates the number of days between the value derived from the first calculation and the current system date, then minuses one from the output to reflect full days that have passed between the two date values. An appropriate **Value Suffix** of ‘Day(s)’ has also been added to reflect the value in the reporting output:

## Report Auditing

Audit information is written for any report that is created on the system.

### Created Reports

The following report displays key report creation information including the source Index, original report creator, report location, type of report, original creation name and whether a report has been deleted and re-created in the same folder:

The **Created Reports** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Report Name, Index Display, Username, Folder Path and Report Type:

For the Username field, null values were removed using the … icon by moving the entry into the **Selected Fields** column and enabling the **Exclude Selected Values** option. By removing the entry using this option, any new users that are created on the system will be automatically added to the report:

Multiple totals have then been created.

The first total has been labelled ‘Created On’, has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

The second total, labelled ‘Same Name’, has the **Total Type** set to Count and the **Hide Row Total** and **Hide Total in Table** options both enabled to hide the output from the report:

The third total, labelled ‘Duplicate Name’, has the **Total Type** set to String Calculation and the following logic applied:

`CASE WHEN ‘Same Name’ > 1 THEN “Yes” ELSE “No” END`

This uses the second total as the basis for string output in the report, where ‘Yes’ is displayed when the total returns a value greater than one, and ‘No’ when it does not.

### Amended Reports

The following report displays key report amendment information including the report name, source Index, report location, type of report, number of amendments and when the last amendment took place:

The **Amended Reports** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Report Guid, Report Name, Index Display, Folder Path and Report Type:

The Report Guid field was also hidden using the **Visibility** icon, as it is used to order the subsequent Report Name field and its visibility is not required in the reporting output.

Two totals have then been created.

The first total has been labelled ‘No. of Amends’ and has the **Total Type** set to Count:

This provides a simple count of records that, due to the query used to drive the report, all pertain to report amendments.

The second total has been labelled ‘Last Amended On’, has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

### Loaded Reports

The following report displays key information for loaded reports including the report name, report location, report type, number of loads and the last time it was loaded:

The **Loaded Reports** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Report Guid, Report Name, Folder Path and Report Type:

!\[L2.png]\(image:L2.png||height="436" width="420")

The Report Guid field was also hidden using the **Visibility** icon, as it is used to order the subsequent Report Name field and its visibility is not required in the reporting output.

Two totals have then been created.

The first total has been labelled ‘Times Loaded’ and has the **Total Type** set to Count:

!\[L3.png]\(image:L3.png||height="328" width="970")

This provides a simple count of records that, due to the query used to drive the report, all pertain to the loading of saved reports.

The second total has been labelled ‘Last Loaded On’, has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

!\[L4.png]\(image:L4.png||height="351" width="973")

### Deleted Reports

The following report details every report deletion across the system, including key report information including the report name, source Index, the user who performed the deletion, the report location and the date and time the deletion took place:

!\[4.3a.png]\(image:4.3a.png||height="157" width="580")

The **Report Deletions** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Report Name, Index Display, Username and Folder Path:

For the Username field, null values were removed using the … icon by moving the entry into the **Selected Fields** column and enabling the **Exclude Selected Values** option. By removing the entry using this option, any new users that are created on the system will be automatically added to the report:

A single total has then been created, labelled ‘Deleted On’, that has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

### Report Exports

The following report details every front-end report export across the system with key report information including the report name, the user who performed the export, the report location, the export file type and the date and time the export took place:

!\[4.4a.png]\(image:4.4a.png||height="142" width="455")

The **Report Exports** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Report Name, Username, Folder Path and Export Type:

For the Username field, null values were removed using the … icon by moving the entry into the **Selected Fields** column and enabling the **Exclude Selected Values** option. By removing the entry using this option, any new users that are created on the system will be automatically added to the report:

A single total has then been created, labelled ‘Exported On’, that has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

## Schedule Auditing

There is a wealth of data generated relating to report schedules designed to aid the maintenance of report schedules across a system.

### Report Schedule Executions

The following report details report schedule across the system with key report information including the schedule name, report name, the user who configured the schedule, distribution method, location, when the schedule was last run and the average execution duration:

!\[5.1a.png]\(image:5.1a.png||height="141" width="580")

The **Report Schedule Executions** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Schedule Name, Report Name, Username, Folder Path and Method:

Two totals were then created.

The first total, labelled ‘Last Schedule Ran On’, has the **Total Type** set to Maximum and the Timestamp field selected from the **Measure** drop-down list. As this is a datetime field, dd/MM/yyyy HH:flag\_mm:ss has been selected from the **Value Format** drop-down list to preserve the original format:

The second total, labelled ‘Avg Execution Duration’, has the Total Type set to Sum and Calculation selected from the Measure drop-down list. The following calculation has been applied:

`Duration/1000`

Used in conjunction with the Time Measure option selected from the **Show Value As** drop-down list, milliseconds can be converted to seconds and expressed in time format:

### Report Schedule Amendments

The following report details report schedule amendments across the system with key schedule information including the schedule name, report name, the user made the amendment, location and when the amendment was made:

!\[5.2a.png]\(image:5.2a.png||height="172" width="580")

The **Report Schedule Amendments** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Schedule Name, Report Name, Username, Folder Path and Timestamp:

For the Username field, null values were removed using the **…** icon by moving the entry into the **Selected Fields** column and enabling the **Exclude Selected Values** option. By removing the entry using this option, any new users that are created on the system will be automatically added to the report:

A single total with the **Total Type** set to Count and the **Hide Row Total** and **Hide Total in Table** options both enabled to hide the output from the report:

By hiding the total values, the rows of interest are simply output in the report without any unnecessary figures.

### Report Schedule Deletions

The following report details report schedule amendments across the system with key schedule information including the schedule name, report name, the user made the amendment, location and when the amendment was made:

!\[5.3a.png]\(image:5.3a.png||height="162" width="580")

The **Report Schedule Deletions** query has been loaded as the first row entry and then the level hidden using the **Visibility** icon. This will ensure the report is filtered by the query results, but the name of the query will not be output against every row unnecessarily.

The following fields were then added as nested rows: Schedule Name, Report Name, Username, Folder Path and Timestamp:

For the Username field, null values were removed using the **…** icon by moving the entry into the Selected Fields column and enabling the **Exclude Selected Values** option. By removing the entry using this option, any new users that are created on the system will be automatically added to the report:

A single total with the **Total Type** set to Count and the **Hide Row Total** and **Hide Total in Table** options both enabled to hide the output from the report:

By hiding the total values, the rows of interest are simply output in the report without any unnecessary figures.

## Index Auditing

With the different Index build types that can be used across a system, monitoring Index record counts over time is an important administrative task that can be automated using the audit Index.

### Daily Index Record Counts Trend

The following report details daily Index record counts:

!\[6.1a.png]\(image:6.1a.png||height="430" width="688")

The Record Type, Action and Data Source Group Display fields were added as rows, and Start Time Day was added as the column:

Using the **…** icon for every field, a number of values were selected.

For the Record Type field, the ‘INDEXMAINTENANCE’ value was selected:

For the Action field, the ‘BUILD’ value was selected:

For the Data Source Group Display field, the null values were removed by moving entry into the **Selected Fields** column along with the CXAIR Configuration CXAIR Users values before enabling the **Exclude Selected Values** option. By removing unwanted entries using this option, any new Indexes that are created on the system will be automatically added to the report:

Finally, for Start Time Day field, null values were removed using the same technique as detailed above:

Three totals were then created.

The first total, labelled ‘Avg Records’, has the **Total Type** set to Average and the Inserted field selected from the **Measure** drop-down list:

The second total, labelled ‘Min Records’, has the **Total Type** set to Minimum and the Inserted field selected from the **Measure** drop-down list. The **Hide Total in Table** option has also been enabled to only display the row total value:

The third total, labelled ‘Max Records’, has the **Total Type** set to Maximum and the Inserted field selected from the **Measure** drop-down list. The **Hide Total in Table** option has also been enabled to only display the row total value:

### Monthly Index Record Counts Trend

The following report displays a record count for each Index per month, along with an average record count:

!\[6.2a.png]\(image:6.2a.png||height="322" width="503")

This uses the same fields as the previously detailed report, with only the column changed to Start Time Month:

The null values were removed by moving entry into the **Selected Fields** column and enabling the **Exclude Selected Values** option. By removing unwanted entries using this option, new dates will be automatically added to the report:

## Further Queries

The following queries can be used to filter the data to events of interest:

| Name                              | Syntax                                                                         |
| --------------------------------- | ------------------------------------------------------------------------------ |
| System Up/Down                    | `+RecordType: ("SYSTEMSTART" OR "SYSTEMSTOP")`                                 |
| User Changes to Calculated Fields | `+RecordType:"CONFIGURATION" +DeltaType:"Save" -Configuration_Extrafields:" "` |
| Report Schedule Creation          | `Initial:"true" +FolderPath:"public\*" +RecordType:"REPORTSCHEDULES"`          |