xsharp.eu • ADS Remote DBServer Monitoring (X# 2.7)
Page 1 of 1

ADS Remote DBServer Monitoring (X# 2.7)

Posted: Tue Dec 01, 2020 9:41 am
by rjpajaron
Hello,

See below is a working code on ADS Remote DBServer Monitor.

The code is already in our production with some modification to fit our use cases.
In case where the ADS data folder location were hidden, with actual path alias describe at adsserver.ini at the physical server boot drive, I implemented this by having a data config table that stores the actual folder location of our production database. This way "folderPath" is relative as the code monitors the running ADS which probably is only exposed only by IP or UNC path and actual path are hidden.

RequiredTables works like a treat when you only target particular table or tables. I did this when the app is about to do some maintenance that requires EXCLUSIVE access to a group of tables. Sysadmin love the this tools because they can see which computers are opening tables and also detect running apps on sleeping computers.

Well, it works in our end in VO and on X# 2.7 when Robert decided to include ADS ACE Management API. Thanks Roberts.

Cool!

Code: Select all

USING Xsharp.ADS

DEFINE AE_SUCCESS := 0


FUNCTION Start() AS VOID STRICT
    ? "Hello World! Today is ",Today()

	LOCAL oRDSM AS ADSRemoteDbServerMonitor

	LOCAL tableArrayList AS ARRAY
	LOCAL userArrayList AS ARRAY
	LOCAL requiredTables AS ARRAY
	LOCAL tableCount, userCount AS DWORD
	LOCAL folderPath AS STRING
		
	BEGIN SEQUENCE

	folderPath := "d:client coopbase data backupmempco2020crm_data" //CRMFOSC00001"
	
	tableArrayList := {}
	requiredTables := {}
	userArrayList := {}

	oRDSM := ADSRemoteDbServerMonitor{}
	
	oRDSM:SetupData(folderPath)
	oRDSM:SetupRequiredTables(requiredTables)  
	oRDSM:GetOpenTablesList()   
	tableArrayList := AClone(oRDSM:GetTableArrayList)
	userArrayList := AClone(oRDSM:GetUserArrayList)     
	tableCount := oRDSM:GetTableCount
	userCount := oRDSM:GetUserCount

	oRDSM:Destroy()
				
	END SEQUENCE
			
	oRDSM := NULL_OBJECT
	? 
	? "ADS Remote Table Monitoring"
	?  
	? "Tables list:"
	ShowArray(tableArrayList)
	? 
	? 
	? "User list:"
	ShowArray(userArrayList)
	?
	? 
	? "Number of tables: "
	?? tableCount
	? "Number of users: " 
	?? userCount
	?
	?
	WAIT
	RETURN	


CLASS ADSRemoteDbServerMonitor
	PROTECT tableArrayList AS ARRAY
	PROTECT userArrayList AS ARRAY
	PROTECT requiredTables AS ARRAY
	PROTECT tableCount, userCount AS DWORD
	PROTECT folderPath AS STRING

	DECLARE METHOD SetupData		
	DECLARE METHOD SetupRequiredTables
	DECLARE METHOD GetOpenTablesList
	
	DECLARE ACCESS GetTableArrayList
	DECLARE ACCESS GetUserArrayList
	DECLARE ACCESS GetTableCount
	DECLARE ACCESS GetUserCount

METHOD Destroy() 
	SELF:requiredTables := NULL_ARRAY
	SELF:tableArrayList := NULL_ARRAY
	SELF:userArrayList := NULL_ARRAY
		
	RETURN NIL
	 
METHOD GetOpenTablesList() AS VOID PASCAL 
	LOCAL hMgmtHandle AS System.IntPtr
	LOCAL ulRetVal AS WORD
	LOCAL usStructSizeTable,  usStructSizeUsers AS WORD
	LOCAL usArrayLenTable, usArrayLenUsers AS WORD
	LOCAL pbufferTable AS ADS_MGMT_TABLE_INFO
	LOCAL pbufferUsers AS ADS_MGMT_USER_INFO
	LOCAL tableName AS STRING
	LOCAL i, j AS DWORD
	LOCAL pos AS DWORD 
	LOCAL userName AS STRING  
	LOCAL lRequiredTables AS LOGIC
	
	BEGIN SEQUENCE
	
	tableArrayList := {}
	userArrayList := {}
	
	tableCount := 0
	userCount := 0
	ulRetVal := AdsMgConnect( SELF:folderPath, "", "", @hMgmtHandle ) 

	// If there was an error then show it and exit
	IF ulRetVal == AE_SUCCESS
	ELSE
		? "Could not connect to server." +CRLF +CRLF +"Server connection error!!!"
		BREAK
	ENDIF 

	usArrayLenTable := 250 
	usStructSizeTable := _SIZEOF( ADS_MGMT_TABLE_INFO )   
	pbufferTable := MemAlloc( usArrayLenTable * usStructSizeTable ) 
	
	ulRetVal := AdsMgGetOpenTables( hMgmtHandle, NULL, 0, pbufferTable, REF usArrayLenTable, REF usStructSizeTable)

	IF ulRetVal == AE_SUCCESS
	ELSE
		? "Could not determined number of tables currently open at the server located at: " +SELF:folderPath +CRLF +CRLF +"Server access error!!!"
		BREAK
	END
	
	IF requiredTables != NULL_ARRAY .AND. alen(requiredTables) > 0
		lRequiredTables := TRUE
	ENDIF
	
	FOR j:= 1 UPTO usArrayLenTable
		? tableName := Psz2String(@pbufferTable.aucTableName)
		IF lRequiredTables
			pos := AScan(requiredTables,{|x| Upper(x) == Upper(FileBase(tableName)) } ) //monitor only those table that matters!!!
		ELSE //no specific tables are monitored, always defualt to 1
			pos := 1
		ENDIF
		
		IF pos > 0 
			BEGIN SEQUENCE
				
				usArrayLenUsers := 250
				usStructSizeUsers := _SIZEOF(ADS_MGMT_USER_INFO)
				pbufferUsers := MemAlloc(usArrayLenUsers * usStructSizeUsers)
			
				ulRetVal := AdsMgGetUserNames( hMgmtHandle, tableName, pbufferUsers, REF usArrayLenUsers, REF usStructSizeUsers)
				
				IF ulRetVal <> AE_SUCCESS
					? "Could not determined number of users on table: " +tableName +CRLF +CRLF +"Table access error!!!"
					BREAK
				ENDIF		
				
				FOR i := 1 UPTO usArrayLenUsers
					userName := Psz2String(@pbufferUsers.aucUserName)
					IF AScan(userArrayList,userName) == 0
						AAdd(userArrayList,userName)  
						userCount += 1
					ENDIF   
					AAdd(tableArrayList,{FileBase(tableName),; //table filename without extension
												FilePath(tableName),; //full path
										 		userName,; //user name
										 		pbufferUsers.usConnNumber,;
										 		Psz2String(@pbufferUsers.aucAuthUserName),; //auth user name
										 		Psz2String(@pbufferUsers.aucAddress)} ) //address
					tableCount += 1
					pbufferUsers++					
				NEXT 
				
			END SEQUENCE
			
			IF pbufferUsers != NULL_PTR	
				MemFree(pbufferUsers)
			ENDIF
			
		ENDIF
		
		pbufferTable++
	
	
	NEXT
	
	END SEQUENCE   
	
	IF pbufferTable != NULL_PTR	
		MemFree(pbufferTable)
	ENDIF        
	
	IF pbufferUsers != NULL_PTR	
		MemFree(pbufferUsers)
	ENDIF        
		
	AdsMgDisconnect( hMgmtHandle )
	
	RETURN

ACCESS GetTableArrayList AS ARRAY PASCAL 
	RETURN SELF:tableArrayList
	
ACCESS GetTableCount AS DWORD PASCAL 
	RETURN SELF:tableCount
	
ACCESS GetUserArrayList AS ARRAY PASCAL 
	RETURN SELF:userArrayList
	
ACCESS GetUserCount AS DWORD PASCAL 
	RETURN SELF:userCount
		
METHOD SetupData(_folderPath AS STRING) AS VOID PASCAL 
	SELF:folderPath := _folderPath
	
	RETURN
	
METHOD SetupRequiredTables(_requiredTables AS ARRAY) AS VOID PASCAL 
	SELF:requiredTables := {}
	IF _requiredTables != NULL_ARRAY .AND. ( ALen(_requiredTables) > 0 ) 
		SELF:requiredTables := AClone(_requiredTables)
	ENDIF
	
	RETURN

END CLASS

FUNCTION FileBase( cFile AS STRING ) AS STRING PASCAL
	LOCAL nPos AS DWORD           	// Marks the position of the last "", if any
   LOCAL cFileBase AS STRING     // Return value containing the filename

   	DO CASE
   		CASE ( nPos := RAt( "", cFile )) != 0

      		// Strip out full path name leaving only the filename (with
      		// extension)
      		cFileBase := SubStr2( cFile, nPos + 1 )

   		CASE ( nPos := At( ":", cFile )) != 0

      		// Strip drive letter if cFile contains only drive letter
      		// no subdirectories
      		cFileBase := SubStr2( cFile, nPos + 1 )

   		OTHERWISE

      		// Assume it's already taken care of
      		cFileBase := cFile

   	ENDCASE

   	// Strip out the file extension, if any
   	IF ( nPos := At( ".", cFileBase )) != 0
    	cFileBase := SubStr3( cFileBase, 1, nPos - 1 )
   	ENDIF

   	RETURN ( cFileBase )

FUNCTION FilePath( cFile AS STRING ) AS STRING PASCAL

   LOCAL nPos AS DWORD// Marks the position of the last "" in cFile, if any
   LOCAL cFilePath AS STRING   // The extracted path for cFile, exluding the filename

   IF ( nPos := RAt( "", cFile )) != 0
      cFilePath := SubStr( cFile, 1, nPos )
   ELSE
      cFilePath := ""
   ENDIF

   RETURN cFilePath