A 'lil Nbtstat help needed. 1

[tman138]

I have an Access database that I am using to analyize my firewall data. I want to call Nbtstat -a in a VBA function to retrieve the computer name from the IP address. Any help??

 

[VBslammer]

You can redirect DOS output to a file, then parse the lines of text returned by your command to get what you're looking for. Here's a sample function that can execute your command, write the results to file, then open the file and return the results:

Function GetNetStats(ByVal commandArgs As String) As String
On Error GoTo ErrHandler
  Dim fso As New FileSystemObject
  Dim so As New WshShell
  Dim io As TextStream
  Dim lngReturn As Long
  Dim strCommand As String
  
  Const outFile As String = "C:\NetStats.Log"
  
  strCommand = "%comspec% /c nbtstat " & commandArgs & " > " & Chr(34) & outFile & Chr(34)
  
  If Dir(outFile) <> "" Then
    fso.DeleteFile outFile
  End If
  
  lngReturn = so.Run(strCommand, vbHide, True)
  
  If lngReturn = 0 Then
    Set io = fso.OpenTextFile(outFile, ForReading)
    GetNetStats = io.ReadAll()
  End If

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

Example Call:

Dim strResults As String

strResults = GetNetStats("-a computername")
If Len(strResults) > 0 Then
  [green]'parse the results[/green]
End If

You could customize the function to return a single line based on a second argument, but you'll have to experiment with the resultset in order to accurately determine how to parse the string.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[tman138]

Thanks- I came up with something similar over the weekend, but this appears cleaner.

 

[tman138]

I used my parsing code with yours & it works great. You can pass in either an IP address or a user name.

Public Function fnGetNetStats(varIPaddressIn As String)
Dim strResults As String
Dim fileNum As Integer
Dim InputString As String
Dim myPos
Dim i As Integer
Dim tempString As String

strResults = GetNetStats("-a " & varIPaddressIn)
If Len(strResults) > 0 Then
'Parse the results
fileNum = FreeFile
Open "C:\NetStats.Log" For Input As #fileNum
While Not EOF(fileNum)
Line Input #fileNum, InputString

myPos = InStr(1, InputString, "<20>")

If InStr(1, InputString, "<20>") > 0 Then
If Left(InputString, 1) <> "C" Then
i = 1
tempString = vbNullString
tempString = tempString & Trim(Mid(InputString, i, myPos - 2))

Do Until Mid(InputString, i, 1) = " "
tempString = Trim(InputString)
i = i + 1
Loop
fnGetNetStats = tempString
Exit Function
End If
End If
Wend
End If
End Function

 

[VBslammer]

Since the original function already reads the file and returns its content in the result, there isn't a need to re-open the file to parse it. Simply parse the results - using the Split() and Filter() functions:

Public Function GetDomainFromIP(ByVal quadDot As String) As String
  Dim strResults As String
  Dim strLines() As String
    
  strResults = GetNetStats("-a " & quadDot)
  If Len(strResults) > 0 Then
    [green]'Parse the results[/green]
    strLines = Split(strResults, vbCrLf)
    strLines = Filter(strLines, "<20>")
    If UBound(strLines) > -1 Then
      GetDomainFromIP = Trim(Left(strLines(0), InStr(strLines(0), "<20>") - 1))
    End If
  End If
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob