can anybody try this sql query on a ccm6?

Unanswered Question
Sep 23rd, 2008

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.

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
stephan.steiner Tue, 09/23/2008 - 08:48

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

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

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