select @ip=''
declare @num int
select @num=1
while(@num=1)
begin
SELECT @ip=b.[client_net_address]
FROM MASTER.sys.dm_exec_sessions a
INNER JOIN MASTER.sys.dm_exec_connections b
ON a.session_id=b.session_id
and a.[host_name]='0.0.0.0'
--and a.[host_name]='book3'
if @@rowcount>0
begin
print @ip
select @num=0
end
end
select b.[session_id],b.[login_time],b.[host_name],b.[original_login_name],a.[client_net_address],a.TEXT
from MASTER.sys.dm_exec_sessions b,
(
SELECT SESSION_ID,[client_net_address],TEXT
FROM
SYS.DM_EXEC_CONNECTIONS
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(MOST_RECENT_SQL_HANDLE)) a where a.SESSION_ID=b.SESSION_ID and a.TEXT like 'select%'
----------------------------------------------------------------------------------------------------------------------------
创建IP和主机名对应关系表
USE [xxxxxxx]
GO
CREATE TABLE [dbo].[audit_host](
[host_name] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL
) ON [PRIMARY]
GO
一秒钟执行一次新连接插入audit_host表
declare @num int
select @num=1
while(@num=1)
begin
insert into audit_host
select * from (SELECT
distinct a.[host_name],b.[client_net_address]
FROM MASTER.sys.dm_exec_sessions a WITH(NOLOCK)
INNER JOIN MASTER.sys.dm_exec_connections b WITH(NOLOCK)
ON a.session_id=b.session_id) c where not exists (select 1 from audit_host d where c.[host_name]=d.[host_name] and c.[client_net_address]=d.[client_net_address])
WAITFOR DELAY '00:00:01'
end