info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



View & Log Active TCP/IP Connections
by BF (Principal Consultant; Architecture; Engineering)
2015-08-14







Create Table:

CREATE TABLE [dbo].[LOG_NETSTAT](
[Output] [varchar](1000) NULL,
[InsertedDate] [datetime] NULL CONSTRAINT [DF_LOG_NETSTAT_InsertedDate] DEFAULT (getdate())
) ON [PRIMARY]

GO


Create Stored Procedure:

Create Procedure [dbo].[spaLogNetStat]
as

/*
select * from dbo.Log_NetStat order by inserteddate desc
nslookup servername
*/

--Log Fully Qualified Domain name (sites, iis, etc)
declare @command2 varchar(1000)
declare @rc2 int
set @command2 = 'netstat -f '
insert dbo.LOG_NETSTAT(Output) exec @rc2 = master..xp_cmdshell @command2

--Log IP
declare @command varchar(1000)
declare @rc int
set @command = 'netstat -na '
insert dbo.LOG_NETSTAT(Output) exec @rc = master..xp_cmdshell @command


Create SQL Job:

Call Stored Procedure



Other Methods (Tools):

Microsoft Message Analyzer
WireShark
CurrPorts