Querying The System Log

Post date: Feb 21, 2019 3:19:1 PM

SMCP records events in the 'System Log', along with information relevant to the event.  For example, when the AR Aging report is run, the system records when it was run, who ran it, and so on.  Over a hundred other events are recorded - fr example, each log in: date, time, user, etc., goes into the log.  Some of the 'event's are used by the developers for diagnostic purposes, but most just record reports being run, or screens and functions being accessed.

Because these events always include the date, time, and the user - as well as details about the event - this record provides a rich store of useful data that can be accessed through queries.

NOTE: some of the 'operations' listed here have been recently changed to make them consistent, so the queries listed below may ONLY work in the updated (as of 2/20/2019) version of SMCP.  To query from logs before that date, check in with us.

For example: to see the 1000 most recent user logins, listing them from latest to earlier, you can run this simple query:

SELECT * FROM systemlog WHERE (soperation = 'SMUSERLOGIN') ORDER BY datloggingtime DESC LIMIT 1000

To see who ran the 'Critical Dates' report, and when, run this query:

SELECT * FROM systemlog WHERE (soperation = 'SMCRITICALDATEREPORT') ORDER BY datloggingtime DESC

You can also audit individual user activity - this query will list the last 1,000 'events' for a particular user:

SELECT * from systemlog WHERE (suserfullname = '[[Enter the user's full name:]]') ORDER BY datloggingtime DESC LIMIT 1000

The system log records every query run as well, so you can audit who queried for what information and when.

To make this more useful, we've added a new function called 'SM List Logging Operations (1223)' to list ALL of the 'event markers' you can use to query your system log.  It lists all of the 'markers' (in the left column), and a description of each (in the right).  This list is updated with each program update to include any NEW logging events.  This item will appear in the top ('System Administration') menu when the permission is granted for it.

Here's what the 'List of Logging Operations' looks like currently: