Inventory: MAC Addresses
We have a need to get a list of computer systems by name along with their wired MAC address. Unfortunately, the inventory page seems to pick which MAC address to display based on some undisclosed (and for us, inappropriate) algorithm. Is there any way to force the agent to pick the wired MAC address? Or, is there a way to get a report from the KBox listing only the wired MAC address?
We have a large number of machines which usually report one of a few Cisco AnyConnect MAC address - this is not very useful for us. These show up as duplicates when I run the report "DUPLICATE MAC ADDRESS" based on the following SQL:
SELECT M1.LAST_SYNC, M1.MAC, M1.NAME AS SYSTEM_NAME
FROM MACHINE M1
WHERE
(SELECT COUNT(MAC) FROM MACHINE
WHERE M1.MAC = MAC) > 1
ORDER BY M1.MAC asc, M1.LAST_SYNC, M1.NAME asc
Obviously, I can't use MACHINE.MAC, unless there is some change in the way the Agent sets that. So, there must be another table I can join to which lists the various network adapters for each computer. Can someone give me a clue or two on what I need to do to link to that table? I will then just need to exclude wireless and the AnyConnect adapters to get what I want.
Thanks,
David
We have a large number of machines which usually report one of a few Cisco AnyConnect MAC address - this is not very useful for us. These show up as duplicates when I run the report "DUPLICATE MAC ADDRESS" based on the following SQL:
SELECT M1.LAST_SYNC, M1.MAC, M1.NAME AS SYSTEM_NAME
FROM MACHINE M1
WHERE
(SELECT COUNT(MAC) FROM MACHINE
WHERE M1.MAC = MAC) > 1
ORDER BY M1.MAC asc, M1.LAST_SYNC, M1.NAME asc
Obviously, I can't use MACHINE.MAC, unless there is some change in the way the Agent sets that. So, there must be another table I can join to which lists the various network adapters for each computer. Can someone give me a clue or two on what I need to do to link to that table? I will then just need to exclude wireless and the AnyConnect adapters to get what I want.
Thanks,
David
0 Comments
[ + ] Show comments
Answers (5)
Please log in to answer
Posted by:
RichB
14 years ago
I used the report wizard starting with the "Computer NIC" topic and added the items in the Network Interface Info section plus Computer Identity Information.System Name. The report formatting provided by the wizard makes it easier to use than just SQL when possible. In the report rules step 3 the interfaces I didn't want reported were listed, for example, Network Interface Info.NIC Does Not Contain Bluetooth, and repeated for others.
Here's the SQL it created for the select statement:
SELECT MACHINE_NICS.IP AS NIC_IP, MACHINE_NICS.MAC AS NIC_MAC, NIC, MACHINE.NAME AS SYSTEM_NAME FROM MACHINE_NICS JOIN MACHINE ON (MACHINE.ID = MACHINE_NICS.ID)
WHERE NIC NOT LIKE '%bluetooth%' AND NIC NOT LIKE '%firewire%' AND NIC NOT LIKE '%modem%' AND NIC NOT LIKE '%virtual%' AND NIC NOT LIKE '%vmware%' AND NIC NOT LIKE '%vpn%'
ORDER BY MACHINE.NAME asc
Here's the SQL it created for the select statement:
SELECT MACHINE_NICS.IP AS NIC_IP, MACHINE_NICS.MAC AS NIC_MAC, NIC, MACHINE.NAME AS SYSTEM_NAME FROM MACHINE_NICS JOIN MACHINE ON (MACHINE.ID = MACHINE_NICS.ID)
WHERE NIC NOT LIKE '%bluetooth%' AND NIC NOT LIKE '%firewire%' AND NIC NOT LIKE '%modem%' AND NIC NOT LIKE '%virtual%' AND NIC NOT LIKE '%vmware%' AND NIC NOT LIKE '%vpn%'
ORDER BY MACHINE.NAME asc
Comments:
-
Great one, thank you - Mahmood1986 9 years ago
-
This was helpful - thank you. - matthewconlon 7 years ago
Posted by:
GillySpy
14 years ago
I think inventory will report whatever interface was used to check into the kbox, but the other NICs will be stored as well and you can see that in a sub-section of the inventory.
RichB has it right. That info is stored in a table called MACHINE_NICS
What might work as an additional filter would be IP address since the IP range on wired connections for many customers is different then the range for Wireless, etc.
RichB has it right. That info is stored in a table called MACHINE_NICS
What might work as an additional filter would be IP address since the IP range on wired connections for many customers is different then the range for Wireless, etc.
Posted by:
anyoneis
14 years ago
Definitely, Gerald! That will save a little maintenance every time a new wired adapter walks into the building.
Thanks,
David
Thanks,
David
Posted by:
crispyb
8 years ago
Thanks RichB! I have to use an 3rd party WOL app and needed to get MAC address's of Agent installed PCs.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.