Technical Article

Get IP Addresses of all Hostnames using SQL Server

,

I had 500 hostnames in a trace file output and requirement was to get the IP addresses against each  hostname so I had exported all the hostnames in a table in master Database and then created 'usp_getIPAdress_frm_Hostname' stored procedure in the same database and passed the table name as an input.

Please insert all the hostnames in a table (column name should be 'hostname') and then pass the table into the procedure. It will return the IP addresses for all hostnames without any changes in the source table. 

--EXEC usp_getIPAdress_frm_Hostname 'trcHostName'
CREATE PROCEDURE usp_getIPAdress_frm_Hostname
    (
      @Tablename VARCHAR(255)
    )
AS 
    BEGIN
        CREATE TABLE #Results ( Results VARCHAR(4000) ) 

        DECLARE @Commandstring VARCHAR(300) ,
            @IP VARCHAR(300) ,
            @ctr VARCHAR(300) ,
            @Hostname VARCHAR(100)
        CREATE TABLE #TblHostName
            (
              ID INT IDENTITY(1, 1) ,
              Hostname VARCHAR(255) ,
              IpAddress VARCHAR(16)
            )

        EXEC('INSERT INTO #TblHostName (Hostname) Select hostname from '+@Tablename+'')

        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

Rate

3 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (5)

You rated this post out of 5. Change rating