Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Steve Erbach]

Dear Collaborators,

As part of the GP Admin Dashboard that 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
——————————

[Beat Bucher]

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)

——————————
——————————————-

[Steve Erbach]

, 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
——————————
——————————————-

[Matthew Arp]

[Matthew Arp]

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
——————————
——————————————-

[Steve Erbach]

,

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
——————————
——————————————-

[Beat Bucher]

& ,

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)

——————————
——————————————-

[Steve Erbach]

, , and ???,

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 AS c ON c.session_id = s.spid
INNER JOIN DYNAMICS..ACTIVITY AS a ON 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
——————————
——————————————-

[John Arnold]

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
——————————
——————————————-

[Matthew Arp]

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 ‘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
——————————
——————————————-

[John Arnold]

IIF you have SQL 2012 or above, I’d expand on ?? 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
——————————
——————————————-

[Beat Bucher]

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)

——————————
——————————————-

[Matthew Arp]

, 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
——————————
——————————————-

[Beat Bucher]

,
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)

——————————
——————————————-