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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2
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
Answered 12/03/2009 by: RichB
Third Degree Green Belt

  • Great one, thank you
Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

1
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.
Answered 12/06/2009 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
2
This is perfect, RichB! Thanks for the lesson.

David
Answered 01/28/2010 by: anyoneis
Senior Yellow Belt

Please log in to comment
1
Definitely, Gerald! That will save a little maintenance every time a new wired adapter walks into the building.

Thanks,
David
Answered 01/28/2010 by: anyoneis
Senior Yellow Belt

Please log in to comment
0
Thanks RichB!  I have to use an 3rd party WOL app and needed to get MAC address's of Agent installed PCs.
Answered 08/28/2015 by: crispyb
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity