cfg database question...

NICE CXone API & Integrations
1

I reckon this should be easy because I’ve definitely worked it out before - but I can’t remember how I did it… :frowning:

I need to find a reference between cfg_Agent and cfg_Person within the database Table structure. I’ve tried the DBID’s, CSID’s and pretty much everything else I can think of but still not able to find the correct pair of Foreign/Primary keys. I expect that there is an interim Table (cfg_???) which might be used to connect them but I haven’t found it yet…

Help please???

Thanks!

TT

2

cfg_agent ? are you sure ? or do you mean cfg_agent_login ? check cfg_login_info table - it links persons to agent_logins.

1 Like
3

Yes - that’s what I meant… ;D

But I just tried this, in the same Query;

cfg_Agent_Login.dbid = cfg_Login_Info.Agent_Login_dbid
and also
cfg_Login_Info.Person_dbid = cfg_Person.dbid

  • returns 35,000+ rows - even with duplicates removed…

Not sure what I am doing wrong…?

Thanks!

TT

4

Hi,

It should be work:

SELECT p.dbid, p.user_name, p.last_name, p.first_name, l.login_code
FROM cfg_person p LEFT OUTER JOIN
cfg_login_info i ON p.dbid = i.person_dbid LEFT OUTER JOIN
cfg_agent_login l ON i.agent_login_dbid = l.dbid

BR,
bandorka

2 Likes
5

Or a more complex one:

SELECT
DISTINCT p.dbid, p.user_name, p.last_name, p.first_name,
l.login_code,
CASE WHEN p.state = 2 THEN ‘disabled’ ELSE ‘’ END AS status,
s.name FROM
cfg_person AS p
LEFT OUTER JOIN cfg_login_info AS i ON p.dbid = i.person_dbid
LEFT OUTER JOIN cfg_agent_login AS l ON i.agent_login_dbid = l.dbid
LEFT OUTER JOIN cfg_switch AS s ON l.switch_dbid = s.dbid
WHERE (p.is_agent = 2) AND (i.agent_login_dbid IS NOT NULL)

BR,
Bandorka

1 Like
6

Thank you - I will try them out now… :slight_smile:

7

Not exactly the same but had to apply it in Hyperion, after telling it that non-fully-joined queries were ok(!). Came up with this from the Hyperion SQL Output;

SELECT AL4.dbid, AL4.user_name, AL4.last_name, AL4.first_name, AL2.login_code, AL3.name
FROM dbo.cfg_login_info AL1 FULL OUTER JOIN dbo.cfg_person AL4 ON (AL1.person_dbid=AL4.dbid) FULL OUTER JOIN dbo.cfg_agent_login AL2 ON (AL1.agent_login_dbid=AL2.dbid) FULL OUTER JOIN dbo.cfg_switch AL3 ON (AL2.switch_dbid=AL3.dbid)
WHERE (AL4.is_agent=2 AND (NOT AL1.agent_login_dbid IS NULL))

It seems to have worked, since I now only have 188 entries… :slight_smile:

Thanks!

TT