Gathering Wireless Vendor Info from the SQL DB

My manager asked for a report of the BlackBerry users and their associated wireless provider. Given this is all stored in the BES SQL DB, it shouldn't be a problem to get the info - but there are some things to figure out before it works right:

1. There are two fields in the BES SQL DB that refer to a wireless provider. The first is the VendorID field, which specifies the actual maker of the BlackBerry device itself, regardless of what network it is currently connected to or roaming on.

2. The second is the Home Network field, which actually doesn't make sense because it shows you which wireless network provider the device is connected to right now, so it may not actually be the home network, but a roaming partner.

For example I have a user whose device is a T-Mobile device and their home network would obviously be T-Mobile, however they currently appear as Cingular under Home Network.

To get these values out of the SQL DB we need a query, here is the one I use to pull the data into Excel:

SELECT UserConfig.DisplayName, vHandheldSummaryInfo.VendorID, vHandheldSummaryInfo.HomeNetwork
FROM BesConfig.dbo.UserConfig UserConfig, BesConfig.dbo.vHandheldSummaryInfo vHandheldSummaryInfo
WHERE vHandheldSummaryInfo.PIN = UserConfig.PIN

Note that you need to join these two tables in Microsoft Query using the PIN field, not the default "id" field that it starts with, otherwise your data won't match. Haven't discovered why, perhaps their are two different id fields in the two tables. Anyway, just use PIN because that is a common key that I know is unique.

So once you run this query you get the user's Display Name, Vendor ID, and Home Network. Now to get the device's carrier I need just the Vendor ID field but I like to see what Home Network people are on, it is interesting to see where your people are in the world and what unique names other wireless carriers have, such as "CCT Boatphone" (!). (Apparently from the British Virgin Islands, but is it really for boats? a cruise ship? hmmm...)

One final issue is that the Vendor ID is in decimal format, so I wasn't quite sure which was which. The Home Network let me see which vendor ID matched up with which vendor name, but after doing a bunch of work for that I figured out that the vendor id's are stored in where else... the Vendors.xml file!

While the Vendors.xml file did in fact contain all the matchings between vendor ID and vendor name, the id's in this file were in Hex format! :( After a little hacking in Excel with the HEX2DEC formula, I came up with the following master table below of all vendors RIM supports:

Hex (vendor.xml)	Decimal (BES DB)	Provider Name
64	100	T-Mobile US
65	101	AWS
66	102	Cingular Wireless
67	103	Nextel
68	104	Sprint PCS
69	105	Verizon
6B	107	Rogers
6D	109	BWA (Aliant/Sasktel)
6F	111	MM02 DE
71	113	Telfort
72	114	T-Mobile DE/AU
73	115	TIM
74	116	Hutchison
75	117	Bouygues
76	118	SFR
77	119	Orange_France
78	120	VODAFONE (UK)
79	121	Telcel
7A	122	Telstra
7B	123	T-Mobile (UK)
7C	124	Vodafone Germany
7D	125	MM02 UK/IRL/NL
7E	126	Telus
7F	127	SMART
80	128	Starhub
81	129	Telefonica
82	130	Swisscom
83	131	Cable and Wireless
84	132	Vodafone (IT)
85	133	Vodafone (ES)
86	134	T-Mobile (NL)
87	135	Cincinnati Bell
89	137	Vodafone Austria
8A	138	Vodafone Australia
8B	139	Vodafone Ireland
8C	140	Vodafone Sweden
8D	141	CSL
8E	142	Orange UK
8F	143	Vodafone New Zealand
90	144	SingTel
91	145	Globe
92	146	Optus
93	147	Orange Mobistar
95	149	Bharti
96	150	KPN
98	152	Proximus
99	153	Vodafone_Portugal
9B	155	BT_Mobile
9C	156	Earthlink
9E	158	E-Plus
9F	159	BASE
A0	160	Dobson
A1	161	Vodafone_Egypt
A2	162	Orange_Switzerland
A4	164	Triton
A5	165	Maxis
A6	166	Vodafone_Denmark_(TDC)
A7	167	MobileOne_(Voda_Singapore)
A8	168	Vodacom

So now I can run my SQL query and easily match up the vendor ID's in decimal with the vendor names from this table. Cool.