Wireless Access

We're in the process of testing DHCP server on Windows 2012 and we'd like to start using policies to give lower lease time to mobile devices.  I figured that since we are now running 6.1 and we have device profiling/fingerprinting setup, I should be able to pull a list from our airwave database by device type and give me a list of MAC addresses.  Once I get this, I can then summarize and get a list of the OUIs and give me a good idea of Apple IOS MACs (since they don't give them out).

Anyone know which cli command I would run to get this, something like

select device_type, mac_address from ? where device_type like '%iphone%' or device_type like '%iPad' or device_type like '%ipod%'

Thanks

Ok I exported a list from the GUI, parsed it in excel and then with sort | uniq and got a list, I'll share it here if anyone else wants to use it:

Apple iPad,04:54:53
Apple iPad,14:10:9F
Apple iPad,18:20:32
Apple iPad,1C:AB:A7
Apple iPad,28:6A:BA
Apple iPad,34:51:C9
Apple iPad,34:C0:59
Apple iPad,40:30:04
Apple iPad,40:B3:95
Apple iPad,4C:B1:99
Apple iPad,5C:96:9D
Apple iPad,64:20:0C
Apple iPad,64:A3:CB
Apple iPad,64:E6:82
Apple iPad,70:DE:E2
Apple iPad,74:E1:B6
Apple iPad,7C:6D:62
Apple iPad,84:29:99
Apple iPad,84:85:06
Apple iPad,8C:2D:AA
Apple iPad,8C:7B:9D
Apple iPad,8C:FA:BA
Apple iPad,98:B8:E3
Apple iPad,98:FE:94
Apple iPad,9C:04:EB
Apple iPad,A4:67:06
Apple iPad,A4:D1:D2
Apple iPad,B0:65:BD
Apple iPad,B4:F0:AB
Apple iPad,B8:C7:5D
Apple iPad,B8:FF:61
Apple iPad,BC:67:78
Apple iPad,C4:2C:03
Apple iPad,C8:BC:C8
Apple iPad,D8:00:4D
Apple iPad,D8:30:62
Apple iPad,D8:A2:5E
Apple iPad,E0:B9:BA
Apple iPad,E4:8B:7F
Apple iPad,E8:06:88
Apple iPad,F0:D1:A9
Apple iPad,FC:25:3F
Apple iPhone,00:1C:B3
Apple iPhone,00:1E:C2
Apple iPhone,00:21:E9
Apple iPhone,00:22:41
Apple iPhone,00:23:12
Apple iPhone,00:23:32
Apple iPhone,00:23:6C
Apple iPhone,00:23:DF
Apple iPhone,00:24:36
Apple iPhone,00:25:00
Apple iPhone,00:25:4B
Apple iPhone,00:26:08
Apple iPhone,00:26:4A
Apple iPhone,00:26:B0
Apple iPhone,00:26:BB
Apple iPhone,00:88:65
Apple iPhone,00:C6:10
Apple iPhone,00:F4:B9
Apple iPhone,04:0C:CE
Apple iPhone,04:1E:64
Apple iPhone,04:F7:E4
Apple iPhone,0C:74:C2
Apple iPhone,0C:77:1A
Apple iPhone,10:40:F3
Apple iPhone,10:9A:DD
Apple iPhone,14:10:9F
Apple iPhone,14:5A:05
Apple iPhone,14:8F:C6
Apple iPhone,18:20:32
Apple iPhone,18:34:51
Apple iPhone,18:9E:FC
Apple iPhone,18:E7:F4
Apple iPhone,20:C9:D0
Apple iPhone,24:AB:81
Apple iPhone,28:37:37
Apple iPhone,28:6A:BA
Apple iPhone,28:CF:DA
Apple iPhone,28:E0:2C
Apple iPhone,28:E7:CF
Apple iPhone,30:F7:C5
Apple iPhone,34:15:9E
Apple iPhone,34:C0:59
Apple iPhone,3C:D0:F8
Apple iPhone,40:30:04
Apple iPhone,40:A6:D9
Apple iPhone,40:B3:95
Apple iPhone,40:D3:2D
Apple iPhone,44:2A:60
Apple iPhone,44:4C:0C
Apple iPhone,44:D8:84
Apple iPhone,48:60:BC
Apple iPhone,4C:B1:99
Apple iPhone,50:EA:D6
Apple iPhone,54:26:96
Apple iPhone,58:1F:AA
Apple iPhone,58:55:CA
Apple iPhone,58:B0:35
Apple iPhone,5C:59:48
Apple iPhone,5C:95:AE
Apple iPhone,5C:96:9D
Apple iPhone,60:33:4B
Apple iPhone,60:C5:47
Apple iPhone,60:FA:CD
Apple iPhone,60:FB:42
Apple iPhone,64:20:0C
Apple iPhone,64:A3:CB
Apple iPhone,64:B9:E8
Apple iPhone,64:E6:82
Apple iPhone,68:09:27
Apple iPhone,68:96:7B
Apple iPhone,68:A8:6D
Apple iPhone,6C:3E:6D
Apple iPhone,6C:C2:6B
Apple iPhone,70:56:81
Apple iPhone,70:73:CB
Apple iPhone,70:DE:E2
Apple iPhone,74:E1:B6
Apple iPhone,74:E2:F5
Apple iPhone,78:A3:E4
Apple iPhone,78:CA:39
Apple iPhone,7C:11:BE
Apple iPhone,7C:6D:62
Apple iPhone,7C:C3:A1
Apple iPhone,7C:C5:37
Apple iPhone,7C:D1:C3
Apple iPhone,84:29:99
Apple iPhone,84:85:06
Apple iPhone,84:FC:FE
Apple iPhone,88:53:95
Apple iPhone,88:C6:63
Apple iPhone,8C:2D:AA
Apple iPhone,8C:58:77
Apple iPhone,8C:7B:9D
Apple iPhone,90:27:E4
Apple iPhone,90:84:0D
Apple iPhone,98:03:D8
Apple iPhone,98:B8:E3
Apple iPhone,98:D6:BB
Apple iPhone,98:FE:94
Apple iPhone,9C:04:EB
Apple iPhone,9C:20:7B
Apple iPhone,A4:67:06
Apple iPhone,A4:D1:D2
Apple iPhone,A8:FA:D8
Apple iPhone,B0:65:BD
Apple iPhone,B4:F0:AB
Apple iPhone,B8:17:C2
Apple iPhone,B8:C7:5D
Apple iPhone,B8:FF:61
Apple iPhone,BC:3B:AF
Apple iPhone,BC:52:B7
Apple iPhone,BC:67:78
Apple iPhone,C0:9F:42
Apple iPhone,C4:2C:03
Apple iPhone,C8:33:4B
Apple iPhone,C8:BC:C8
Apple iPhone,CC:08:E0
Apple iPhone,D0:23:DB
Apple iPhone,D4:9A:20
Apple iPhone,D8:30:62
Apple iPhone,D8:9E:3F
Apple iPhone,D8:A2:5E
Apple iPhone,D8:D1:CB
Apple iPhone,DC:2B:61
Apple iPhone,E0:B9:BA
Apple iPhone,E0:C9:7A
Apple iPhone,E0:F8:47
Apple iPhone,E4:8B:7F
Apple iPhone,E4:CE:8F
Apple iPhone,E8:06:88
Apple iPhone,EC:85:2F
Apple iPhone,F0:B4:79
Apple iPhone,F0:CB:A1
Apple iPhone,F0:D1:A9
Apple iPhone,F0:DC:E2
Apple iPhone,F8:1E:DF
Apple iPhone,FC:25:3F
Apple iPod,00:1E:C2
Apple iPod,00:22:41
Apple iPod,00:23:32
Apple iPod,00:23:DF
Apple iPod,00:24:36
Apple iPod,00:25:4B
Apple iPod,00:26:08
Apple iPod,00:26:4A
Apple iPod,00:26:B0
Apple iPod,00:26:BB
Apple iPod,00:88:65
Apple iPod,00:C6:10
Apple iPod,18:E7:F4
Apple iPod,28:37:37
Apple iPod,40:D3:2D
Apple iPod,44:D8:84
Apple iPod,58:55:CA
Apple iPod,58:B0:35
Apple iPod,5C:59:48
Apple iPod,60:33:4B
Apple iPod,60:FB:42
Apple iPod,70:56:81
Apple iPod,70:73:CB
Apple iPod,88:C6:63
Apple iPod,8C:2D:AA
Apple iPod,8C:7B:9D
Apple iPod,B8:C7:5D
Apple iPod,BC:67:78
Apple iPod,C4:2C:03
Apple iPod,D4:9A:20
Apple iPod,F0:B4:79
Apple iPod,F8:1E:DF

That list looks incomplete.  That's only Apple devices that have been associated to your AMP within your historical data retention period.

Try this:

# dbcsv "select vendor, device_type, mac from oui where vendor like '%Apple%' order by mac asc;" >> /tmp/applemacs.csv

This gives you all entries that we currently have for Apple products.  From there, you can open into excel and change any rows / columns / headers.  This table gets updated periodically.

That gave me a lot less than what I got through the gui... how do I get a list of tables in the db and their definitions?

This is what I got btw:

Apple,,000393000000
Apple,,000502000000
Apple,,000A27000000
Apple,,000A95000000
Apple,,000D93000000
Apple,,0010FA000000
Apple,,001124000000
Apple,,001451000000
Apple,,0016CB000000
Apple,,0017F2000000
Apple,,0019E3000000
Apple,Phone,001B63000000
Apple,Phone,001CB3000000
Apple,Phone,001D4F000000
Apple,,001E52000000
Apple,Phone,001EC2000000
Apple,,001F5B000000
Apple,,001FF3000000
Apple,,0021E9000000
Apple,,002241000000
Apple,Phone,002312000000
Apple,OLAP,002332000000
Apple,OLAP,00236C000000
Apple,OLAP,0023DF000000
Apple,OLAP,002436000000
Apple,OLAP,002500000000
Apple,OLAP,00254B000000
Apple,OLAP,0025BC000000
Apple,OLAP,002608000000
Apple,OLAP,00264A000000
Apple,NLAP,0026B0000000
Apple,,0026BB000000
Apple,,003065000000
Apple,,0050E4000000
Apple,,00A040000000
Apple,,040CCE000000
Apple,,041E64000000
Apple,,080007000000
Apple,,0C74C2000000
Apple,,1093E9000000
Apple,,109ADD000000
Apple,,18E7F4000000
Apple,,24AB81000000
Apple,,28E7CF000000
Apple,,34159E000000
Apple,,3C0754000000
Apple,,40A6D9000000
Apple,,40D32D000000
Apple,,442A60000000
Apple,,581FAA000000
Apple,,5855CA000000
Apple,,58B035000000
Apple,,5C5948000000
Apple,,60334B000000
Apple,,60FB42000000
Apple,,64B9E8000000
Apple,,70CD60000000
Apple,,78CA39000000
Apple,,7C6D62000000
Apple,,7CC537000000
Apple,,7CF05F000000
Apple,,88C663000000
Apple,,8C5877000000
Apple,,8C7B9D000000
Apple,,9027E4000000
Apple,,90840D000000
Apple,,9803D8000000
Apple,,A46706000000
Apple,,A4B197000000
Apple,,B8C75D000000
Apple,,B8FF61000000
Apple,,C42C03000000
Apple,,C82A14000000
Apple,,C8BCC8000000
Apple,,CC08E0000000
Apple,,D49A20000000
Apple,,D83062000000
Apple,,D89E3F000000
Apple,,D8A25E000000
Apple,,DC2B61000000
Apple,,E0F847000000
Apple,,E4CE8F000000
Apple,,E8040B000000
Apple,,E80688000000
Apple,,F0B479000000
Apple,,F81EDF000000

It's possible that the OUI database may need updating.  The other entries you have may have been defined by the controller.

There's no easy way to list all the tables, and it might not be as useful to you since there's about 1000 database tables.  The tables also do not have definitions, so the name of the tables is the most you're going to have to extrapolate what data is maintained in that table.  The OUI table is the source for all device identification over MAC address.  Can you give an example MAC address that isn't in the output?  There's 184 entries (as of AMP 7.6.0).  When I scanned your list by eye, the first 5-10 were also in the list.

This command from the cli would probably give you what you're looking for:

# dbcsv "select device_type, mac from client_first_last where device_type like '%iP%'"

If you still want to see all of the tables in the database, you can log into the database console with the "db" command and do "\dt" to see a list of tables, or "\d <table_name>" to see a table's schema. "\q" would get you back to a bash prompt.

Yes this is what I was looking for!

41000 clients... will cat <file> | sort | uniq

and get results:

Apple iPad,00:14:10
Apple iPad,00:92:C3
Apple iPad,00:9D:13
Apple iPad,00:E0:4C
Apple iPad,04:54:53
Apple iPad,10:40:F3
Apple iPad,10:93:E9
Apple iPad,14:10:9F
Apple iPad,18:20:32
Apple iPad,1C:AB:A7
Apple iPad,28:6A:BA
Apple iPad,28:E7:CF
Apple iPad,34:51:C9
Apple iPad,34:C0:59
Apple iPad,40:30:04
Apple iPad,40:B3:95
Apple iPad,44:2A:60
Apple iPad,48:02:2A
Apple iPad,4C:B1:99
Apple iPad,54:26:96
Apple iPad,5C:96:9D
Apple iPad,64:20:0C
Apple iPad,64:A3:CB
Apple iPad,64:E6:82
Apple iPad,70:73:CB
Apple iPad,70:DE:E2
Apple iPad,74:E1:B6
Apple iPad,74:E2:F5
Apple iPad,78:CA:39
Apple iPad,7C:6D:62
Apple iPad,7C:C3:A1
Apple iPad,84:29:99
Apple iPad,84:85:06
Apple iPad,88:53:95
Apple iPad,8C:2D:AA
Apple iPad,8C:7B:9D
Apple iPad,8C:FA:BA
Apple iPad,90:84:0D
Apple iPad,98:B8:E3
Apple iPad,98:FE:94
Apple iPad,9C:04:EB
Apple iPad,A4:67:06
Apple iPad,A4:D1:D2
Apple iPad,B0:65:BD
Apple iPad,B4:F0:AB
Apple iPad,B8:C7:5D
Apple iPad,B8:F6:B1
Apple iPad,B8:FF:61
Apple iPad,BC:3B:AF
Apple iPad,BC:67:78
Apple iPad,C4:2C:03
Apple iPad,C8:BC:C8
Apple iPad,D0:23:DB
Apple iPad,D8:00:4D
Apple iPad,D8:30:62
Apple iPad,D8:A2:5E
Apple iPad,E0:2A:82
Apple iPad,E0:B9:BA
Apple iPad,E0:F8:47
Apple iPad,E4:8B:7F
Apple iPad,E8:06:88
Apple iPad,EC:85:2F
Apple iPad,F0:D1:A9
Apple iPad,F4:1B:A1
Apple iPad,FC:25:3F
Apple iPhone,00:1B:63
Apple iPhone,00:1C:B3
Apple iPhone,00:1D:4F
Apple iPhone,00:1E:52
Apple iPhone,00:1E:C2
Apple iPhone,00:1F:5B
Apple iPhone,00:1F:F3
Apple iPhone,00:21:E9
Apple iPhone,00:22:41
Apple iPhone,00:23:12
Apple iPhone,00:23:32
Apple iPhone,00:23:6C
Apple iPhone,00:23:DF
Apple iPhone,00:24:36
Apple iPhone,00:25:00
Apple iPhone,00:25:4B
Apple iPhone,00:25:BC
Apple iPhone,00:26:08
Apple iPhone,00:26:4A
Apple iPhone,00:26:B0
Apple iPhone,00:26:BB
Apple iPhone,00:88:65
Apple iPhone,00:C6:10
Apple iPhone,00:F4:B9
Apple iPhone,04:0C:CE
Apple iPhone,04:1E:64
Apple iPhone,04:54:53
Apple iPhone,04:F7:E4
Apple iPhone,0C:74:C2
Apple iPhone,0C:77:1A
Apple iPhone,10:40:F3
Apple iPhone,10:93:E9
Apple iPhone,10:9A:DD
Apple iPhone,14:10:9F
Apple iPhone,14:5A:05
Apple iPhone,14:8F:C6
Apple iPhone,18:20:32
Apple iPhone,18:34:51
Apple iPhone,18:9E:FC
Apple iPhone,18:E7:F4
Apple iPhone,1C:AB:A7
Apple iPhone,20:C9:D0
Apple iPhone,24:AB:81
Apple iPhone,28:37:37
Apple iPhone,28:6A:BA
Apple iPhone,28:CF:DA
Apple iPhone,28:E0:2C
Apple iPhone,28:E7:CF
Apple iPhone,28:EF:01
Apple iPhone,30:F7:C5
Apple iPhone,34:15:9E
Apple iPhone,34:51:C9
Apple iPhone,34:C0:59
Apple iPhone,3C:D0:F8
Apple iPhone,40:30:04
Apple iPhone,40:A6:D9
Apple iPhone,40:B3:95
Apple iPhone,40:D3:2D
Apple iPhone,44:2A:60
Apple iPhone,44:4C:0C
Apple iPhone,44:D8:84
Apple iPhone,46:4C:0C
Apple iPhone,48:02:2A
Apple iPhone,48:60:BC
Apple iPhone,4C:B1:99
Apple iPhone,50:EA:D6
Apple iPhone,54:26:96
Apple iPhone,58:1F:AA
Apple iPhone,58:55:CA
Apple iPhone,58:67:1A
Apple iPhone,58:B0:35
Apple iPhone,5C:59:48
Apple iPhone,5C:95:AE
Apple iPhone,5C:96:9D
Apple iPhone,60:33:4B
Apple iPhone,60:C5:47
Apple iPhone,60:FA:CD
Apple iPhone,60:FB:42
Apple iPhone,64:20:0C
Apple iPhone,64:80:99
Apple iPhone,64:A3:CB
Apple iPhone,64:B9:E8
Apple iPhone,64:E6:82
Apple iPhone,68:09:27
Apple iPhone,68:96:7B
Apple iPhone,68:A8:6D
Apple iPhone,6C:3E:6D
Apple iPhone,6C:C2:6B
Apple iPhone,70:56:81
Apple iPhone,70:73:CB
Apple iPhone,70:DE:E2
Apple iPhone,74:E1:B6
Apple iPhone,74:E2:F5
Apple iPhone,78:A3:E4
Apple iPhone,78:CA:39
Apple iPhone,7C:11:BE
Apple iPhone,7C:6D:62
Apple iPhone,7C:C3:A1
Apple iPhone,7C:C5:37
Apple iPhone,7C:D1:C3
Apple iPhone,84:29:99
Apple iPhone,84:85:06
Apple iPhone,84:FC:FE
Apple iPhone,88:53:95
Apple iPhone,88:C6:63
Apple iPhone,8C:2D:AA
Apple iPhone,8C:58:77
Apple iPhone,8C:7B:9D
Apple iPhone,8C:FA:BA
Apple iPhone,90:27:E4
Apple iPhone,90:84:0D
Apple iPhone,98:03:D8
Apple iPhone,98:B8:E3
Apple iPhone,98:D6:BB
Apple iPhone,98:FE:94
Apple iPhone,9C:04:EB
Apple iPhone,9C:20:7B
Apple iPhone,A4:67:06
Apple iPhone,A4:D1:D2
Apple iPhone,A8:96:8A
Apple iPhone,A8:FA:D8
Apple iPhone,B0:65:BD
Apple iPhone,B4:F0:AB
Apple iPhone,B8:17:C2
Apple iPhone,B8:8D:12
Apple iPhone,B8:C7:5D
Apple iPhone,B8:F6:B1
Apple iPhone,B8:FF:61
Apple iPhone,BC:3B:AF
Apple iPhone,BC:52:B7
Apple iPhone,BC:67:78
Apple iPhone,C0:9F:42
Apple iPhone,C4:2C:03
Apple iPhone,C8:33:4B
Apple iPhone,C8:BC:C8
Apple iPhone,CC:08:E0
Apple iPhone,D0:23:DB
Apple iPhone,D4:9A:20
Apple iPhone,D8:00:4D
Apple iPhone,D8:30:62
Apple iPhone,D8:9E:3F
Apple iPhone,D8:A2:5E
Apple iPhone,D8:D1:CB
Apple iPhone,DC:2B:61
Apple iPhone,E0:B9:BA
Apple iPhone,E0:C9:7A
Apple iPhone,E0:CB:1D
Apple iPhone,E0:F8:47
Apple iPhone,E4:8B:7F
Apple iPhone,E4:CE:8F
Apple iPhone,E8:06:88
Apple iPhone,EC:85:2F
Apple iPhone,F0:4F:7C
Apple iPhone,F0:A2:25
Apple iPhone,F0:B4:79
Apple iPhone,F0:CB:A1
Apple iPhone,F0:D1:A9
Apple iPhone,F0:DC:E2
Apple iPhone,F4:AC:C1
Apple iPhone,F8:1E:DF
Apple iPhone,FC:25:3F
Apple iPod,00:1D:4F
Apple iPod,00:1E:52
Apple iPod,00:1E:C2
Apple iPod,00:22:41
Apple iPod,00:23:32
Apple iPod,00:23:DF
Apple iPod,00:24:36
Apple iPod,00:25:00
Apple iPod,00:25:4B
Apple iPod,00:25:BC
Apple iPod,00:26:08
Apple iPod,00:26:4A
Apple iPod,00:26:B0
Apple iPod,00:26:BB
Apple iPod,00:88:65
Apple iPod,00:C6:10
Apple iPod,04:1E:64
Apple iPod,14:10:9F
Apple iPod,14:5A:05
Apple iPod,14:8F:C6
Apple iPod,18:E7:F4
Apple iPod,28:37:37
Apple iPod,34:15:9E
Apple iPod,40:D3:2D
Apple iPod,44:D8:84
Apple iPod,58:55:CA
Apple iPod,58:B0:35
Apple iPod,5C:59:48
Apple iPod,60:33:4B
Apple iPod,60:FB:42
Apple iPod,64:B9:E8
Apple iPod,70:56:81
Apple iPod,70:73:CB
Apple iPod,7C:6D:62
Apple iPod,7C:C5:37
Apple iPod,88:C6:63
Apple iPod,8C:2D:AA
Apple iPod,8C:7B:9D
Apple iPod,90:27:E4
Apple iPod,90:84:0D
Apple iPod,98:D6:BB
Apple iPod,B8:C7:5D
Apple iPod,B8:F6:B1
Apple iPod,BC:67:78
Apple iPod,C4:2C:03
Apple iPod,D4:9A:20
Apple iPod,D8:30:62
Apple iPod,D8:A2:5E
Apple iPod,E8:06:88
Apple iPod,F0:B4:79
Apple iPod,F4:1B:A1
Apple iPod,F8:1E:DF
iPhone,28:E0:2C
iPhone,58:1F:AA
iPhone,98:D6:BB
iPod,18:E7:F4

 I'm then using this to put into our windows 2012 server DHCP into a policy to give lower lease times, I'm using a power shell command:

set-dhcpserver4policy -Name "Apple iPad" -Condition OR -mac EQ,001410*,0092C3* etc...

Great Post!  Thanks for the Tips

Randy

I'm more interested in getting information from what the controllers are getting via DHCP fingerprinting rather than the OUI database...