Who Logged Into This Server?

Question

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

michalB · Accepted Answer

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