quote:

---

Originally posted by AskSQLTeam

Raed writes "Hi,

How can I know the IP address of each user logged in my server or tried to either through applications, Query Analyzer, Enterprize or any way else.

Thaks,"

---

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
/* 
ALL SPIDS to IP
2005-01-04: Daniel_Janik@yahoo.com, Dallas, Tx
*/

DECLARE @SPID as int,
	@MAXSPID as int,
	@MAC as varchar(12),
	@MACDisplay as varchar(18),
	@IP as varchar(15),
	@CMD as varchar(100)

SET @MAXSPID = (SELECT MAX(SPID) FROM master..sysprocesses)
SET @SPID = (SELECT MIN(SPID) FROM master..sysprocesses where status != 'background' and spid > 7)

CREATE TABLE #temp2 (OUTPUT varchar(255))

WHILE @SPID < @MAXSPID
BEGIN
	-- SPID to MAC
	SELECT @MAC = NET_ADDRESS FROM master..sysprocesses WHERE SPID = @SPID
	
	-- MAC to IP
	CREATE TABLE #temp (OUTPUT varchar(255))
	SET NOCOUNT ON
	INSERT INTO #temp EXEC master..xp_cmdshell 'arp -a'
	
	SELECT @MACDisplay = LEFT(@MAC, 2) + '-' + SUBSTRING(@MAC, 3, 2) + '-' + SUBSTRING(@MAC, 5, 2) + '-' + SUBSTRING(@MAC, 7, 2) + '-' + SUBSTRING(@MAC, 9, 2) + '-' + SUBSTRING(@MAC, 11, 2)
	SELECT @IP = SUBSTRING(output, 3, 15) FROM #temp WHERE output LIKE '%' + @MACDisplay + '%'
	
	-- Resolve the IP
	SET @CMD = 'master..xp_cmdshell "ping -a ' + @IP + '"'
	INSERT INTO #temp2 EXEC (@CMD)

	DROP TABLE #temp
	SET @SPID = (SELECT MIN(SPID) FROM master..sysprocesses where status != 'background' and spid > @SPID)
END

delete from #temp2 where output not like 'pinging%'
delete from #temp2 where output is null
select distinct * from #temp2
DROP TABLE #temp2
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --