Open Forum

Expand all | Collapse all

Will ACTIVITY.ClientUIType do the trick?

Jump to Best Answer
  • 1.  Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 11 days ago
    Dear Collaborators,

    As part of the GP Admin Dashboard that @Beat Bucher and I created, I was hoping to find a way to tell if a GP user is logged in via a Remote Desktop Connection or via a local client. I see the "ClientUIType" column in the DYNAMICS.ACTIVITY table, but that doesn't appear to help me.

    Is there any way to differentiate?

    Why do I ask, you ask? Because we're transitioning from all local client installs of GP to using just the Remote Desktop install. Some of our 3rd-party apps aren't quite tweaked all the way, so individual users fall back on their local GP installs. I'd like to know who's who.

    Sincerely,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 11 days ago
    My dear @Steve Erbach,
    I wish there would be a simple 'magic' way of doing this distinction... but alas, there is not. The field in the ACTIVITY table was introduced a few releases back when the Web Client made it's way into GP.. this way Microsoft can differentiate the license type (full client or web client).
    A full client remains a full client, and there is no way (AFAIK) to know if it runs from a Citrix / TS environment or a local GP client..  The SQL server activity monitor would give you that information, but won't tell you if the actual computer is a workstation or a server by that effect (at least I don't think so).
    You'd have to come up with a tricky way to identify the users, i.e. defining users that access GP only from a TS by appending a "_TS" to their user name.. that could be an option.
    Any other suggestions are welcome :-)​

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 11 days ago
    @Beat Bucher, mon ami!​​​

    >> I wish there would be a simple 'magic' way of doing this distinction... but alas, there is not. <<

    Quelle tristesse!

    You gave me an idea, though. Our users start GP from desktop shortcuts, both for the local install and for the RDS install. I think we could wrap the startup for GP in a batch file (or, better yet, a PowerShell script) that appended a line in a log that identifies the workstation name and the date & time of the login attempt. That would be a start, I think.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: Will ACTIVITY.ClientUIType do the trick?

    TOP CONTRIBUTOR
    Posted 11 days ago
    Edited by Matthew Arp 11 days ago
    Conference-GPUG_200x200


  • 5.  RE: Will ACTIVITY.ClientUIType do the trick?

    TOP CONTRIBUTOR
    Posted 11 days ago
    Edited by Matthew Arp 11 days ago
    Gasp, I totally misread your question... If you have a list of your Terminal servers it is easy...

    select login_name, 0 IsLocalClient from sys.dm_exec_sessions where login_name IN (select USERID from DYNAMICS.dbo.ACTIVITY) AND host_name IN('WSTS1','WSTS2')
    UNION ALL
    select login_name, 1 IsLocalClient from sys.dm_exec_sessions where login_name IN (select USERID from DYNAMICS.dbo.ACTIVITY) AND host_name NOT IN('','WSTS1','WSTS2')​


    ------------------------------
    Matthew Arp
    Business Systems Developer
    Hunton Group
    Houston TX
    ------------------------------

    Conference-GPUG_200x200


  • 6.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 11 days ago
    @Matthew Arp,

    I like the "Gasp!"

    I ran this query to spelunk a bit:

    SELECT s.*
    FROM sys.dm_exec_sessions AS s
    WHERE s.login_time > '2019-11-01'
    AND s.login_name IN
    (SELECT USERID FROM dbo.ACTIVITY)
    ORDER BY login_time

    ...but no joy! The host_name column had no values in it. Close, but no banana!

    Regards,


    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 7.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 11 days ago
    Edited by Beat Bucher 11 days ago
    @Steve Erbach & @Matthew Arp,

    The suggestion might actually work... it presumes that you know the name of your TS/Citrix servers.. in the case of the GP Admin Dashboard we use very generic scripts, trying to avoid as much customization as possible.
    This would imply that you have to change the ​​list of possible TS/Citrix servers within the script before using the Excel Dashboard..

    I was thinking about the SQL session table and I wasn't aware that you can distinguish between local & remote sessions.. which opens a new door of options.
    It's kind of confusing that 1 = remote in 'IsLocalClient' and 0 = local .. but heck it works, so why complain :-)

    Edit: try this code out :

    SELECT DISTINCT(s.login_name) -–prevents double records by user as there are 2 timestamps
           ,Format(s.login_time,'yyyy-MM-dd', 'en-US') as LoginDate
          ,s.host_name
           ,s.program_name
           ,s.host_process_id
           ,s.client_interface_name
           ,s.STATUS
           ,s.database_id

    FROM sys.dm_exec_sessions AS s

    WHERE s.login_time > CONVERT(DATE, GETDATE() - 1)
           AND s.login_name IN (
                  SELECT USERID
                  FROM dbo.ACTIVITY )
           AND s.host_name <> ''
           AND client_version = 7 -- Returns only GP Dexterity sessions




    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Conference-GPUG_200x200


  • 8.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 8 days ago
    @Beat Bucher, @Matthew Arp, and @John Arnold ​​​,

    Well! Some neat solutions to my problem!

    1. Béat, your query has the same issue as the original query that Matthew posted: the host_name column​ has no values at all; so the query can't distinguish between RDS users and local users.
    2. John's original query shows the different IP addresses... and, indeed, the RDS IP is shared by the multiple users who log into GP from it.
    3. Then Matthew's refinement flags the RDS users with 0s and the local users with 1s.
    4. John refined his query to show a ClientType using the IIF() function.
    5. And I modified John's refinement using a CASE statement instead of the IIF() function:

    SELECT DISTINCT s.loginame
    , c.client_net_address
    , CASE
    WHEN c.client_net_address IN ('192.168.16.35') THEN 'Remote'
    ELSE 'Local'
    END AS ClientType --, IIF(c.client_net_address IN ('192.168.16.35'), 'Remote', 'Local') AS ClientType
    FROM master..sysprocesses AS s
    INNER JOIN sys.dm_exec_connections ASON c.session_id = s.spid
    INNER JOIN DYNAMICS..ACTIVITY ASON a.USERID = s.loginame;

    Excellent. Thank you Béat. Matthew, and John (kinda reminds me of the old Dion song!) for your help!

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 9.  RE: Will ACTIVITY.ClientUIType do the trick?

    TOP CONTRIBUTOR
    Posted 9 days ago
    Hi Steve,

    Assuming your Desktop Clients will have a different IP range than your Remote Desktops, how about a query that gives the IP address of the GP Users that are logged in.  This seems to work (I haven't tested it much)

    SELECT DISTINCT master..sysprocesses.loginame, sys.dm_exec_connections.client_net_address
    FROM master..sysprocesses
    JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = master..sysprocesses.spid
    JOIN DYNAMICS..ACTIVITY ON ACTIVITY.USERID = master..sysprocesses.loginame

    I hope this helps!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Conference-GPUG_200x200


  • 10.  RE: Will ACTIVITY.ClientUIType do the trick?

    TOP CONTRIBUTOR
    Posted 9 days ago
    So it looks like Microsoft 'fixed' something between GP versions, previously there would be at least one SQL sessions that had the host_name populated. Testing my script against a 2018 install I see that GP never populates the host_name attribute... 😤

    So to expand on @John Arnold's suggestion, I updated my original script to go off IP addresses.

    select login_name, 0 IsLocalClient from sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c ON c.session_id=s.session_id WHERE login_name IN (select USERID from DYNAMICS.dbo.ACTIVITY) AND c.client_net_address IN('TS SERVER IPs')
    UNION ALL
    select login_name, 1 IsLocalClient from sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c ON c.session_id=s.session_id WHERE login_name IN (select USERID from DYNAMICS.dbo.ACTIVITY) AND c.client_net_address NOT IN('TS SERVER IPs')​


    ------------------------------
    Matthew Arp
    Business Systems Developer
    Hunton Group
    Houston TX
    ------------------------------

    Conference-GPUG_200x200


  • 11.  RE: Will ACTIVITY.ClientUIType do the trick?
    Best Answer

    TOP CONTRIBUTOR
    Posted 8 days ago
    IIF you have SQL 2012 or above, I'd expand on @Matthew Arp​​ and use the IIF function

    SELECT DISTINCT sysprocesses.loginame,
    dm_exec_connections.client_net_address,
    IIF(dm_exec_connections.client_net_address IN ('TS IPs', '10.1.6.147'), 'Remote', 'Local') AS ClientType
    FROM master..sysprocesses
    JOIN sys.dm_exec_connections ON dm_exec_connections.session_id = sysprocesses.spid
    JOIN DYNAMICS..ACTIVITY ON ACTIVITY.USERID = sysprocesses.loginame



    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Conference-GPUG_200x200


  • 12.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 7 days ago
    Guys,
    I don't know about you, but having to find out which IP Address belongs to which server is pretty bad.. I want the information at the tip of my fingers.. 

    Don't know if anything changed in GP 2018R2 and above, but this query below was tested against a Citrix Farm with 3 servers and 1 GP / SQL server.. And it returns every single user sessions from the GP ACTIVITY table with their corresponding server & DB name : 

    -- this query returns only GP clients with their host name to identify the actual session.

    SELECT DISTINCT(es.login_name)

           ,Format(es.login_time,'yyyy-MM-dd', 'en-US') as LoginDate

           ,es.host_name

           ,es.program_name

           ,es.host_process_id

           ,es.client_interface_name

           ,es.STATUS

           --,es.database_id

           --,sp.dbid

           ,db_name(dbid) as 'Database'

    FROM sys.dm_exec_sessions AS es inner join sys.sysprocesses sp on es.session_id = sp.spid

    WHERE es.login_time > CONVERT(DATE, GETDATE() - 1)

           AND es.login_name IN (

                  SELECT USERID

                  FROM DYNAMICS.dbo.ACTIVITY

                  )

           AND es.host_name <> ''

           AND client_version = 7

           AND es.program_name = 'Dexterity for Microsoft Dynamics GP'

    The output result could't be clearer :


    As you can see, the one session running off the SQL server itself, while all the others are split between the various Citrix servers.
    This configuration is running off a Windows Server 2016, SQL server 2017 (14.0.17285) and GP 2018 R2.
    By using the Program Name field to filter out data for Dexterity only, I cut all the noise coming from MR 2012 sessions, or every other .NET application that is hooking up to the server.
    Strangely enough, for some users, SQL is returning 2 different DB names under the same host_process_id, which I suspect might be a company change from within GP, without closing the session, in which case it just keeps the old DB ID in the session table, though it's no longer using it. This is confirmed by using the full login_time stamp instead of truncating it to the single date only, showing that one company was used earlier by the same user.

    While not being perfect, it give a pretty good idea of who is logged on in GP thru which terminal / computer.



    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Conference-GPUG_200x200


  • 13.  RE: Will ACTIVITY.ClientUIType do the trick?

    TOP CONTRIBUTOR
    Posted 7 days ago
    Edited by Matthew Arp 7 days ago
    @Beat Bucher, there must have been a change, that host_name column is no longer populated in GP2018R2.

    ------------------------------
    Matthew Arp
    Business Systems Developer
    Hunton Group
    Houston TX
    ------------------------------

    Conference-GPUG_200x200


  • 14.  RE: Will ACTIVITY.ClientUIType do the trick?

    GPUG ALL STAR
    Posted 7 days ago
    @Matthew Arp,
    I'm going to run some testings on my various Azure VM's I own.. I have multiple setups ranging from GP 2010 up to the very last GP version and running on various OS & SQL platforms.
    From Microsoft's SQL server documentation about host_name :

    Security Note: The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature.​

    In the case of GP, it looks like the host_name is not provided anymore when using TS from Microsoft, but it seems to work just fine with Citrix... weird.


    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role