Okay - I found a SQL Stored Procedure on internet (Credit to Jack Worthem) (A Better Way To Track User Activity In Dynamics GP (jackworthen.com)
that I have tweaked for my purposes.
I want a basic - log in, log out report - here's what I have so far:
USE [DYNAMICS]
------select * from track_activity_code
DECLARE @userid VARCHAR(20),
@startdate DATETIME,
@enddate DATETIME
----@action VARCHAR(100)
SET @userid = 'BronT'
SET @startdate = '8/01/2022'
SET @enddate = '8/01/2022'
----SET @action = '2'
--------select * from Sy05000 where USERID='BronT' and DATE1 >='2022-08-01' and inqytype='15'
IF @UserID IS NULL
BEGIN
SELECT s.USERID AS 'User ID',
m.USERNAME AS 'User Name',
m.USRCLASS AS 'User Class',
s.DATE1 AS 'Date',
s.TIME1 AS 'Time',
c.description AS 'Simple Description',
s.SECDESC AS 'Detailed Description',
s.CMPNYNAM AS 'Company Name'
FROM sy05000 s
INNER JOIN track_activity_code c on s.inqytype = c.code
INNER JOIN SY01400 m ON m.userid = s.userid
WHERE s.date1 BETWEEN @startdate AND @enddate and c.code in ('15','2')
ORDER BY s.DATE1
END
ELSE IF @UserID IS NOT NULL
BEGIN
SELECT s.USERID AS 'User ID',
m.USERNAME AS 'User Name',
m.USRCLASS AS 'User Class',
s.DATE1 AS 'Date',
s.TIME1 AS 'Time',
c.description AS 'Simple Description',
s.SECDESC AS 'Detailed Description',
s.CMPNYNAM AS 'Company Name'
FROM sy05000 s
INNER JOIN track_activity_code c on s.inqytype = c.code
INNER JOIN SY01400 m ON m.userid = s.userid
WHERE s.date1 BETWEEN @startdate AND @enddate and c.code in ('15','2')
AND s.userid = @userid
ORDER BY s.DATE1
END
Here's what my dataset looks like:
First problem - the SY0500 table always has two identical records for the log out event. No idea why - maybe third party or something is writing a duplicate record. So, 6 rows really should be 4 rows.
Secondly, not sure if I should just pivot the data in the stored procedure so that these 6 would be two records since we have two login's and two logouts.
Thirdly, not certain how I want to handle the way too often no log out situation. Any thoughts?
Has anybody done a nice clean query that shows time logged into GP, by user, each day.
Any guidance would be appreciated.