April 18, 2013 at 1:57 am
Comments posted to this topic are about the item Get IP Addresses of all Hostnames using SQL Server
May 2, 2013 at 7:11 am
Please tell me you are using a local/desktop instance of SQL server to run this code and that you're not doing this on a production server.
I might try to convince you to use a scripting environment for this task and have it contact your database to store the found/discovered data - but I also understand that if your comfort zone is T-SQL that's the language you'll use to solve the problem at hand. (when your only tool is a hammer all your problems look like nails)
Some other thoughts:
You're already using dynamic sql, why not make the column name in the table also passed into the procedure - in case the dynamic tablename has some other column name containing the hostnames.
Try updating your ping command to include -n 1 and -w 1 so you only ping once and only wait 1ms for a reply. Since you are only looking for the dns lookup "Pinging...[a.b.c.d]" line anyway you don't really even need the results of the ping. Sending 1 instead of the default 4 will be less wasteful (and faster) and timing-out quickly (especially on not-found hosts) will also help this task complete sooner. (which might not be important until you consider the resources consumed by executing xp_cmdshell 500+ times in a cursor)
btw, if you decide to store the IP address using 4 tinyint fields for the sake of indexing and easier grouping by various octets, you can use parsename() for a clever split:
select parsename('192.168.1.10',4) /* 192, or the "server" part of a 4part name */
select parsename('192.168.1.10',3) /* 168, or the "database" part of a 4part name */
select parsename('192.168.1.10',2) /* 1, or the "owner" part of a 4part name */
select parsename('192.168.1.10',1) /* 10, or the "table" part of a 4part name */
May 2, 2013 at 3:30 pm
Tried to look at this to see what was what and could not get it to run. Messages were
Msg 8152, Level 16, State 14, Procedure usp_getIPAdress_frm_Hostname, Line 47
String or binary data would be truncated.
M.
Not all gray hairs are Dinosaurs!
May 20, 2013 at 8:01 am
Pardon my lack of knowledge in the SQL environment. How do I really use this script and make us of it. It seems to be every interesting. I am really new to this. Thanks for sharing.
October 13, 2015 at 9:09 am
Awesome script. Works like a clock. Thanks.
You do not need to have a stored procedure there.
Made 3 modifications:
1. Converted it an ad hoc query using the sysprocesses.hostname column
2. Expanded some string variables to hold longer strings
3. Filtered empty hostnames and duplicates as following (cut execution time by half):
EXEC('INSERT INTO #TblHostName (Hostname) Select distinct hostname from sysprocesses where hostname <> ''''')
The altered version below:
You could drop BEGIN/END (I did not bother)
use master;
BEGIN
CREATE TABLE #Results ( Results VARCHAR(4000) )
DECLARE @Commandstring VARCHAR(4000) ,
@IP VARCHAR(300) ,
@ctr VARCHAR(600) ,
@Hostname VARCHAR(128)
CREATE TABLE #TblHostName
(
ID INT IDENTITY(1, 1) ,
Hostname VARCHAR(255) ,
IpAddress VARCHAR(32)
)
EXEC('INSERT INTO #TblHostName (Hostname) Select distinct hostname from sysprocesses where hostname <> ''''')
SELECT @ctr = MAX(id)
FROM #TblHostName
WHILE @CTR > 0
BEGIN
SET @IP = 'NOT FOUND'
SELECT @Hostname = hostname
FROM #TblHostName
WHERE id = @ctr
SET @Commandstring = 'ping ' + @Hostname
--Insert the string in temp table
INSERT INTO #Results
EXEC master..xp_cmdshell @Commandstring
--Get the IP address from the string
SELECT @IP = SUBSTRING(Results, CHARINDEX('[', Results) + 1,
CHARINDEX(']', Results)
- CHARINDEX('[', Results) - 1)
FROM #Results
WHERE Results LIKE 'Pinging%'
UPDATE #TblHostName
SET IPADDRESS = @IP
WHERE id = @ctr
TRUNCATE TABLE #Results
SELECT @ctr = ( @ctr - 1 )
END
SELECT *
FROM #TblHostName
DROP TABLE #TblHostName
DROP TABLE #Results
END
Alex Donskoy
SQL DBA at GTLaw
Miami FL
October 21, 2015 at 7:37 am
aleksey donskoy (10/13/2015)
Awesome script. Works like a clock. Thanks.You do not need to have a stored procedure there.
Made 3 modifications:
1. Converted it an ad hoc query using the sysprocesses.hostname column
2. Expanded some string variables to hold longer strings
3. Filtered empty hostnames and duplicates as following (cut execution time by half):
EXEC('INSERT INTO #TblHostName (Hostname) Select distinct hostname from sysprocesses where hostname <> ''''')
The altered version below:
Thanks for the improvements.
October 26, 2015 at 12:39 pm
Found an easier way to do it. (Combined 2 scripts):
select distinct hostname, client_net_address from sysprocesses
inner join sys.dm_exec_connections ON sysprocesses.spid=sys.dm_exec_connections.session_id
where hostname <> ''
Alex Donskoy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply