2 Replies Latest reply on Apr 15, 2013 1:49 PM by niterave

    Who Logged Into This Server?

    niterave

      Hey Everyone,

       

      Thanks in advance for taking a look at my question.  Hopefully this is an easy one and I just can't find the information that is already there.....

       

      So, in UDT it is pretty darn easy to find out where a specific user logged in.  Click the username in the "All User Logins" pane, and viola! you have a list of everywhere the user logged in going back a month in my case.

       

      What I need to do is get a list of all users that logged into a specific endpoint.  That seems like it should be pretty easy since all the information should already be there.  I just can't seem to be able to figure it out.  So I'm hoping that A) This information is already there, I just need to know where to look (what click sequence I need to follow to pull it up), or B) This is a pretty easy query/filter to write and someone has already done and would be kind enough to share.

       

      Thanks again,
      Matt

        • Re: Who Logged Into This Server?
          michalB

          I think the query below might work for you. You can use it to create a report that can be displayed on the web. Either use the WHERE clause, or remove the line and select grouping by the IP Address in the Report Writer. Please note that this approach is not officially supported and the database schema is a subject to change, so the report may need a redesign with any upgrade to a newer version.

           

          select UDT_User.AccountName, UDT_UserToIPAddressHistory.LogonDateTime, UDT_UserToIPAddressHistory.IPAddress

          from dbo.UDT_UserToIPAddressHistory

          join UDT_User on UDT_User.UserID = UDT_UserToIPAddressHistory.UserID

           

          where UDT_UserToIPAddressHistory.IPAddress = '10.199.2.92'

           

          order by UDT_UserToIPAddressHistory.LogonDateTime

           

           

          This one should also include current connections (again, you can remove the two WHERE clauses and add grouping by IP Address in Report Writer):

          SELECT DISTINCT userLatestLogin.RecentLogin AS [LogonDateTime], userLatestLogin.IPAddress, u.AccountName

          FROM UDT_User u

          INNER JOIN

          (     SELECT MAX(uip.LogonDateTime) as [RecentLogin], uip.UserID,uip.IPAddress

            FROM (

            SELECT u2ip.LogonDateTime, u2ip.UserID, u2ip.IPAddress

            FROM UDT_UserToIPAddressCurrent u2ip

          WHERE u2ip.IPAddress = '10.199.2.92' 

          union select u2iph.LogonDateTime, u2iph.UserID,u2iph.IPAddress

            FROM UDT_UserToIPAddressHistory u2iph

          WHERE u2iph.IPAddress = '10.199.2.92' ) uip

            GROUP BY uip.UserID,uip.IPAddress ) userLatestLogin

                                              ON u.UserID = userLatestLogin.UserID

           

                                              ORDER BY [LogonDateTime] DESC