Get IP Addresses of all Hostnames using SQL Server

  • Comments posted to this topic are about the item Get IP Addresses of all Hostnames using SQL Server

  • 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 */

  • 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!

  • 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.

  • 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

  • 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.

  • 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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply