ADS: sp_mgGetUserTables() with same user name

[Tom]

Hi, there.

In a topology that runs on (among other things) a terminal server, all users who connect to the ADS have the same username under most installation circumstances. If you retrieve further information about this with sp_mgGetConnectedUsers(), you will get, for example, the actual user name with which you logged in to Windows, as well as the IP and a few other pieces of information. Unfortunately, at least for me, the "ConnNumber" is always and in all cases 0 (zero), and unfortunately that is the only other way to find out who keeps which tables open via sp_mgGetUserTables(). So: I have "TERMINAL SERVER USER" three times as an ADS user, and all three have connection number 0. I see three different connections, but via sp_mgGetUserTables() with "TERMINAL SERVER USER" I always get the same tables - namely the ones that all three users have opened. Alternatively with the connection number (0) it is the same.

You can test this with the ADSMGT-tool Roger provided.

Any ideas?

[rdonnay]

I will run my AdsMgt program on the terminal server tomorrow morning after I can make several RDP connections.

Most of our users are connecting from workstations on a LAN.

[k-insis]

To track users who have open files eg. program running so you can kick it out of exe for maintenance?

How about each user has single, after that user named table? Like 'con_sepplG.dbf' , 'conn_betinaM.dbf', etc and all are opened shared+RO all the time in separate thread. So you might check for open tables in ads management tool.

Hi, there.

In a topology that runs on (among other things) a terminal server, all users who connect to the ADS have the same username under most installation circumstances. If you retrieve further information about this with sp_mgGetConnectedUsers(), you will get, for example, the actual user name with which you logged in to Windows, as well as the IP and a few other pieces of information. Unfortunately, at least for me, the "ConnNumber" is always and in all cases 0 (zero), and unfortunately that is the only other way to find out who keeps which tables open via sp_mgGetUserTables(). So: I have "TERMINAL SERVER USER" three times as an ADS user, and all three have connection number 0. I see three different connections, but via sp_mgGetUserTables() with "TERMINAL SERVER USER" I always get the same tables - namely the ones that all three users have opened. Alternatively with the connection number (0) it is the same.

You can test this with the ADSMGT-tool Roger provided.

Any ideas?

[Tom]

To track users who have open files eg. program running so you can kick it out of exe for maintenance?

To track users who have special files open. I'm talking about a situation with 200+ users in a mixed environment, about half of them using a TS with the same user name on that TS (which is standard on a TS - the name of the computer). All the guys accessing via LAN ("classical") are no problem. I can find the OSUserLoginNames of those users to find out from which workstation they connected, but I can only use the UserName to get the information of opened tables by a user, so I get all tables open used by (all) the TS user(s), because those user names are not unique. I need to find a way to match open tables with the OS login names.

[Tom]

What I need is a way to find the tables used by an ADS user with a special "TSAddress". This information is unique even on a Terminal Server.

[Tom]

Found a way, Marcus Herz from the german forum helped me.
If I retrieve all user tables open for a user name (which is the computer name), I can walk through the result list and find out using "sp_mgGetTableUsers(<cTable>)", which TSAddress or OSLoginUserName is attached to every open table. This gives me the information I need.

[k-insis]

Great!

Thx for sharing info. Might be handy.

Found a way, Marcus Herz from the german forum helped me.
If I retrieve all user tables open for a user name (which is the computer name), I can walk through the result list and find out using "sp_mgGetTableUsers(<cTable>)", which TSAddress or OSLoginUserName is attached to every open table. This gives me the information I need.

[Tom]

To mention Marcus again:

He pointed out to me that the "Connection number" only worked in the context of NetWare and has been de-supported for quite a while.

[Tom]

Addition: Using those stored procedures again and again may lead to ADS Error 7216, which means the number of SQL-statements for that connection is exceeded. sp_SetStatementLimit(0) solves that problem.

Edit: Correcting a misbehaviour solves the problem aswell.

I took the functions from ADSMGT.PRG, which use the SQL command translated into DC_AdsSqlStatement(). It is needed to close the statement after finishing the function, like this:

Code: Select all

STATIC FUNCTION LoadTableUsers(oSession,cTable)
LOCAL cSql, cAlias := 'Tables', aObjects := {}, oStatement

TEXT INTO cSql WRAP
SELECT * FROM
(
  EXECUTE PROCEDURE sp_mgGetTableUsers('TABLE')
) anyalias
ENDTEXT

cSql := StrTran(cSql,"TABLE",AllTrim(cTable))

SQL cSql INTO OBJECTS aObjects INTO CURSOR cAlias VIA oSession TO oStatement

dbCloseArea()
oStatement:Close() // <-- don't forget, otherwise open workareas will increase and the number of open statements aswell
RETURN aObjects

[rdonnay]

We have 3 or 4 users who connect via RDP and VPN occasionally to 2 different user accounts on the server.

This screen shows what info we have on those connections.
The username is W2016SVR on each connection, but they have different TSAddress which is the VPN address..

adsmgt.JPG (213.6 KiB) Viewed 1860 times