User & Table auditing in X3

Welcome to another blog, today we will discuss the auditing capabilities of X3 and tools available to use when looking to audit or investigate problems potentially related to a user, table, or session. 

Let’s begin this journey by activating auditing on the status field of products, so we can track any changes made to the products status.

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

How to run an audit on a table.field?

• To turn on an audit trail on a table, go to Development, Data and Parameters, Tables, Table.
• 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.

For details explanations on all the fields found in this function please visit online help here. 

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. (DEMO – 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 DEMO.AESPION

ORDER BY ESPDAT_0 desc, ESPTIM_0 desc  

Additional information regarding SQL auditing options are available here.

I hope this blog has provided some useful information on what is available to use when investigating users, sessions, and auditing history.

Be sure to review other helpful documents on this subject in the knowledge base, Sage community and online help if needed.