06-12-2014 08:32 AM - edited 03-16-2019 11:05 PM
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.
Solved! Go to Solution.
07-30-2014 12:57 AM
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
07-30-2014 12:57 AM
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide