PingScanner using T-SQL in SQL Server

I coded a small stored procedure to detect which host are in the same Local Network with the SQL Server for further access. It’s kinda convenient since you don’t need any external scanner for this job. Only Transact-SQL is enough.
Note:
+ You’ll need permission to execute xp_cmdshell to use this stored procedure.
+ Change timeout setting in your SQL query executor script to a higher value(for ex timeout=3600) since scanning takes a while.

Running Procedure

Running Query

Result: Hosts online in the SQLServer's Local Network

Result: Hosts online in the SQLServer's Local Network

-----------------------------------------------
--Ping Scanner using T-SQL (C) Duong Thanh ( knightvn AT gmail.com)
--Detect which host are online on LAN by pinging from SQL Server
--Usage: EXEC spPingScan '192.168.1.0-254'
-------------------------------------------------
CREATE PROCEDURE spPingScan @ip_range varchar(200)
AS
BEGIN
	DECLARE @stpos int, @i int, @ip_start varchar(200), @start int
			,@head varchar(200),  @pos_dash int, @end int
	
	SET @ip_range = LTRIM(RTRIM(@ip_range))	    
	--ex: @ip_range = '192.168.1.1-10'	 
	SET @pos_dash = CHARINDEX('-', @ip_range) -- dash position
	SET @end = SUBSTRING(@ip_range, @pos_dash + 1, LEN(@ip_range) - @pos_dash) -- 233
	SET @ip_start = SUBSTRING (@ip_range, 1, LEN(@ip_range) - LEN(@end) - 1) -- 192.168.101.20

	SET @stpos = 1

	-- Get final . index 
	SET @i = 1

	WHILE @i < 4
	BEGIN 
		SET @stpos= CHARINDEX('.', @ip_start, @stpos+1)
		SET @i = @i + 1   
	END 


	SET @start = CAST(SUBSTRING(@ip_start, @stpos+1, LEN(@ip_start) - @stpos) AS int) -- 10
	SET @head = SUBSTRING(@ip_start, 1, LEN(@ip_start) - LEN(@start)) -- 192.168.1.


    --tmp tables 
	CREATE TABLE #tmpPingResult
	(  &#91;ID&#93; int identity NOT NULL, &#91;content&#93; varchar(400) NULL )


	CREATE TABLE #tmpHostsUp 
	( &#91;ID&#93; int identity NOT NULL, &#91;Host&#93; varchar(50) NULL)


	DECLARE @j int, @cmd varchar(200), @host varchar(50)
	SET @cmd = ''
	SET @j = @start 

	 
	WHILE (@j <= @end)
	BEGIN
		 SET @host = @head + LTRIM(RTRIM(CAST(@j as varchar(3))))
		 SET @cmd = 'ping -n 1 ' + @host
		 INSERT INTO #tmpPingResult EXEC master..xp_cmdshell @cmd
	     
		 IF( (SELECT COUNT(*) FROM #tmpPingResult) > 8 ) 
		   BEGIN
			  INSERT INTO #tmpHostsUp VALUES (@host) --insert into host table if find a host up 
		   END
	       
		 TRUNCATE TABLE #tmpPingResult
		 SET @j = @j + 1
	END
	
  --return
  SELECT * FROM #tmpHostsUp

END

Advertisements

Leave a comment

Filed under Hacking

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s