none
SQL query for LAN network adapter and MAC-address. Not like default report in SCCM 2007.

    Question

  • In SCCM 2007 there is a report (ID 37-Network) named, "MAC - Computers for a specific MAC address.

    Depending on the type of network, it sometimes shows the Wifi-cards MAC-address and sometimes the LAN-cards MAC-address. I know that the IP-address comes from the Wifi-card (in case the computer is only connected to Wifi) but it is the LAN-cards MAC I want.

    ------------------------------------------------------------------------

    QUERY:

    SELECT Sys.Netbios_Name0,MAC.MAC_Addresses0, IP.IP_Addresses0
    FROM v_R_System Sys, v_RA_System_MACAddresses MAC, v_RA_System_IPAddresses IP 
    WHERE Sys.ResourceID = MAC.ResourceID AND Sys.ResourceID = IP.ResourceID AND
              MAC.MAC_Addresses0 LIKE @variable
    ORDER BY MAC.MAC_Addresses0

    ------------------------------------------------------------------------

    I'm gonna use the report for filling up my MDT database in Deployment Workbench, with system names and mac-adresses, that later will be used in OSD-tasksequence( in SCCM 2007) to assosiate Computers with Roles and Applications.

     

    I only want to get the LAN-adapters MAC-addresses in the report.

    HOW DO I WRITE A PROPER QUERY?





    Sunday, December 11, 2011 8:25 PM

Answers

  • Unfortunately there is no guarantee way to determine Wireless NIC or Wireed NIC. You can look at this view and it will help v_GS_NETWORK_ADAPTER but there you must manually filter out all the wireless ones.


    http://www.enhansoft.com/
    • Marked as answer by Sabrina Shen Tuesday, December 20, 2011 8:49 AM
    Sunday, December 11, 2011 8:39 PM
  • Maybe add something like NAME NOT EQUAL %WIFI% to your query?
    • Marked as answer by Sabrina Shen Tuesday, December 20, 2011 8:49 AM
    Monday, December 12, 2011 2:35 AM
  • Doing that will help but it will not guarantee that there are no wireless nic..
    http://www.enhansoft.com/
    • Marked as answer by Sabrina Shen Tuesday, December 20, 2011 8:49 AM
    Monday, December 12, 2011 3:13 AM

All replies

  • Unfortunately there is no guarantee way to determine Wireless NIC or Wireed NIC. You can look at this view and it will help v_GS_NETWORK_ADAPTER but there you must manually filter out all the wireless ones.


    http://www.enhansoft.com/
    • Marked as answer by Sabrina Shen Tuesday, December 20, 2011 8:49 AM
    Sunday, December 11, 2011 8:39 PM
  • Does it matter to "Deployment workbench" and the MDT-database if it is the MAC-address of the Wired och the Wireless card that is assosiated with "Roles and Applications"?
    Sunday, December 11, 2011 8:43 PM
  • MDT/OSD is not my specialty.  I’m sure that you will get better response within the OSD forum. There are a few guy in there that do a lot fo cool things with OSD and MDT.


    http://www.enhansoft.com/
    Sunday, December 11, 2011 9:10 PM
  • Maybe add something like NAME NOT EQUAL %WIFI% to your query?
    • Marked as answer by Sabrina Shen Tuesday, December 20, 2011 8:49 AM
    Monday, December 12, 2011 2:35 AM
  • Doing that will help but it will not guarantee that there are no wireless nic..
    http://www.enhansoft.com/
    • Marked as answer by Sabrina Shen Tuesday, December 20, 2011 8:49 AM
    Monday, December 12, 2011 3:13 AM