Can i get the mac address added into this script.

hi,

Can i get the mac  address added into this script.
Mac address into the "E" colum.

Regards
sharath
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
 
objExcel.Workbooks.Add
 
intRow = 2
 
objExcel.Cells(1, 1).Value = "Machine Name"
 
objExcel.Cells(1, 2).Value = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
If Not HostName = "" then 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 3).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 4).Value = "Off Line"
 
End Select
 
 
 
intRow = intRow + 1
 
End if
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
Function ResolveIP(computerName)
   Dim objShell  :  Set objShell = CreateObject("WScript.Shell")
   Dim objExec   :  Set objExec = objShell.Exec("ping " & computerName & " -n 1")
   Dim strOutput : strOutput = objExec.StdOut.ReadAll
   Dim RegEx     :  Set RegEx = New RegExp
   RegEx.Pattern = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function

Open in new window

LVL 11
bsharathAsked:
Who is Participating?
 
yehudahaConnect With a Mentor Commented:
here:
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
 
objExcel.Workbooks.Add
 
intRow = 2
 
objExcel.Cells(1, 1).Value = "Machine Name"
 
objExcel.Cells(1, 2).Value = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
objExcel.Cells(1, 5).Value = "MacAdress"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
If Not HostName = "" then 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
 
Select Case Ping
 
Case 0 
objExcel.Cells(intRow, 3).Value = "On Line"
If per(HostName) Then
objExcel.Cells(intRow, 5).Value = FindMac(HostName)  
Else
objExcel.Cells(intRow, 5).Value = "You Dont Have Permission To Query " & HostName
End if 
Case 1
objExcel.Cells(intRow, 4).Value = "Off Line"
objExcel.Cells(intRow, 5).Value = "Machine Turn Off"
end select
 
 
intRow = intRow + 1
 
End if
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1:E1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
Function ResolveIP(computerName)
   Dim objShell  :  Set objShell = CreateObject("WScript.Shell")
   Dim objExec   :  Set objExec = objShell.Exec("ping " & computerName & " -n 1")
   Dim strOutput : strOutput = objExec.StdOut.ReadAll
   Dim RegEx     :  Set RegEx = New RegExp
   RegEx.Pattern = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function
 
Function FindMac(ip)
strComputer = ip
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colItems = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
 
For Each objItem in colItems
    FindMac = objItem.MACAddress
Next
End Function
 
Function per(computer)
	strcomputer = computer
	On Error Resume Next
	Set objWMIService = GetObject("winmgmts:" _
	& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	If err.number <> 0 Then
		err.Clear
		per = False
		On Error goto 0
	Else
		per = True
		On Error goto 0
	End If
End Function

Open in new window

0
 
mmarcuseCommented:
I had to read this twice to see that what you wanted to do was get the MAC address of the remote machine.
Since you are already pinging the remote machine, its mac address will be in the ARP cache. The code below will extract the Mac address for a given recently pinged IP address (in the example, 192.168.1.117) If you want the '-' characters in the Mac Address string, then leave out the Replace function. Hope this helps.
' lookup MAC address of an IP address recently pinged.
'
dim objMacLookup
MacAddress="NOT FOUND"
 
set wshShell = CreateObject("WScript.Shell")
ipaddress="192.168.1.117"
command = "%comspec% /c arp -a | FIND "&Chr(34)&ipaddress&Chr(34)
set objMacLookup = wshShell.Exec(command)
MacAddress = Replace(Mid(objMacLookup.StdOut.Readline(),25,17),"-","")

Open in new window

0
 
bsharathAuthor Commented:
Where should i fit in this code in the main code...
Will it get ip address if the machine is Offline

Why i ask is if it cannot get then can the Mac address be fetched from the DHCP server
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
mmarcuseConnect With a Mentor Commented:
Here's a thought: turn the  code into a function,i.e. 'ResolveMac' to keep it consistent with your naming, and have the function take ipaddress as a parameter instead of the way it is now, defined as part of the code.

Then, in your program, you can add

objExcel.Cells(intRow, 5).Value = ResolveMac(objExcel.Cells.(intRow,2).value)

this will only work if you were successful in pinging that machine (at least recently enough for it to be in the arp cache). It probably won't work if the machine is offline.

I'm afraid I don't know how to get he MAC address from the DHCP server, so I couldn't help you with that one.
0
 
yehudahaCommented:
i took mmarcuse code and used it in the main code

here :
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
 
objExcel.Workbooks.Add
 
intRow = 2
 
objExcel.Cells(1, 1).Value = "Machine Name"
 
objExcel.Cells(1, 2).Value = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
objExcel.Cells(1, 5).Value = "MacAdress"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
If Not HostName = "" then 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 3).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 4).Value = "Off Line"
 
End Select
 
objExcel.Cells(intRow, 5).Value = FindMac(ResolveIP(HostName))  
 
intRow = intRow + 1
 
