From time to time at Community the question arises how to track when which users have logged in via Service Portal or via Backend.
In order not have to implement a new application for this purpose, it would make sense using one of the OOTB tables. The table sysevent (Event Log) is a promising candidate:
We can see who has logged in when. But we cannot see where the user has logged in.
To answer this question we need the syslog_transaction table (Transaction Log), which stores records regarding all browser activities for an instance. The session value of that table corresponds to the instance field at table sysevent. To distinguish where a user has logged in, you can look for a value in the URL field that starts with "/api/now/sp":
Now you only need an Action Script that listens for the "login" event and writes into the field parm2 whether the user has logged in to the "Backend" or to the "Portal":
For better copying, find the following code from field "Script":
var grTransactionLog = new GlideRecord('syslog_transaction');
grTransactionLog.addEncodedQuery('urlSTARTSWITH/api/now/sp^session=' + event.getValue('instance'));
grTransactionLog.setLimit(1);
grTransactionLog.query();
event.setValue('parm2', grTransactionLog.next() ? 'Portal' : 'Backend');
event.update();
Now at table sysevent you have everything you need for generating the required reports:
Please note
With my solution, the IP address of the logged-in user (value of "Parm2" field at table sysevent) is overridden. If you need that IP address for tracking purposes you also can add a new custom field to that table and store the login source there.
All records older than 7 days are cleared automatically by the OOTB table cleaner. To increase the value of 604800 seconds go to table sys_auto_flush and search for table "sysevent". Then enter a respective value at column "Age in seconds":
