Sage X3 Audit Trails

Let’s look at the auditing capabilities of Sage X3 and the tools available to use when looking to audit or investigate problems related to either a user, table or session.

We begin by activating auditing on the status field of products, so we can track any changes made to the products status.

Development> Data and Parameters> Tables

*Note: I will be using the “Products” table (ITMMASTER) in this example, but you can apply these methods to any table/fields you choose.

• Select ITMMASTER from left list.
• On Audit tab, under Type of Audit, select Modification.
• Under Fields audited, enter ITMSTA, click Save and Validation.
  • Change the status of a product, go to Common Data> Products> Products.

• To view the audit log,
  • go to Usage> Audit> Fields, (CONSAUD) and enter Table ITMMASTER, field ITMSTA then search.
• The following are the results grid where you will see the audit records of the changes made.

1. Sequence – The audit number sequence value.
2. Table – Table that the audit pertains to.
3. Date & Time of the audited event.
4. Event – Type of event triggering the audit.
5. Key & Secondary Key – Primary and secondary (if any) keys of the audited table.
6. User – User code logged in that triggered the event.
7. Login – System login of the user at origin of audited event.
8. Client – Network address of the user connected that triggered the audit record.
9. Field – It defines the audited field.
10. Previous Value – In case of modification, this displays the previous value of the field.
11. New Value – After modification, this displays the new value of the field.
12. Workflow status – This status is used to find out if a Workflow has been requested on this audit.

This is a great way to track audit activity in specific areas you’d like to monitor.

A standard report that will give detail listing of transaction by account is:
GLGRPDEV which can be found in Reports> reports> reports.

In addition to the ability to audit specific table fields you can also obtain user audits recorded through MongoDB. There is a new menu under Administration called “History Logs”, the purpose of this is to track changes made to user accounts and which users made those changes.

1. Open Administration, Administration, Settings, Global setting.
2. Click Edit.
3. Scroll down to History section.
4. Check mark Enable for Users code, this will enable traces for users, groups and roles.
5. Click Save.
6. Open Administration, Administration, Users, Users.
7. Select an existing user and make a change to that user and Save.
8. Now open Administration, Usage, Logs, History Logs.
9. Notice that you have a line that represents the change that was done to the user.
10. Click on action on that line and select Details.

Notice that there are other things that could be traced beside users, by enabling other codes in global settings.

1. Created on – Date displayed indicating when the change was made
2. Created by – User record that made the change.
3. Operation – Type of change made. (Created, Modified, and Deleted)
4. Entity Name – Area of X3 where the change is reflected.
5. Value – Index of main area of the record where the change was made.

Selecting the record and choosing to view details will display the actual change made.

If you choose to select the “Details” option from the ellipse on the left of each record you will get additional information.

1. Property – Identifies the field(s) that was updated.
2. New Value – New value that was entered.
3. Previous Value – Value that existed prior to the modification.

Tracking station connections

Another useful task to add to your collection of information regarding the user and changes made is to view the station detail where a user’s connection was made.

When a user first connects to Sage X3 and logs in, they create a Syracuse session. If the user then launches a classic function, additional sessions are created, linked to the Syracuse session already created for them.

For users that are currently active, you can see the Syracuse and Classic session information. Navigate to Administration> Usage> Sessions Management> Session Information and see the list of users and the IP address from which they connected. This information is stored in MongoDB but as they are temp tables the data is cleared once the user closes the session, no history of closed sessions is retained.

From Session information you can see you have various details on what sessions, Ports, processes are being used by these specific sessions.

• 1. Type – The type of connection, which could be Standard, related to an end user connection; Batch, related to the batch server; or SOAP, related to the SOAP web services.
  2. Session ID – Identifies a unique ID for the web session. For a given browser and IP address, only one session is created. If two tabs are open, or if two windows using the same browser on a workstation are open, a unique session ID is assigned. It follows the assignment rule of the session cookies.
  3. User login – Identifies the user code that was used for the connection. Note that this user code is unique for a given session.
  4. Status – Identifies the exact status of the session in the authentication or disconnection process.
  5. Web client – Contains the IP address of the client browser connected.
  6. Host – Port – Process -Pid – Identifies the web server that manages the session. If the load balancer is used, the process number is mentioned with this Pid.
  7. Endpoint – X3 login – Identifies the endpoint and the X3 login.

Tables used

• ASYSSMINTERN — Track user connection data, updated when users connect but records are removed on session end.
• ALOGIN table holds information related to each login, the current status, and data connection date stamp.
• AESPION holds a date stamp as well as a time stamp, the user name, a record showing the connection and another record showing the disconnection time, and finally the function the user was in when a data change inside the system was made.

You can query the AESPION table via SQL using the following syntax.

Note: You will need to update the schema name used in this example. (SEED – LIVE – PRODUCTION etc.)

SELECT
ESPDAT_0 ActionDate,
ESPTIM_0 ActionTime,
ESPUSR_0 X3User,
ESPMOT_0 Reason,
ESPFNC_0 X3Function,
ESPTAB_0 X3Table,
ESPNAT_0 OperationAccount,
ESP1_0 Characters1,
ESP2_0 Characters2

FROM SEED.AESPION

ORDER BY ESPDAT_0 desc, ESPTIM_0 desc