GP User Activity with Status and IP

If you've ever needed to clear out old GP users, you know it can sometimes be difficult to figure out which ones are still connected and which ones are not. Yes, the ones that logged in two days ago may seem the obvious choices to kick out, but that's not always the case. We've seen users leave GP open for days on end to not lose their seat and their SQL session is hot, and we've seen users open GP in the morning but bombed out an hour later but didn't need it anymore so they didn't bother to log in to clear their seat.

Either way, this script below combines several GP and SQL system tables and views to give you a broad view of the status of your GP users. It will show whether users in User Activity are in fact still connected to GP, how long they've been logged into GP, when their last SQL activity occurred and how long ago it was, their computer's IP address, if they are working on batches, and what table they are currently working in.

I'm sure there is still some room for improvement, and I will be adding to it from time to time, so check back later for any updates.

USE DYNAMICS
SELECT 
    Activity.USERID UserID, 
    Users.USERNAME Username, 
    Activity.CMPNYNAM Company,
    CASE
        WHEN AbandonedSessions.SQLSESID IS NULL
            THEN 'Connected'
        ELSE 'Removable'
    END Status,
    CONVERT(varchar(20),ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) LoggedInAt,
    ISNULL(CONVERT(varchar(20),ConnectedSessions.last_batch),'') LastGPUsage,
    CASE WHEN GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))-1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 's '
    END    TimeSinceLogin,
    CASE 
        WHEN ConnectedSessions.last_batch IS NULL 
            THEN ''
        WHEN GETDATE()-ConnectedSessions.last_batch < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-ConnectedSessions.last_batch)- 1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-ConnectedSessions.last_batch)) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-ConnectedSessions.last_batch)) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-ConnectedSessions.last_batch)) + 's '
    END GPIdleTime,
    ISNULL(lock.table_path_name,'') OpenTable,
    ISNULL(batch.BatchCount,0) BatchesInProgress,
    Activity.SQLSESID SessionID,
    ISNULL(CONVERT(varchar(10),ConnectedSessions.SPID),'') SPID,
    ISNULL(ConnectedSessions.client_net_address,'') IPAddress
FROM 
    DYNAMICS..ACTIVITY Activity
    INNER JOIN DYNAMICS..SY01400 Users 
    ON Activity.USERID = Users.USERID
LEFT JOIN
    (
        SELECT *
        FROM DYNAMICS..Activity 
        WHERE SQLSESID NOT IN (
            SELECT SQLSESID
            FROM DYNAMICS..Activity A
            INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
            INNER JOIN master..sysprocesses P
            on S.sqlsvr_spid = P.spid
            AND A.USERID = P.loginame
        )
        AND NOT EXISTS
            (SELECT spid, c.CMPNYNAM DB, loginame,*
            FROM master..sysprocesses p
            inner join master..sysdatabases d
                on p.dbid = d.dbid
            inner join DYNAMICS..SY01500 c
                on d.name = RTRIM(c.INTERID)
            where c.CMPNYNAM = Activity.CMPNYNAM
                and p.loginame = Activity.USERID
                and p.program_name = ''
            )
    ) AbandonedSessions
    ON Activity.SQLSESID = AbandonedSessions.SQLSESID
    AND Activity.USERID = AbandonedSessions.USERID
LEFT JOIN
    (
        SELECT 
            A.SQLSESID, 
            P.spid, 
            P.last_batch, 
            ec.client_net_address, 
            CASE 
                WHEN P.login_time < A.LOGINDAT + A.LOGINTIM 
                    THEN p.login_time 
                ELSE A.LOGINDAT + A.LOGINTIM 
            END last_login
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
            ON S.sqlsvr_spid = P.spid
        AND A.USERID = P.loginame
        INNER JOIN sys.dm_exec_connections ec
            ON S.sqlsvr_spid = ec.session_id
    ) ConnectedSessions
    ON Activity.SQLSESID = ConnectedSessions.SQLSESID
