?
Solved

Can i get the mac  address added into this script.

Posted on 2009-05-01
19
Medium Priority
?
450 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:bsharath
  • 11
  • 6
  • 2
19 Comments
 

Expert Comment

by:mmarcuse
ID: 24280860
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
 
LVL 11

Author Comment

by:bsharath
ID: 24281031
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
 

Assisted Solution

by:mmarcuse
mmarcuse earned 400 total points
ID: 24281184
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:yehudaha
ID: 24288652
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
 
LVL 11

Author Comment

by:bsharath
ID: 24288696
Thank U...

If offline can't i get the mac address from the DHCP server
0
 
LVL 11

Author Comment

by:bsharath
ID: 24288697
Thank U...

If offline can't i get the mac address from the DHCP server
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24288979
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
 
LVL 11

Author Comment

by:bsharath
ID: 24289414
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
 
LVL 11

Author Comment

by:bsharath
ID: 24289416
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
 
LVL 14

Expert Comment

by:yehudaha
ID: 24289826
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
 
LVL 11

Author Comment

by:bsharath
ID: 24289968
I get this in colum E
You Dont Have Permission To Query machine name
0
 
LVL 11

Author Comment

by:bsharath
ID: 24289969
I get this in colum E
You Dont Have Permission To Query machine name
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24290326
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
 
LVL 11

Author Comment

by:bsharath
ID: 24290351
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
 
LVL 11

Author Comment

by:bsharath
ID: 24290353
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
 
LVL 14

Expert Comment

by:yehudaha
ID: 24290367
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
 
LVL 11

Author Comment

by:bsharath
ID: 24290396
ya that would be fine....
0
 
LVL 14

Accepted Solution

by:
yehudaha earned 1600 total points
ID: 24290584
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
 
LVL 11

Author Comment

by:bsharath
ID: 24293308
Thank U... :-)))
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question