btamulis 8/6/2022 5:17:58 PM

GP Security Login Logout Report

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. 

 

Version: All
Section: Dynamics GP, SQL for Beginners, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3