End if
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
Function ResolveIP(computerName)
   Dim objShell  :  Set objShell = CreateObject("WScript.Shell")
   Dim objExec   :  Set objExec = objShell.Exec("ping " & computerName & " -n 1")
   Dim strOutput : strOutput = objExec.StdOut.ReadAll
   Dim RegEx     :  Set RegEx = New RegExp
   RegEx.Pattern = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function
 
Function FindMac(ip)
MacAddress="NOT FOUND"
 
set wshShell = CreateObject("WScript.Shell")
ipaddress=ip
command = "%comspec% /c arp -a | FIND "&Chr(34)&ipaddress&Chr(34)
set objMacLookup = wshShell.Exec(command)
FindMac = Replace(Mid(objMacLookup.StdOut.Readline(),25,17),"-","")
End Function

Open in new window

0
 
bsharathAuthor Commented:
Thank U...

If offline can't i get the mac address from the DHCP server
0
 
bsharathAuthor Commented:
Thank U...

If offline can't i get the mac address from the DHCP server
0
 
yehudahaCommented:
maybe with the netsh command, but to this correctly for a spesific ip address
it's out of my knowledge.

any way if you find a way i'm the first person you need tell him the solution :-)
0
 
bsharathAuthor Commented:
yehudaha

I get the mac address for 900 out of 3000 system names

I guess its getting the data of 1 range of ip's
Or a DHCP
Not sure but it shows as its getting the mac of online systems from one range of ip addresses and not the others that get the ip's from different DHCP servers even when they are online
0
 
bsharathAuthor Commented:
yehudaha

I get the mac address for 900 out of 3000 system names

I guess its getting the data of 1 range of ip's
Or a DHCP
Not sure but it shows as its getting the mac of online systems from one range of ip addresses and not the others that get the ip's from different DHCP servers even when they are online
0
 
yehudahaCommented:
try this
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
 
objExcel.Workbooks.Add
 
intRow = 2
 
objExcel.Cells(1, 1).Value = "Machine Name"
 
objExcel.Cells(1, 2).Value = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
objExcel.Cells(1, 5).Value = "MacAdress"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
If Not HostName = "" then 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 3).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 4).Value = "Off Line"
 
End Select
 
If per(HostName) Then  
objExcel.Cells(intRow, 5).Value = FindMac(HostName)  
Else
objExcel.Cells(intRow, 5).Value = "You Dont Have Permission To Query " & HostName
End if
 
intRow = intRow + 1
 
End if
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
Function ResolveIP(computerName)
   Dim objShell  :  Set objShell = CreateObject("WScript.Shell")
   Dim objExec   :  Set objExec = objShell.Exec("ping " & computerName & " -n 1")
   Dim strOutput : strOutput = objExec.StdOut.ReadAll
   Dim RegEx     :  Set RegEx = New RegExp
   RegEx.Pattern = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function
 
Function FindMac(ip)
strComputer = ip
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colItems = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
 
For Each objItem in colItems
    FindMac = objItem.MACAddress
Next
End Function
 
Function per(computer)
	strcomputer = computer
	On Error Resume Next
	Set objWMIService = GetObject("winmgmts:" _
	& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
	If err.number <> 0 Then
		err.Clear
		per = False
		On Error goto 0
	Else
		per = True
		On Error goto 0
	End If
End Function

Open in new window

0
 
bsharathAuthor Commented:
I get this in colum E
You Dont Have Permission To Query machine name
0
 
bsharathAuthor Commented:
I get this in colum E
You Dont Have Permission To Query machine name
0
 
yehudahaCommented:
true

your not admin in the machine your trying to get the mac address.
mmarcuse code doesn't qury remote subnets.

so i changed the code to wmi syntax that has no prblem to query any subnet,
but the limitation you need to be with admin rights on the machine.

so you need to choose whats best for you.
0
 
bsharathAuthor Commented:
yehudaha

I am the Domain admin of the Domain

I am running it from a Machine logged in as Administrator

I guess i am getting this because the systems are off
For the machines which are ON i get the mac adddress

Its become very slow now. Before it was fast.

From the last comment till now its scanned just 300+ systems. Is there any way to speed it up.

I guess that would solve my issues
As mentioned mac addresses cannot be pulled from the DHCP server if the machines are off
0
 
bsharathAuthor Commented:
yehudaha

I am the Domain admin of the Domain

I am running it from a Machine logged in as Administrator

I guess i am getting this because the systems are off
For the machines which are ON i get the mac adddress

Its become very slow now. Before it was fast.

From the last comment till now its scanned just 300+ systems. Is there any way to speed it up.

I guess that would solve my issues
As mentioned mac addresses cannot be pulled from the DHCP server if the machines are off
0
 
yehudahaCommented:
i see

i can improve another thing so the wmi querys will be more faster
and you will not get the error to turn off machine.

ok with you ?
0
 
bsharathAuthor Commented:
ya that would be fine....
0
 
bsharathAuthor Commented:
Thank U... :-)))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.