This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Export list of active users from SQL

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;

WHILE @@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

  • 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;
    
    WHILE @@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