February 3 2011

SCCM report to show all Apple devices (iPhone, iPod, iPad)

As part of the network discovery process, I noticed that it was picking up the iPhone and other Apple machines (Macs, iPads) that were on the corporate network either via being plugged in or on the wireless LAN.

Shortly afterwards, there was a requirement to report on all the Apple devices found in the environment. SCCM to the rescue.

After visiting the IEEE website (http://standards.ieee.org/develop/regauth/oui/public.html) and doing a search for ‘Apple’, I found the OUI (first 6 hex characters of the MAC address) that all Apple product would have (keep in mind some manufacturers subcontract component manufacture and others include registered firm OUIs in their products).

I then wrote a collection membership rule SQL query to find all the devices that have these OUIs as the start of their MAC address:

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.MACAddresses like "58:55:CA%" or SMS_R_System.MACAddresses like "00:03:93%" or SMS_R_System.MACAddresses like "00:03:93%" or SMS_R_System.MACAddresses like "00:05:02%" or SMS_R_System.MACAddresses like "00:0A:27%" or SMS_R_System.MACAddresses like "00:0A:95%" or SMS_R_System.MACAddresses like "00:10:FA%" or SMS_R_System.MACAddresses like "00:11:24%" or SMS_R_System.MACAddresses like "00:14:51%" or SMS_R_System.MACAddresses like "00:16:CB%" or SMS_R_System.MACAddresses like "00:17:F2%" or SMS_R_System.MACAddresses like "00:19:E3%" or SMS_R_System.MACAddresses like "00:1B:63%" or SMS_R_System.MACAddresses like "00:1C:B3%" or SMS_R_System.MACAddresses like "00:1D:4F%" or SMS_R_System.MACAddresses like "00:1E:52%" or SMS_R_System.MACAddresses like "00:1E:C2%" or SMS_R_System.MACAddresses like "00:1F:5B%" or SMS_R_System.MACAddresses like "00:1F:71%" or SMS_R_System.MACAddresses like "00:1F:F3%" or SMS_R_System.MACAddresses like "00:21:E9%" or SMS_R_System.MACAddresses like "00:22:41%" or SMS_R_System.MACAddresses like "00:23:12%" or SMS_R_System.MACAddresses like "00:23:32%" or SMS_R_System.MACAddresses like "00:23:6C%" or SMS_R_System.MACAddresses like "00:23:DF%" or SMS_R_System.MACAddresses like "00:24:36%" or SMS_R_System.MACAddresses like "00:25:00%" or SMS_R_System.MACAddresses like "00:25:4B%" or SMS_R_System.MACAddresses like "00:25:BC%" or SMS_R_System.MACAddresses like "00:26:08%" or SMS_R_System.MACAddresses like "00:26:4A%" or SMS_R_System.MACAddresses like "00:26:B0%" or SMS_R_System.MACAddresses like "00:26:BB%" or SMS_R_System.MACAddresses like "00:30:65%" or SMS_R_System.MACAddresses like "00:50:E4%" or SMS_R_System.MACAddresses like "00:A0:3F%" or SMS_R_System.MACAddresses like "00:A0:40%" or SMS_R_System.MACAddresses like "04:1E:64%" or SMS_R_System.MACAddresses like "08:00:07%" or SMS_R_System.MACAddresses like "10:93:E9%" or SMS_R_System.MACAddresses like "10:9A:DD%" or SMS_R_System.MACAddresses like "18:E7:F4%" or SMS_R_System.MACAddresses like "24:AB:81%" or SMS_R_System.MACAddresses like "28:E7:CF%" or SMS_R_System.MACAddresses like "34:15:9E%" or SMS_R_System.MACAddresses like "3C:07:54%" or SMS_R_System.MACAddresses like "40:A6:D9%" or SMS_R_System.MACAddresses like "40:D3:2D%" or SMS_R_System.MACAddresses like "44:2A:60%" or SMS_R_System.MACAddresses like "58:1F:AA%" or SMS_R_System.MACAddresses like "58:55:CA%" or SMS_R_System.MACAddresses like "58:B0:35%" or SMS_R_System.MACAddresses like "5C:59:48%" or SMS_R_System.MACAddresses like "60:33:4B%" or SMS_R_System.MACAddresses like "60:FB:42%" or SMS_R_System.MACAddresses like "64:B9:E8%" or SMS_R_System.MACAddresses like "70:CD:60%" or SMS_R_System.MACAddresses like "78:CA:39%" or SMS_R_System.MACAddresses like "7C:6D:62%" or SMS_R_System.MACAddresses like "7C:C5:37%" or SMS_R_System.MACAddresses like "88:C6:63%" or SMS_R_System.MACAddresses like "8C:58:77%" or SMS_R_System.MACAddresses like "8C:7B:9D%" or SMS_R_System.MACAddresses like "90:27:E4%" or SMS_R_System.MACAddresses like "90:84:0D%" or SMS_R_System.MACAddresses like "98:03:D8%" or SMS_R_System.MACAddresses like "A4:67:06%" or SMS_R_System.MACAddresses like "B8:FF:61%" or SMS_R_System.MACAddresses like "C4:2C:03%" or SMS_R_System.MACAddresses like "C8:2A:14%" or SMS_R_System.MACAddresses like "C8:AA:CC%" or SMS_R_System.MACAddresses like "C8:BC:C8%" or SMS_R_System.MACAddresses like "CC:08:E0%" or SMS_R_System.MACAddresses like "D4:9A:20%" or SMS_R_System.MACAddresses like "D8:30:62%" or SMS_R_System.MACAddresses like "D8:A2:5E%" or SMS_R_System.MACAddresses like "DC:2B:61%" or SMS_R_System.MACAddresses like "E0:F8:47%" or SMS_R_System.MACAddresses like "E8:06:88%" or SMS_R_System.MACAddresses like "F0:B4:79%" or SMS_R_System.MACAddresses like "F8:1E:DF%"

I then had a collection that has all the devices that have Apple MACs. Then I simply created a report that shows all the members in this collection.

This report will display all of the Apple devices discovered on the network. This may include computers that have Apple devices connected (eg iPod touch) as their MAC addresses are recorded by SCCM.

Useful.
 
 



----------------------------------------------------------------------------
I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.

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


Tags: , , , , , , , , , , , ,

Posted February 3, 2011 by danovich in category "SMS / SCCM

5 COMMENTS :

  1. By tpullins on

    I’m fairly new to SCCM. I can’t seem to get this to work. I created a new query and applied it to my collection I created. When I edit the query it shows as 7 different ‘System Resource’ classes with the different attributes. However, I don’t get any discovered members in my collection. I’m certain we have apple devices on our network. Configmgr clients don’t have to be installed on them, correct?

    Reply
  2. By danovich (Post author) on

    Did you select Show Query Language rather then Query Design? You are correct, the ConfigMgr client won’t be installed on them.

    Reply
  3. By bassar on

    I’m seeing a similar problem to above. I added the query by using Show Query Language – I only have 6 System Resource classes but looking at the query itself that seems correct. I can see our Apple devices in the All Systems collection. The problem I see here is when I look at the properties for an Apple device there is no MAC Addresses property as I would see for other devices. Finally should I set the membership rules to “Dynamically add new resources”?

    Reply
  4. By danovich (Post author) on

    I’ve created another way – just creating a report without the collection. Remember that each Apple device may have multiple MAC addresses, so it will appear more than once. Report SQL query:

    select distinct
    v_RA_System_MACAddresses.MAC_Addresses0,
    v_R_System.Name0,
    v_R_System.AD_Site_Name0

    from v_RA_System_MACAddresses
    Inner Join
    v_R_System On v_R_System.ResourceID = v_RA_System_MACAddresses.ResourceID

    where v_RA_System_MACAddresses.MAC_Addresses0
    like ’58:55:CA%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:03:93%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:03:93%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:05:02%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:0A:27%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:0A:95%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:10:FA%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:11:24%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:14:51%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:16:CB%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:17:F2%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:19:E3%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1B:63%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1C:B3%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1D:4F%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1E:52%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1E:C2%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1F:5B%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1F:71%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:1F:F3%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:21:E9%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:22:41%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:23:12%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:23:32%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:23:6C%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:23:DF%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:24:36%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:25:00%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:25:4B%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:25:BC%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:26:08%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:26:4A%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:26:B0%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:26:BB%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:30:65%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:50:E4%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:A0:3F%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’00:A0:40%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’04:1E:64%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’08:00:07%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’10:93:E9%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’10:9A:DD%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’18:E7:F4%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’24:AB:81%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’28:E7:CF%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’34:15:9E%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘3C:07:54%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’40:A6:D9%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’40:D3:2D%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’44:2A:60%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’58:1F:AA%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’58:55:CA%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’58:B0:35%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘5C:59:48%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’60:33:4B%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’60:FB:42%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’64:B9:E8%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’70:CD:60%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’78:CA:39%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘7C:6D:62%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘7C:C5:37%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’88:C6:63%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘8C:58:77%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘8C:7B:9D%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’90:27:E4%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’90:84:0D%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ’98:03:D8%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘A4:67:06%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘B8:FF:61%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘C4:2C:03%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘C8:2A:14%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘C8:AA:CC%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘C8:BC:C8%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘CC:08:E0%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘D4:9A:20%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘D8:30:62%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘D8:A2:5E%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘DC:2B:61%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘E0:F8:47%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘E8:06:88%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘F0:B4:79%’ or v_RA_System_MACAddresses.MAC_Addresses0 like ‘F8:1E:DF%’

    order by v_R_System.Name0

    Reply

Leave a Reply