1 Reply Latest reply on Mar 5, 2015 9:32 PM by gregf

    How many active users are in my database?

    Landon Winburn ITSMMVPGroup
      SQL script to query how many active users you have in your database. Replace domainname and 180 in the script accordingly.

      Select count(LoginName) from [User]
      --Replace domainname with your domain name and 180 with the number of days to query
      WHERE DomainName = 'domainname' and LastLogin >= DATEADD(day, -180, GETDATE())
        • 1. Re: How many active users are in my database?
          With an added column for the amount of space (excluding archives) those users are taking up in the database. Plus a column for that last logon date too:

          (screenshot from PSExport)

          SELECT u.Name, SUM(ap.StoredSize)/1024/1024 AS [Space Used in DB (MB)], u.LastLogin
          FROM ApplicationProfile ap
          INNER JOIN UserGroup ON ap.UserGroupFK = UserGroup.UserGroupPK
          INNER JOIN [User] u ON ap.UserFK = u.UserPK
          AND DomainName = 'domainname' 
          AND  DATEDIFF(d, u.LastLogin, GETDATE()) <= 180
          GROUP BY u.Name, u.LastLogin, ap.UserFK
          ORDER BY "Space Used in DB (MB)" DESC