CUCM SQL Query AND statement

[tpfrankli]

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.

[a.janssen · Accepted solution]

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