cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
777
Views
0
Helpful
1
Replies

CUCM SQL Query AND statement

tpfrankli
Level 5
Level 5

So either I'm dense or it just doesn't exist. All I really want is a report of what devices are associated to an application user. We have a situation wherein we have two CTI applications and if a single device is associated to both the world comes crashing down. I wasn't able to find this in the GUI interface so I took to the CLI via SQL and I have my query working.

The following syntax will spit back all associated devices for user X:

/*Show Devices associated with PBC-Genesys User*/
select d.fkdevice,dv.name as DeviceName
from applicationuserdevicemap as d
inner join device as dv on d.fkdevice=dv.pkid
where fkapplicationuser = '5cad9858-2934-82ae-e5a4-6822ac390ed0'

And by simply changing the fkapplicationuser I can see devices associated with user Y:

/*Show Devices associated with WBC-Genesys User*/
select d.fkdevice,dv.name as DeviceName
from applicationuserdevicemap as d
inner join device as dv on d.fkdevice=dv.pkid
where fkapplicationuser = '0bbacbb2-0c38-7023-936c-733aedd8a9d8'

Now I would think a simple AND at the bottom of this query would return me a list of devices that happened to be associated to both but that isn't the case. Is this possible? Here's what I tried that doesn't work.

/*Show Devices associated with PBC-Genesys AND WBC-Genesys Users*/
select d.fkdevice,dv.name as DeviceName
from applicationuserdevicemap as d
inner join device as dv on d.fkdevice=dv.pkid
where fkapplicationuser = '5cad9858-2934-82ae-e5a4-6822ac390ed0' AND fkapplicationuser = '0bbacbb2-0c38-7023-936c-733aedd8a9d8'

I'm the first to admit I suck at SQL but I'm trying slowly to wrap my head around it.

Thanks.

~~~ Rate helpful posts Blog - http://tripplehelix.net
1 Accepted Solution

Accepted Solutions

a.janssen
Level 1
Level 1

This should give you the required information:

admin:run sql select d.name, d.description from applicationuserdevicemap audm1, applicationuserdevicemap audm2, applicationuser au1, applicationuser au2, device d where audm1.fkdevice=d.pkid and audm2.fkdevice=d.pkid and audm1.fkapplicationuser=au1.pkid and audm2.fkapplicationuser=au2.pkid and au1.name='Service1' and au2.name='Service2'
name            description
=============== ==================================
SEP123412341234 (1210999) Logged Out
SEP123412341235 (1210999) Logged Out
 

Just replace Service 1&2 with your exact servicename.

Hth

Andre

View solution in original post

1 Reply 1

a.janssen
Level 1
Level 1

This should give you the required information:

admin:run sql select d.name, d.description from applicationuserdevicemap audm1, applicationuserdevicemap audm2, applicationuser au1, applicationuser au2, device d where audm1.fkdevice=d.pkid and audm2.fkdevice=d.pkid and audm1.fkapplicationuser=au1.pkid and audm2.fkapplicationuser=au2.pkid and au1.name='Service1' and au2.name='Service2'
name            description
=============== ==================================
SEP123412341234 (1210999) Logged Out
SEP123412341235 (1210999) Logged Out
 

Just replace Service 1&2 with your exact servicename.

Hth

Andre

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: