sqlport : 用SQL Server檢測進程和端口的映射關係

set nocount on
go

create table #cmd_output (seqno int identity(1,1), value1 nvarchar(512))
create table #tasklist(image_name varchar(30), pid int, session_name varchar(100),
  session_id varchar(10), mem_usage varchar(20))
create table #netstat(proto varchar(5), local_addr varchar(50), remote_addr varchar(50), state varchar(30), pid int)

-- handle tasklist output
insert into #cmd_output (value1) exec master..xp_cmdshell 'tasklist'
declare @cnt int, @line varchar(1000)
declare @image_name varchar(30), @pid int, @session_name varchar(100),
  @session_id varchar(10), @mem_usage varchar(20)
select @cnt = 5, @line = '1'
while @line is not null
begin
  select @line=value1 from #cmd_output where seqno=@cnt
  exec master..xp_sscanf @line, '%s %d %s %s %sK',
     @image_name OUTPUT, @pid OUTPUT, @session_name OUTPUT, @session_id OUTPUT, @mem_usage OUTPUT
  insert #tasklist select @image_name, @pid, @session_name, @session_id, @mem_usage+' K'
  set @cnt = @cnt + 1
end

truncate table #cmd_output

-- handle netstat output
insert into #cmd_output (value1) exec master..xp_cmdshell 'netstat -ao'
declare @proto varchar(5), @local_addr varchar(50), @remote_addr varchar(50), @state varchar(30), @pid2 int
select @cnt = 5, @line = 'abcd'
while @line is not null
begin
  select @line=value1 from #cmd_output where seqno=@cnt
  if charindex('*:*', @line) > 0
  begin
    exec master..xp_sscanf @line, '%s %s %s %d',
       @proto OUTPUT, @local_addr OUTPUT,  @remote_addr OUTPUT, @pid2 OUTPUT
    insert #netstat select @proto, @local_addr, @remote_addr, 'UDP/UNKNOWN', @pid2
  end
  else
  begin
    exec master..xp_sscanf @line, '%s %s %s %s %d',
       @proto OUTPUT, @local_addr OUTPUT, @remote_addr OUTPUT, @state OUTPUT, @pid2 OUTPUT
    insert #netstat select @proto, @local_addr, @remote_addr, @state, @pid2
  end
  set @cnt = @cnt + 1
end

--select * from #cmd_output order by 1 ,2
--select * from #tasklist
select A.*, B.image_name from #netstat A left join #tasklist B on A.pid = B.pid where proto is not null

drop table #cmd_output, #tasklist, #netstat
go

set nocount off
go