This document was generated from CDN thread
Created by: David Staudt on 08-03-2010 07:00:22 PM
This is a situation where <executeSQLQuery> comes in handy.
You can use the MS SQL Enterpries Manager on the UCM console to inspect the schema and contents of the CallManager database to build your query. I believe the tables you'll want to look at will be: device, typeclass - possibly joining others as needed depending on what fields you want returned.
i.e.
<SQL>select name, description [other fields] from device where tkclass=254 and name like '1001*</SQL>
tkclass=254 indicates the device is a device profile (see the TypeClass table.)
Subject: RE: Axl query - Multiple "GetDeviceProfile" request
Replied by: Dan-Anders Hook on 10-03-2010 06:16:08 PM
Hi,
I think you need to use "AS" to separate the equally named fields. Here is a sample query which worked for me (if my memory serves me correctly)
Select D.Name,typeProduct.name,D.description,Location.name,N.DNOrPattern,DN.Display,CallingSearchSpace.name,CallingSearchSpace0.name, CallingSearchSpace1.name from device D,Numplan N, DeviceNumPlanMap DN,typeProduct,Location,CallingSearchSpace,CallingSearchSpace As CallingSearchSpace0,CallingSearchSpace As CallingSearchSpace1 WHERE (((D.tkProduct IN (Select enum from TypeProduct where Name LIKE 'Cisco 7940%' ESCAPE '\'))) ) and (tkProduct NOT IN (21,22) AND tkClass = 1) And D.PKID = DN.fkDevice And DN.fkNumplan = N.PKID And D.tkProduct *= typeProduct.enum And D.fkLocation *= Location.pkid And N.fkCallingSearchSpace_SharedLineAppear *= CallingSearchSpace.pkid And N.fkCallingSearchSpace_CFA *= CallingSearchSpace0.pkid And N.fkCallingSearchSpace_CFNA *= CallingSearchSpace1.pkid ORDER BY D.Name;
Kind regards,
//Dan
Subject: RE: Axl query - Multiple "GetDeviceProfile" request
Replied by: David Staudt on 10-03-2010 08:47:43 PM
Right...use 'as' to alias each field that could be a duplicate. I believe this is a limitation of the jdbc drive being used by AXL. I.e.:
SELECT Device.name as devicename,Device.description,TypeModel.name as typeModelName,PhoneTemplate.name as PhoneTemplateName,SoftkeyTemplate.name as softeyTemplateName FROM Device
join PhoneTemplate on Device.fkPhoneTemplate = PhoneTemplate.pkid
join SoftkeyTemplate on Device.fkSoftkeyTemplate = SoftkeyTemplate.pkid
join TypeModel on Device.tkModel = TypeModel.Enum
where Device.tkClass = '254' and Device.name like '%1204%'
