Wednesday, September 25, 2013

SCCM Report for Machine Name MAC Address IP Address


The following SQL query provides Machine name, MAC address and IP Address.

*****************************************************************

SELECT     v_RA_System_ResourceNames.Resource_Names0 AS [Resource name], v_RA_System_MACAddresses.MAC_Addresses0 AS [MAC Address],
v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address]
FROM         v_RA_System_MACAddresses INNER JOIN
v_RA_System_ResourceNames ON v_RA_System_MACAddresses.ResourceID = v_RA_System_ResourceNames.ResourceID INNER JOIN
v_RA_System_IPAddresses ON v_RA_System_MACAddresses.ResourceID = v_RA_System_IPAddresses.ResourceID

***************************************************************



More SCCM custom reports can be found here




6 comments:

  1. If you have created two separate queries to generate the mac address for Physical & WLAN MAC addresses separately and merged both the reports.So that solved my problem.

    Thanks
    Silvester Norman

    Change MAC Address

    ReplyDelete
  2. How would you add username to this report also? I am still kind of new to all this.

    Thanks!

    ReplyDelete
  3. Dear sir!

    Thank you for this but I have a question. I am very new to sccm, and especially to query language. But if I copy and past this, I get a syntax error message. Is there something in there I need to edit to make it work?

    ReplyDelete
  4. Simple copy and paste should work.
    First paste in notepad then see whether the webpage adding any characters.

    ReplyDelete
    Replies
    1. im trying this from sccm, not from sql and every time i receive syntax error. can you help me whit this?
      i need to create query for laptop/deskop computers and their IP addresses. problem is that many of them have multiple IPs, some from VPN, wifi.....is there any chance to exclude IPv6 from query and also to join right IP with right MAC from network adapter?!
      tnx in advance

      Delete