can anybody try this sql query on a ccm6?

Unanswered Question
Sep 23rd, 2008
User Badges:
  • Silver, 250 points or more

I ran into something weird today.. I almost cannot believe that it's not my fault. I wanted to get the device name, userid and name of the udp logged in on a given phone.. so I wrote the query and sent it.. and what I get back is the username for all 3 fields. I figured that must be my error and my SQL syntax is wrong. So I went for a simpler query that I know works.. getting the device name and username for all devices that have an extension mobility user logged in. The query to get this is


select u.userid, u.pkid, d.name, d.pkid FROM device d

inner join extensionmobilitydynamic e ON d.pkid = e.fkdevice

inner join enduser u on u.pkid = e.fkenduser


You'll see you have the match between device and userid. However, look at the pkids.. they're the same. If you select just the user, you have an entirely different pkid.


Here's another query to try:


select d.name, d.pkid, d2.name, d2.pkid FROM device d

inner join extensionmobilitydynamic e ON d.pkid = e.fkdevice

inner join device d2 on d2.pkid = e.fkdevice_currentloginprofile


It should give you the name of each device and user device profile logged into the device. Once again, the pkids are the same.. and they're the pkid from the d (so the physical device).


I ran this on a second system we have and the result is the same. So could anyone quickly verify that those queries are correct and if so run them to see if they get the same result? Somehow I do not dare open a case at this point as I can almost not believe this could be so wrong.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
stephan.steiner Tue, 09/23/2008 - 08:48
User Badges:
  • Silver, 250 points or more

Alright.. I dare call it a bug now.. I went to check every pkid from the three tables.. the extensionmobilitydynamic contains the proper data. I even created a small ccm db on my sql server to test my queries and see if they are valid (they are) then finally ran the queries via cli and guess what.. the results are okay.. but via axl they are incorrect.


I'm getting good at finding bugs again.. that's number 2 today (okay, I discovered the start of the other issue yesterday).

ckatene Tue, 09/23/2008 - 13:18
User Badges:
  • Bronze, 100 points or more

the problem appears to be having identical column names in your select statement. in the first query you have two columns named 'pkid'. if you alias one of them it appears to work correctly:


> select u.userid, u.pkid, d.name, d.pkid FROM device d

inner join extensionmobilitydynamic e ON d.pkid = e.fkdevice

inner join enduser u on u.pkid = e.fkenduser

;


> > > Warning: URL Host: 9.1.1.19 vs. 9.1.1.19


userid,pkid,name,pkid

te kairangi,cf0c9146-7e57-62cc-dc03-4162c37d9b3b,SEP0017E06A3B76,cf0c9146-7e57-62cc-dc03-4162c37d9b3b

tamihana,1b99c410-f519-c278-ce9a-86a48df86edf,SEP000E381BA072,1b99c410-f519-c278-ce9a-86a48df86edf

jeremy,d5bac516-ef96-7f93-d950-3991ee02dc7b,SEP000BFD32E500,d5bac516-ef96-7f93-d950-3991ee02dc7b

j1b,a92f4d85-8773-a106-3f71-9e18c72aa4d0,SEP000DED6C47BC,a92f4d85-8773-a106-3f71-9e18c72aa4d0

callum,253335f4-fe57-a35b-0766-1baf7c27f1f4,SEP0017E0A1B399,253335f4-fe57-a35b-0766-1baf7c27f1f4

tk,951d5c26-c50b-9770-4284-0fadeb9523a4,SEP0003E363064A,951d5c26-c50b-9770-4284-0fadeb9523a4



> > select u.userid, u.pkid user_pkid, d.name, d.pkid FROM device d

inner join extensionmobilitydynamic e ON d.pkid = e.fkdevice

inner join enduser u on u.pkid = e.fkenduser

;


> > > Warning: URL Host: 9.1.1.19 vs. 9.1.1.19


userid,user_pkid,name,pkid

te kairangi,400359ad-fc84-7dfe-fd56-308f50a3de55,SEP0017E06A3B76,cf0c9146-7e57-62cc-dc03-4162c37d9b3b

tamihana,50621083-ecdc-14b4-e2e3-a1f90f1851a6,SEP000E381BA072,1b99c410-f519-c278-ce9a-86a48df86edf

jeremy,113e2371-bc72-1c51-243a-e85d8066b6f5,SEP000BFD32E500,d5bac516-ef96-7f93-d950-3991ee02dc7b

j1b,440e945f-e517-d9d2-13f2-93b840f8f57a,SEP000DED6C47BC,a92f4d85-8773-a106-3f71-9e18c72aa4d0

callum,23d52323-d535-20c4-b841-df921bfcdacd,SEP0017E0A1B399,253335f4-fe57-a35b-0766-1baf7c27f1f4

tk,2e81151e-16b3-5f7f-8ff0-da5b54a8ece0,SEP0003E363064A,951d5c26-c50b-9770-4284-0fadeb9523a4

> >



stephan.steiner Wed, 09/24/2008 - 05:53
User Badges:
  • Silver, 250 points or more

Thanks for the workaround.. I forwarded the info to Cisco and I expect a bug to be filed soon. Meanwhile I can continue to work with the workaround.

Actions

This Discussion