Open Forum

Dynamics SL Usage Statistics

  • 1.  Dynamics SL Usage Statistics

    Posted 10 days ago
    We have a client who would like a report of who and which of their users access which screen or report in Dynamics SL.  Preferably the report would be graphical and show a range for example last 30 days? 90 days, 180 days etc

    I thought I would use Userrec outjoin to pstatus, but seems limited.

    Appreciate any pointers...

    ------------------------------
    Peter Ongaria
    Managing Director
    SysCorp International Limited
    Kampala
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Dynamics SL Usage Statistics

    Posted 8 days ago
    Each table has a Last Updated date/time, last updated user and last updated process (screen ID etc). This could be used for the detailed data, with UserRec to use as a cross reference.
    You might want to put a trigger on these fields to populate a user table to report from.

    ------------------------------
    Kevin Finn
    Director
    N2 End Support Services Ltd.
    London
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Dynamics SL Usage Statistics

    TOP CONTRIBUTOR
    Posted 8 days ago
    As you've probably already found, using the "Crtd_User" and "Lupd-User" fields in the various SL tables is very useful, but has its limits.    We wanted what you wanted:  record who opened a screen or report - even if they didn't update anything, and when.    We have the additional requirement of knowing which of our many databases the user logged into.    So we created a custom table in the System Database to store the data accesses, and then added a trigger on the table [SL SYSTEM DB].[dbo].[Access] to capture when screens or reports were opened.  Anytime I need a list of who did what, I query that custom table [XScreenAccess].    We've been running this trigger since 2012 with no ill effects.

    Of course, you will need to write a report/graphic to consume data from the custom table.

    Custom Table and Trigger follow.

    USE [XX-SYSTEMDATABASENAME-XX]
    GO

    /****** Object: Table [dbo].[xScreenAccess] Script Date: 9/14/2020 8:47:35 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[xScreenAccess](
    [AccessDate] [smalldatetime] NOT NULL,
    [CpnyID] [varchar](10) NOT NULL,
    [ScrnNbr] [varchar](5) NOT NULL,
    [UserID] [varchar](47) NOT NULL
    ) ON [PRIMARY]
    GO

    GRANT SELECT ON [dbo].[xScreenAccess] TO [07718158D19D4f5f9D23B55DBF5DF1] AS [dbo]

    GO

    GRANT SELECT ON [dbo].[xScreenAccess] TO [E8F575915A2E4897A517779C0DD7CE] AS [dbo]
    GO

    GRANT CONTROL ON [dbo].[xScreenAccess] TO [MSDSL] AS [dbo]
    GO

    GRANT DELETE ON [dbo].[xScreenAccess] TO [MSDSL] AS [dbo]
    GO

    GRANT INSERT ON [dbo].[xScreenAccess] TO [MSDSL] AS [dbo]
    GO

    GRANT SELECT ON [dbo].[xScreenAccess] TO [MSDSL] AS [dbo]
    GO

    GRANT UPDATE ON [dbo].[xScreenAccess] TO [MSDSL] AS [dbo]
    GO

    GRANT DELETE ON [dbo].[xScreenAccess] TO [public] AS [dbo]
    GO

    GRANT INSERT ON [dbo].[xScreenAccess] TO [public] AS [dbo]
    GO

    GRANT SELECT ON [dbo].[xScreenAccess] TO [public] AS [dbo]
    GO

    GRANT UPDATE ON [dbo].[xScreenAccess] TO [public] AS [dbo]
    GO


    USE [XX-SYSTEMDATABASENAME-XX]
    GO

    /****** Object: Trigger [dbo].[TRG_ScreenUsage] Script Date: 9/14/2020 8:46:49 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE TRIGGER [dbo].[TRG_ScreenUsage] ON [dbo].[Access]
    FOR INSERT
    AS

    Set NoCount On

    INSERT INTO xScreenAccess (AccessDate,CpnyID,ScrnNbr,UserID)
    SELECT GETDATE(),CompanyID,ScrnNbr,UserID
    FROM Inserted
    where ScrnNbr <> 'MENU'

    Set NoCount Off

    GO

    ALTER TABLE [dbo].[Access] ENABLE TRIGGER [TRG_ScreenUsage]
    GO

    Hope this helps.



    ------------------------------
    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Dynamics SL Usage Statistics

    TOP CONTRIBUTOR
    Posted 8 days ago
    PS  Because we are a government contractor, we also needed to have far more detailed tracking.   We have to be able to identify who made what change to which records in a database, when that change was made, and in which database.    To enable that, we have used iterations of a product that is now FastPath which we've used since around 2013.     The trick for us is to remember to install the product every time we create a new database.    We have a template we've created to define which tables we track and in what detail  - which speeds up setup for each database and creates uniformity.

    ------------------------------
    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts