if object_id('p_getlinkinfo','P')is not null drop proc p_getlinkinfo go create proc p_getlinkinfo @dbname sysname=null, --要查詢的數據庫名,默認表示所有 @includeip bit=0 --是否顯示IP信息 as begin declare @dbid int set @dbid=db_id(@dbname) if object_id('tempdb..#tb')is not null drop table #tb if object_id('tempdb..#ip')is not null drop table #ip create table #tb (id int identity(1,1), dbname sysname, hostname nchar(128), loginname nchar(128), net_address nchar(12), net_ip nvarchar(15), prog_name nchar(128)) insert into #tb(hostname,dbname,net_address,loginname,prog_name) select distinct hostname, db_name(dbid), net_address, loginame, program_name from master..sysprocesses where hostname!=''and(@dbid is null or dbid=@dbid) if @includeip=0 goto lb_show --不顯示IP declare @sql varchar(500),@hostname nchar(128),@id int create table #ip(hostname nchar(128),a varchar(200)) declare tb cursor local for select distinct hostname from #tb open tb fetch next from tb into @hostname while @@fetch_status=0 begin set @sql='ping '+@hostname+' -a -n 1 -l 1' insert #ip(a) exec master..xp_cmdshell @sql update #ip set hostname=@hostname where hostname is null fetch next from tb into @hostname end update #tb set net_ip=left(a,patindex('%:%',a)-1) from #tb a inner join (select hostname,a=substring(a,patindex('Ping statistics for %:%',a)+20,20) from #ip where a like'Ping statistics for %:%')b on a.hostname=b.hostname lb_show: select id, dbname, hostname, loginname, net_address, net_ip, prog_name from #tb end go exec p_getlinkinfo @dbname='DB_WIP',@includeip=1
请高手帮助,如何能查找历史的SQL连接记录,能查访问的IP更好。
只能查找active user connectionsSELECT connection_id, c.session_id, connect_time, client_net_address, client_tcp_port, host_name, program_name, login_name, row_count FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id 如果要查历史,create table connection_log (XXXXXXXXXXXXX) 可以在程序打开一个连接后, INSERT connection_log SELECT connection_id, c.session_id, connect_time, client_net_address, client_tcp_port, host_name, program_name, login_name, row_count FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id
一般都是借用xp_cmdshell用ping获取返回的信息来获得ip的
但是这个也是有极大的制约比如有防火墙或者广域网的情况下那是没办法的还有跨平台操作也是个问题
更何况名字和ip也不一定是一对一的关系
go
create proc p_getlinkinfo
@dbname sysname=null, --要查詢的數據庫名,默認表示所有
@includeip bit=0 --是否顯示IP信息
as
begin
declare @dbid int
set @dbid=db_id(@dbname)
if object_id('tempdb..#tb')is not null drop table #tb
if object_id('tempdb..#ip')is not null drop table #ip
create table #tb
(id int identity(1,1),
dbname sysname,
hostname nchar(128),
loginname nchar(128),
net_address nchar(12),
net_ip nvarchar(15),
prog_name nchar(128))
insert into #tb(hostname,dbname,net_address,loginname,prog_name)
select distinct hostname,
db_name(dbid),
net_address,
loginame,
program_name
from master..sysprocesses
where hostname!=''and(@dbid is null or dbid=@dbid)
if @includeip=0 goto lb_show --不顯示IP
declare @sql varchar(500),@hostname nchar(128),@id int
create table #ip(hostname nchar(128),a varchar(200))
declare tb cursor local for select distinct hostname from #tb
open tb
fetch next from tb into @hostname
while @@fetch_status=0
begin
set @sql='ping '+@hostname+' -a -n 1 -l 1'
insert #ip(a) exec master..xp_cmdshell @sql
update #ip set hostname=@hostname where hostname is null
fetch next from tb into @hostname
end
update #tb set net_ip=left(a,patindex('%:%',a)-1)
from #tb a inner join
(select hostname,a=substring(a,patindex('Ping statistics for %:%',a)+20,20)
from #ip
where a like'Ping statistics for %:%')b
on a.hostname=b.hostname
lb_show:
select id,
dbname,
hostname,
loginname,
net_address,
net_ip,
prog_name
from #tb
end
go
exec p_getlinkinfo @dbname='DB_WIP',@includeip=1
c.session_id,
connect_time,
client_net_address,
client_tcp_port,
host_name,
program_name,
login_name,
row_count
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id
如果要查历史,create table connection_log (XXXXXXXXXXXXX)
可以在程序打开一个连接后,
INSERT connection_log
SELECT connection_id,
c.session_id,
connect_time,
client_net_address,
client_tcp_port,
host_name,
program_name,
login_name,
row_count
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id