Export list of active users from SQL

info@brianp.dk

Hi,

I've been asked to create a list of all user to see if there a being used. I can see in this forum that meny have asked for it as it's not made out of the box in the system. So I created a SQL script to find the users and give an nice overview of the user. But when looking at the "LastLoginTime" and "LastLogoutTime" it give me a weird number like "1563444162" can anyone help me how to convert this number?

  --Create a table to hold the query results
CREATE TABLE #mftusers
    (dbid smallint, dbname sysname, system_license varchar(99));

DECLARE @fixedname nvarchar(150);

--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
    SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
    FROM master.sys.databases
    WHERE state_desc = 'ONLINE'
	AND name like 'ServUmft_%'
    AND HAS_DBACCESS(name) = 1 
    AND name NOT IN ('master','msdb','model','tempdb');

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @fixedname;

@FETCH_STATUS = 0   
BEGIN   
       EXEC ('USE ' + @fixedname + ';' + 
      'SELECT
      [LastLoginTime]
      ,[LastLogoutTime]
      ,[Logins]
      ,[Logouts]
      ,[MostConcurrentLogins]
      ,[LoginID]
      ,[Organization]
      ,[FullName]
      ,[EmailAddress]
      ,[HomeDir]
  FROM
       [dbo].[SUUsers];');

       FETCH NEXT FROM dbCursor INTO @fixedname;
END;   

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #mftusers

DROP TABLE #mftusers

info@brianp.dk

I managed to find a solution to the timestamp, it is a Unix timestamp known as EPOCH, it is the time in seconds since 1970-01-01. I have converted it to date.

  --Create a table to hold the query results
CREATE TABLE #mftusers
    (dbid smallint, dbname sysname, system_license varchar(99));

DECLARE @fixedname nvarchar(150);

--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
    SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
    FROM master.sys.databases
    WHERE state_desc = 'ONLINE'
	AND name like 'ServUmft_%'
    AND HAS_DBACCESS(name) = 1 
    AND name NOT IN ('master','msdb','model','tempdb');

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @fixedname;

@FETCH_STATUS = 0   
BEGIN   
       EXEC ('USE ' + @fixedname + ';' + 
      'SELECT 
       dateadd(dd,LastLoginTime/60/60/24, ''19700101'') as Lastlogin
      ,dateadd(dd,LastLogOutTime/60/60/24, ''19700101'') as Lastlogout
      ,[Logins]
      ,[Logouts]
      ,[MostConcurrentLogins]
      ,[LoginID]
      ,[Organization]
      ,[FullName]
      ,[EmailAddress]
      ,[HomeDir]
  FROM
       [dbo].[SUUsers];');

       FETCH NEXT FROM dbCursor INTO @fixedname;
END;   

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #mftusers

DROP TABLE #mftusers