As chance would have it, I had been checking Adam’s blog daily for the last few days to find the next T-SQL Tuesday. Not having seen it, I started working on my next post yesterday evening. The fortunate thing is that the post I was working on fits well with this months topic. T-SQL Tuesday #005 is being hosted by Aaron Nelson. And now I have one more blog to bookmark since I didn’t have his bookmarked previously.
This month the topic is Reporting. Reporting is a pretty wide open topic but not nearly as wide open as the previous two months. Nonetheless, I think the topic should be handled pretty easily by most of the participants. My post deals with reporting on User connections. I have to admit that this one really just fell into my lap as I was helping somebody else.
The Story
Recently I came across a question on how to find the IP address of a connection. From experience I had a couple of preconceptions on what could be done to find this information. SQL server gives us a few avenues to be able to find that data. There are also some good reasons to try and track this data as well. However, the context of the question was different than I had envisioned it. The person already knew how to gain the IP address and was already well onto their way with a well formed query. All that was needed was just the final piece of the puzzle. This final piece was to integrate sys.dm_exec_connections with the existing query which pulls information from trace files that exist on the server.
After exploring the query a bit, it also became evident that other pertinent information could prove quite useful in a single result set. The trace can be used to pull back plenty of useful information depending on the needs. Here is a query that you can use to explore that information and determine for yourself the pertinent information for your requirements, or if the information you seek is even attainable through this method.
|
1 2 3 4 5 6 7 8 |
select * FROM sys.traces T CROSS Apply ::fn_trace_gettable( CASE WHEN CHARINDEX( '_',T.[path]) <> 0 THEN SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc' ELSE T.[path] End, T.max_files) |
Now, I must explain that there is a problem with joining the trace files to the DMVs. The sys.dm_exec_connections maintains current connection information as does the sys.dm_exec_sessions DMV. Thus mapping the trace file to these DMV’s could be problematic if looking for historical information. So now the conundrum is really how to make this work. So that the data returned in the report, some additional constraints would have to be placed on the query. But let’s first evaluate the first go around with this requirement.
Query Attempts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT I.NTUserName,I.loginname,I.SessionLoginName,I.databasename ,EC.client_net_address as SourceIPAddress,I.HostName, I.ApplicationName ,Min(I.StartTime) as ConnectionStart,Max(I.StartTime) as ConnectionEnd ,S.principal_id,S.sid,S.type_desc,S.name FROM sys.traces T CROSS Apply ::fn_trace_gettable( CASE WHEN CHARINDEX( '_',T.[path]) <> 0 THEN SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc' ELSE T.[path] End, T.max_files) I LEFT Outer Join sys.server_principals S ON CONVERT(VARBINARY(MAX), I.loginsid) = S.sid Left Outer Join sys.dm_exec_connections EC On EC.session_id = I.SPID WHERE T.id = 1 And I.LoginSid is not null Group By I.NTUserName,I.loginname,I.SessionLoginName,I.databasename,EC.client_net_address,I.HostName,I.ApplicationName ,I.StartTime,S.principal_id,S.sid,S.type_desc,S.name Having datediff(dd, Max(I.StartTime), getdate()) <= 30 Order By databasename, loginname, I.StartTime |
While evaluating this query, one may spot the obvious problem. If not seen at this point, a quick execution of the query will divulge the problem. SPIDs are not unique, they are re-used. Thus when querying historical information against current running information, one is going to get inaccurate results. Essentially, for this requirement we have no better certain knowledge what the IP Address would be for those connections showing up in the trace files. The IP Addresses of the current connections will cross populate and render inaccurate results from the historical information.
My next step gets us a little closer. I decided to include more limiting information in the Join to the sys.dm_exec_connections view. The way I determined to do this was that I needed to also include the loginname as a condition. Thus in order to get that, I need to include sys.dm_exec_sessions in my query. To make it all come together, I pulled that information into a CTE. Here is the new version.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
With ConnectInfo as ( Select ec.session_id, es.login_time,es.login_name,ec.client_net_address From sys.dm_exec_connections ec Inner Join sys.dm_exec_sessions es On ec.session_id = es.session_id ) SELECT I.NTUserName,I.loginname,I.SessionLoginName,I.databasename ,EC.client_net_address as SourceIPAddress,I.HostName, I.ApplicationName ,Min(I.StartTime) as ConnectionStart,Max(I.StartTime) as ConnectionEnd ,S.principal_id,S.sid,S.type_desc,S.name FROM sys.traces T CROSS Apply ::fn_trace_gettable( CASE WHEN CHARINDEX( '_',T.[path]) <> 0 THEN SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc' ELSE T.[path] End, T.max_files) I LEFT Outer Join sys.server_principals S ON CONVERT(VARBINARY(MAX), I.loginsid) = S.sid Left Outer Join ConnectInfo EC On EC.session_id = I.SPID And EC.login_name = I.LoginName WHERE T.id = 1 And I.LoginSid is not null Group By I.NTUserName,I.loginname,I.SessionLoginName,I.databasename,EC.client_net_address,I.HostName,I.ApplicationName ,I.StartTime,S.principal_id,S.sid,S.type_desc,S.name Having datediff(dd, Max(I.StartTime), getdate()) <= 30 Order By databasename, loginname, I.StartTime |
The information pulled back is much cleaner now. But wait, this mostly reflects current connections or connections from the same person who happens to have the same SPID as a previous time that person connected. Yes, an inherent problem with combining historical information to the current connection information in the server.
Solution
My recommendation to solve this need for capturing IP address information along with the person who connected, their computer hostname, and the time that they connected is to do so pre-emptively. (I know this diverges from the report for a minute, but is necessary to setup for the report). A solution I have implemented in the past is to use a logon trigger that records the pertinent information to a Table.
Tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AuditLogonViolation]( [LogonVID] [int] IDENTITY(1,1) NOT NULL, [Host] [varchar](30) NULL, [ViolationTime] [datetime] NULL, [ServerName] [varchar](30) NULL, [LoginName] [varchar](30) NULL, [SIDValue] [varchar](30) NULL, [ClientHost] [varchar](30) NULL, [ErrorMessage] [varchar](100) NULL, CONSTRAINT [PK__AuditLogonViolation] PRIMARY KEY CLUSTERED ( [LogonVID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[AuditLogonEvent]( [LogonEventID] [int] IDENTITY(1,1) NOT NULL, [Host] [varchar](30) NULL, [EventType] [varchar](100) NULL, [EventTime] [datetime] NULL, [SPID] [smallint] NULL, [ServerName] [varchar](30) NULL, [LoginName] [varchar](30) NULL, [LoginType] [varchar](30) NULL, [SID] [varchar](30) NULL, [ClientHost] [varchar](30) NULL, CONSTRAINT [PK_LogonEventID] PRIMARY KEY CLUSTERED ( [LogonEventID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SecData] ) ON [SecData] GO SET ANSI_PADDING OFF GO |
Trigger
|
1 2 3 4 5 6 7 8 |