LEFT JOIN
    (
        SELECT session_id, max(table_path_name) table_path_name
        FROM tempdb..DEX_LOCK
        GROUP BY session_id
    ) lock
    ON Activity.SQLSESID = lock.session_id
LEFT JOIN
    (
        SELECT
            USERID,
            CMPNYNAM,
            COUNT(*) BatchCount
        FROM
            Dynamics..SY00800
        GROUP BY
            USERID,
            CMPNYNAM
    ) batch
    ON Activity.USERID = batch.USERID
    and Activity.CMPNYNAM = batch.CMPNYNAM
ORDER BY 
    Activity.LOGINDAT,
    Activity.LOGINTIM

Check Disk Space script

Part of normal maintenance of a Dynamics GP SQL server is to make sure there is enough disk space for your data as it grows. Similarly, you need to make sure your data files aren't growing more than they should.

This script draws from several system tables to show the recovery model, the last backup of the data or log file, the size of the file, the growth of the file the next time it runs out of room, the drive on which the file is stored, and the free space of the drive.

I included the Recovery Model and LastSQLBackup columns to show the situation where FULL recovery is used, but no Transaction Log backups are taking place. This situation causes the Transaction Log to grow unnecessarily until it fills the hard drive. If you do not back up the Transaction Log, having FULL recovery is both useless and dangerous to your data at the same time; dangerous because it can bottom out your hard drive while transactions are being posted, and useless because the log file hasn't been backed up to be able to use in a restore which is the whole point of having it in the first place.

-- Shows database file space usage and free disk space
USE [master]

IF EXISTS (SELECT * FROM sys.tables WHERE name='#cbs_drivespace')
    DROP TABLE #cbs_drivespace

IF EXISTS (SELECT * FROM sys.tables WHERE name='##cbs_db_sizes')
    DROP TABLE ##cbs_db_sizes

CREATE TABLE #cbs_drivespace
(
    Drive varchar(10), 
    MB_Free numeric(19,2)
)

CREATE TABLE ##cbs_db_sizes
(
    dbname varchar(100),
    filetype varchar(4),
    size_in_gb numeric(19,2),
    growth_in_percent numeric(19,2),
    growth_in_gb numeric(19,2)
)

DECLARE @SQL varchar(4000)
declare @DatabaseId varchar(255)

INSERT INTO #cbs_drivespace(Drive, MB_Free)
EXEC master.dbo.xp_fixeddrives

