doing a whois in excel

[saimike]

I have multiple columns of IPv4 and IPv6 address. How would I go about writing a function that takes the IPv4/IPv6 address, and performs a whois (e.g. http://whois.arin.net/rest/nets;q=15.185.186.215?showDetails=true&showARIN=false&ext=netref2) and extracts the following info:


NetRange
Name
Organization


e.g.:
A1,B1,C1,D1
15.185.186.215,15.0.0.0 - 15.255.255.255,HP-INTERNET,Hewlett-Packard Company (HP)


Thanks!

 

[mjbeam]

The code below will create a user defined function. To use it, put the following in cells B1, C1 and D1:

B1 ---> =whois(A1,"NetRange")
C1 ---> =whois(A1,"Name")
D1 ---> =whois(A1,"Organization")




Function WhoIs(ByVal tRange As Range, ByVal tItem As String) As String

Dim xmlhttp As Object
Dim r, c As Integer
Dim t As String

r = tRange.Row
c = tRange.Column

ip = Cells(r, c).Text

Set xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
xmlhttp.Open "get", "http://whois.arin.net/rest/nets;q=" & ip & "?showDetails=true&showARIN=false&ext=netref2", False
xmlhttp.send
t = xmlhttp.ResponseText

Select Case UCase(tItem)
Case "NETRANGE"
t = Mid(t, InStr(t, "<endAddress>") + 12)
WhoIs = Left(t, InStr(t, "<") - 1) & " - "
t = Mid(t, InStr(t, "<startAddress>") + 14)
WhoIs = WhoIs & Left(t, InStr(t, "<") - 1)
Case "NAME"
t = Mid(t, InStr(t, "<name>") + 6)
WhoIs = Left(t, InStr(t, "<") - 1)
Case "ORGANIZATION"
t = Mid(t, InStr(t, "<orgRef name=") + 14)
WhoIs = Left(t, InStr(t, Chr(34)) - 1) & " ("
t = Mid(t, InStr(t, "handle=") + 8)
WhoIs = WhoIs & Left(t, InStr(t, Chr(34)) - 1) & ")"
End Select

End Function

 

[mjbeam]

Sorry, the code didn't post correctly. The software is interpreting some of my code as XML or something. For these lines:

t = Mid(t, InStr(t, "< endAddress>") + 12)
t = Mid(t, InStr(t, "< startAddress>") + 14)
t = Mid(t, InStr(t, "< name>") + 6)
t = Mid(t, InStr(t, "< orgRef name=") + 14)

...remove all spaces from inside the quotes (ie. the space after the < character). I had to add the space to get the code to post.



Function WhoIs(ByVal tRange As Range, ByVal tItem As String) As String

Dim xmlhttp As Object
Dim r, c As Integer
Dim t As String

r = tRange.Row
c = tRange.Column

ip = Cells(r, c).Text

Set xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
xmlhttp.Open "get", "http://whois.arin.net/rest/nets;q=" & ip & "?showDetails=true&showARIN=false&ext=netref2", False
xmlhttp.send
t = xmlhttp.ResponseText

Select Case UCase(tItem)
Case "NETRANGE"
t = Mid(t, InStr(t, "< endAddress>") + 12)
WhoIs = Left(t, InStr(t, "<") - 1) & " - "
t = Mid(t, InStr(t, "< startAddress>") + 14)
WhoIs = WhoIs & Left(t, InStr(t, "<") - 1)
Case "NAME"
t = Mid(t, InStr(t, "< name>") + 6)
WhoIs = Left(t, InStr(t, "<") - 1)
Case "ORGANIZATION"
t = Mid(t, InStr(t, "< orgRef name=") + 14)
WhoIs = Left(t, InStr(t, Chr(34)) - 1) & " ("
t = Mid(t, InStr(t, "handle=") + 8)
WhoIs = WhoIs & Left(t, InStr(t, Chr(34)) - 1) & ")"
End Select

End Function

 

[saimike]

mjbeam, thanks! I think that worked. Let me play around with it.

 

[Scott Huish]

You can work with the range directly without converting it to rows and columns:

ip=tRange.Text

 

[mjbeam]

The previous code had the range values backwards. This will fix that, and I reformatted it so it will show up correctly in this forum:

Function WhoIs(ByVal tRange As Range, ByVal tItem As String) As String

    Dim xmlhttp As Object
    Dim r, c As Integer
    Dim t As String
    
    r = tRange.Row
    c = tRange.Column
    
    ip = Cells(r, c).Text
    
    Set xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
    xmlhttp.Open "get", "http://whois.arin.net/rest/nets;q=" & ip & "?showDetails=true&showARIN=false&ext=netref2", False
    xmlhttp.send
    t = xmlhttp.ResponseText
    
    Select Case UCase(tItem)
        Case "NETRANGE"
            t = Mid(t, InStr(t, "<" & "endAddress>") + 12)
            WhoIs = Left(t, InStr(t, "<") - 1)
            t = Mid(t, InStr(t, "<" & "startAddress>") + 14)
            WhoIs = Left(t, InStr(t, "<") - 1) & " - " & WhoIs
        Case "NAME"
            t = Mid(t, InStr(t, "<" & "name>") + 6)
            WhoIs = Left(t, InStr(t, "<") - 1)
        Case "ORGANIZATION"
            t = Mid(t, InStr(t, "<" & "orgRef name=") + 14)
            WhoIs = Left(t, InStr(t, Chr(34)) - 1) & " ("
            t = Mid(t, InStr(t, "handle=") + 8)
            WhoIs = WhoIs & Left(t, InStr(t, Chr(34)) - 1) & ")"
    End Select

End Function

 

[saimike]

Thanks mjbeam!

Can I submit a change request here ... How do I add a <Case "ALL"> where B1, C1 and D1 are all populated with NETRANGE, NAME, ORGANIZATION in one go? This way, I wouldn't have to make 3 xmlhttp.open, which is slow. Appreciate all your help!

 

[mjbeam]

You will have to enter this as an array formula on the spreadsheet. For example, enter 15.185.186.215 in cell A1. Then select cells B1-D1, and in the formula editor enter the formula:

=WhoIs2(A1)

Then press Shift-Ctrl-Enter.

Function WhoIs2(ByVal tRange As Range) As Variant

    Dim xmlhttp As Object
    Dim s, t As String
    Dim WhoIsArray(3) As Variant
    
    ip = tRange.Text 'Props to Scott Huish
    
    Set xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
    xmlhttp.Open "get", "http://whois.arin.net/rest/nets;q=" & ip & "?showDetails=true&showARIN=false&ext=netref2", False
    xmlhttp.send
    s = xmlhttp.ResponseText
    
    t = Mid(s, InStr(s, "<" & "endAddress>") + 12)
    WhoIsArray(2) = Left(t, InStr(t, "<") - 1)
    t = Mid(t, InStr(t, "<" & "startAddress>") + 14)
    WhoIsArray(2) = Left(t, InStr(t, "<") - 1) & " - " & WhoIsArray(2)

    t = Mid(s, InStr(s, "<" & "name>") + 6)
    WhoIsArray(0) = Left(t, InStr(t, "<") - 1)

    t = Mid(s, InStr(s, "<" & "orgRef name=") + 14)
    WhoIsArray(1) = Left(t, InStr(t, Chr(34)) - 1) & " ("
    t = Mid(t, InStr(t, "handle=") + 8)
    WhoIsArray(1) = WhoIsArray(1) & Left(t, InStr(t, Chr(34)) - 1) & ")"
    
    WhoIs2 = WhoIsArray

End Function

 

[mjbeam]

I noticed that I didn't output the NetRange, Name, and Organization in the order you asked for. To correct this you can change the WhoIsArray index values to what you want. In the example above, WhoIsArray(0) will go into cells B1,WhoIsArray (1) to cell C1, and WhoIsArray(2) will go to cell D1. To fix the code change the indexes as needed. If this isn't clear let me know and I'll fix it for you.

 

[saimike]

mjbeam no worries, I was trying to avoid using an array formula so I'll just let it run ... Thanks!.