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.
Related Articles
5 users responded in this post
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?
Did you select Show Query Language rather then Query Design? You are correct, the ConfigMgr client won’t be installed on them.
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”?
There is also a good article here – http://myitforum.com/cs2/blogs/skissinger/archive/2009/12/16/iphone-inventory-apps-installed-last-backup-date-version.aspx – showing iPhone Inventory – Apps installed, last backup date, Version
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
Leave A Reply