declare DatabaseLoop cursor for 
    select name from master..sysdatabases where name in 
        (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
        or name in ('master','msdb','tempdb')

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId
while (@@fetch_status <> -1)
    begin

    /*Inner Loop for each Database*/
    SET @SQL =
        'INSERT INTO ##cbs_db_sizes '
        + 'SELECT ''' + @DatabaseId + ''' AS dbname, '
        + 'CASE WHEN RIGHT(RTRIM(filename),3) = ''mdf'' THEN ''Data'' ELSE ''Log'' END AS filetype, '
        + 'CONVERT(numeric(19,2),size * 8 / 1024. / 1024) AS size_in_gb, '
        + 'CONVERT(numeric(19,2),CASE WHEN status & 0x100000 > 0 THEN growth ELSE (growth * 100.) / (size * 1.) END) AS growth_in_percent, '
        + 'CONVERT(numeric(19,2),CASE WHEN status & 0x100000 = 0 THEN growth * 8 / 1024. / 1024 ELSE size * (growth / 100.) * 8 / 1024 / 1024 END) AS growth_in_gb '
        + 'FROM ' + @DatabaseId + '..sysfiles'
    EXEC (@SQL)
    fetch next from DatabaseLoop into @DatabaseId 
    end

close DatabaseLoop
deallocate DatabaseLoop

SELECT
    db.name as DBName,
    db.dbid as DBID,
    DatabasePropertyEX(db.name, 'Recovery') as RecoveryModel,
    s.filetype as FileType,
    CASE 
        WHEN s.filetype = 'Data' 
            THEN ISNULL(CONVERT(varchar(15),bu.LastBU,101),'Never')
        WHEN s.filetype = 'Log' AND DatabasePropertyEX(db.name, 'Recovery') = 'SIMPLE'
            THEN 'N/A'
        ELSE
            ISNULL(CONVERT(varchar(15),bu.LastBU,101),'Never')
    END LastSQLBackup,
    s.size_in_gb as SizeInGB,
    s.growth_in_gb as GrowthInGB,
    s.growth_in_percent as GrowthInPercent,
    ds.Drive as DriveLetter,
    CONVERT(numeric(19,2),ds.MB_Free / 1024) DriveGBFree
FROM
    sysdatabases db
INNER JOIN
    ##cbs_db_sizes s
    ON db.name = s.dbname
INNER JOIN
    #cbs_drivespace ds
    ON LEFT(db.filename,1) = ds.Drive
LEFT JOIN
    (
        SELECT
            a.Name as dbname,
            COALESCE(Convert(varchar(100), MAX(b.backup_finish_date), 101),'Backup Not Taken') as LastBU,
            b.type as BUType,
            CASE 
                WHEN b.type = 'D' THEN 'Data'
                WHEN b.type = 'L' THEN 'Log'
            END as BUFileMatch
        FROM 
            sysdatabases a 
        LEFT OUTER JOIN 
            msdb.dbo.backupset b
            ON b.database_name = a.name
        WHERE
            b.type IN ('D','L')
        GROUP BY 
            a.Name,
            b.type
    ) bu
    ON s.dbname = bu.dbname
    AND s.filetype = bu.BUFileMatch
ORDER BY
    DBName,FileType

DROP TABLE #cbs_drivespace
DROP TABLE ##cbs_db_sizes

Copy Live to Test company - version 4

I've updated my script since my original post back in 2011 that is used to refresh a GP test company with live data.

This tried and tested script will perform the following:

• Back up the current test database (optional; on by default)
• Back up the live database (optional; on by default)
• Restore the live backup (or, optionally, any other SQL backup)
• Run the CreateTestCompany (Microsoft) script
• Set the output settings to print to screen instead of a printer (optional; on by default)
• Change the Recovery Mode to Simple, and shrink the log file

DECLARE
        @SourceDatabase varchar(10),
        @TESTDatabase varchar(10),
        @DatabaseFolder varchar(1000),
        @LogFolder varchar(1000),
        @BackupFolder varchar(1000),
        @BackupFilename varchar(100),
        @TESTBackupFilename varchar(100),
        @RestoreFile varchar(100),
        @RestoreToDataFolder varchar(100),
        @RestoreToLogFolder varchar(100),
        @UseRestoreFile smallint,
        @UseRestoreToFolders smallint,
        @CompressionAllowed smallint,
        @Compress smallint,
        @BackupTESTFirst smallint,
        @LogicalFilenameDB varchar(100),
        @LogicalFilenameLog varchar(100),
        @SetPrintToScreen smallint,
        @SQL varchar(max)
-----------------------------------------------------------------------
-- Set up all the information here for the live and test database names
-----------------------------------------------------------------------

SET        @SourceDatabase =        'TWO'
USE                                [TWO]    -- enter the Source Database between the braces
SET        @TESTDatabase =            'TEST'
SET        @BackupFolder =            'F:\DB Backups\' --end with a backslash (\)
--                                        Folder where you want to save the backup file
SET        @Compress =                1    --  0 = Do not compress backups; 1 = Compress backups
--                                        2008 and forward; SQL Express and some Standard editions do not allow compression
SET        @BackupTESTFirst =        1     --    0 = No; 1 = Yes
--                                        Backup the TEST database before restoring
--                                        You should do this the first time you restore per day
SET        @UseRestoreFile =        0    --    0 = Create a backup and restore it
--                                        1 = Use the below filename to restore from instead of creating new backup
--                                        Use this when you just want to reload a backup you already created
SET        @RestoreFile =            'TWO_20130403.bak'
--                                        File to restore if you have already created a backup of the live company
SET        @UseRestoreToFolders =    1    --    0 = Restore to the same folder as the live database; 
--                                        1 = Restore to the folders specified below
SET        @RestoreToDataFolder =    'G:\SQL Data TEST\' --end with a backslash (\)
--                                        Folder where you want to restore the .mdf (Data) file TO
SET        @RestoreToLogFolder =    'H:\SQL Log TEST\' --end with a backslash (\)
--                                        Folder where you want to restore the .ldf (Log) file TO
SET        @SetPrintToScreen =        1  --    0 = No; 1 = Yes
--                                         Change the posting output to the screen instead of the printer
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT @CompressionAllowed =    CONVERT(smallint,ISNULL((SELECT value FROM sys.configurations WHERE name = 'backup compression default'),-1))
SELECT @LogicalFilenameDB =        (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 1
SELECT @LogicalFilenameLog =    (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 0

IF @UseRestoreToFolders = 1
    BEGIN
        SET @DatabaseFolder = @RestoreToDataFolder
        SET @LogFolder = @RestoreToLogFolder
        print 'Using specified data folder ' + @DatabaseFolder + ' and log folder ' + @LogFolder
    END
ELSE
    BEGIN
        SELECT TOP 1 @DatabaseFolder =    left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 1
        SELECT TOP 1 @LogFolder =        left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 0
        print 'Using live database''s data folder ' + @DatabaseFolder + ' and log folder ' + @LogFolder
    END

print 'Compression: ' + CASE WHEN @Compress = 1 AND @CompressionAllowed = -1 THEN 'NOT ALLOWED' WHEN @Compress = 1 THEN 'ON' ELSE 'OFF' END
print @LogicalFilenameDB
print @LogicalFilenameLog
print @DatabaseFolder

SET    @SQL = ''

IF @UseRestoreFile = 0
    BEGIN
        SET    @BackupFilename = @BackupFolder + @SourceDatabase +
            '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
            '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
            '_for_' + @TESTDatabase + '.bak'
        END
    ELSE
        BEGIN
            SET @BackupFilename = @BackupFolder + @RestoreFile
        END

print '.'
print 'Backup Filename: ' + @BackupFilename
print '.'

SET @TESTBackupFilename = @BackupFolder + @TESTDatabase +
    '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
    '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
    '_pre_LIVE_restore.bak'

-- check to see if the TEST database is in use first
IF EXISTS(SELECT spid,loginame=rtrim(loginame),hostname,program_name,login_time,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
        WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
        = @TESTDatabase)
    BEGIN
        PRINT 'The database is in use'
        SELECT spid,loginame=rtrim(loginame),hostname,program_name,login_time,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
            WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
            = @TESTDatabase
    END
ELSE
    BEGIN
        IF @UseRestoreFile = 0
        BEGIN

        -- Back up the LIVE database
            SET @SQL = '' +
                'PRINT ''Backing up Source database (' + @SourceDatabase + ')''; ' +
                'BACKUP DATABASE [' + @SourceDatabase + '] TO DISK ' +
                '= N''' + @BackupFilename + ''' ' + 
                'WITH COPY_ONLY, NOFORMAT, NOINIT, NAME ' +
                '= N''' + @SourceDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                IF @Compress = 1 AND @CompressionAllowed > -1
                    SET @SQL = @SQL + ', COMPRESSION'

            EXEC (@SQL)
        END

        -- Back up the TEST database
        IF @BackupTESTFirst = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Backing up Destination database (' + @TESTDatabase + ')''; ' +
                    'BACKUP DATABASE [' + @TESTDatabase + '] TO DISK ' +
                    '= N''' + @TESTBackupFilename + ''' ' + 
                    'WITH COPY_ONLY, NOFORMAT, NOINIT, NAME ' +
                    '= N''' + @TESTDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                IF @Compress = 1 AND @CompressionAllowed > -1
                    SET @SQL = @SQL + ', COMPRESSION'
                
                EXEC (@SQL)
            END

        -- Restore to the TEST database
        SET @SQL = '' +
            'PRINT ''Restoring to Destination database (' + @TESTDatabase + ')''; ' +
            'RESTORE DATABASE [' + @TESTDatabase + '] FROM DISK ' +
            '= N''' + @BackupFilename + ''' ' +
            'WITH FILE = 1, ' +
            'MOVE N''' + @LogicalFilenameDB + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Dat.mdf'', ' +
            'MOVE N''' + @LogicalFilenameLog + ''' TO ' +
            'N''' + @LogFolder + 'GPS' + @TESTDatabase + 'Log.ldf'', ' +
            'NOUNLOAD, REPLACE, STATS = 10'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Setting Recovery Model to Simple''; ' +
            'ALTER DATABASE [' + @TESTDatabase + '] SET RECOVERY SIMPLE WITH NO_WAIT'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Shrinking Log File'' ' +
            'USE [' + @TESTDatabase + '] ' +
            'DBCC SHRINKFILE (N''' + @LogicalFilenameLog + ''' , 0, TRUNCATEONLY)'
        EXEC (@SQL)

        IF @SetPrintToScreen = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Setting output to print to screen instead of printer'' ' +
                    'USE [' + @TESTDatabase + '] ' +
                    'UPDATE SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0'
                EXEC (@SQL)
            END

        -- Run the CreateTestCompany script from MS
        SET @SQL = '' +
            'PRINT ''Running CreateTestCompany script on ' + @TESTDatabase + '''; ' +
            'USE [' + @TESTDatabase + '] ' +
            'if not exists(select 1 from tempdb.dbo.sysobjects where name = ''##updatedTables'') ' +
            ' create table [##updatedTables] ([tableName] char(100)) ' +
            'truncate table ##updatedTables ' +
            'declare @cStatement varchar(255) ' +
            'declare G_cursor CURSOR for ' +
            'select ' +
            'case ' +
            'when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'') ' +
            ' then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) ' +
            'else ' +
            '''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' ' +
            'end ' +
            'from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c ' +
            'where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'', ''COMPANYCODE_I'') ' +
            'and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ''BASE TABLE''; ' +
            'set nocount on; ' +
            'OPEN G_cursor; ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'WHILE (@@FETCH_STATUS <> -1) ' +
            'begin ' +
            'insert ##updatedTables select ' +
            'substring(@cStatement,8,patindex(''%set%'',@cStatement)-9) ' +
            'Exec (@cStatement) ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'end ' +
            'DEALLOCATE G_cursor ' +
            'select [tableName] as ''Tables that were Updated'' from ##updatedTables '
        EXEC (@SQL)
            PRINT 'Don''t forget to run GP SQL Maintenance on the ' + @TESTDatabase + ' database.'
    END

Version 4 changes:

• Specify the restore folder for the data file (.mdf)
• Specify the restore folder for the log file (.ldf)

Version 3 changes:

• Backup compression (optional; on by default)
• Uses the "Copy Only" switch - so it does not mark the database as backed up - useful when using certain other backup software as the primary backup

Version 2 changes:

• Messages window shows the auto-generated backup filename of the LIVE database for use in the optional restore feature below
• Restores a backup you already created (optional)
• Changes the posting output of the TEST company in GP to Screen instead of Printer (optional)
• Changes the Recovery Model to Simple and shrinks the log file of the TEST database

Version 1

• Backs up the TEST database (optional)
• Backs up the LIVE database and restore to the TEST database
• Runs CreateTestCompany script from PartnerSource against the TEST database