help with a SQL query to get list of Users and the Groups

[Shashank devadiga]

Hello, community,

I found the below SQL query in the older thread to get a list of Users and the Groups they are in.

But it's not fetching the user IDs that are not assigned to any of the user groups.

Can I get help in modifying this query to do so?

select a.OH_Client, a.OH_NAME, b.OH_Name 

from 

OH a, 

oh b, 

USRG

where 

a.OH_IDNR = USRG_USR_Idnr and

b.OH_Idnr = USRG_USG_Idnr

order by 3

Thank you!

[Peter Grundler]

Hi Shashank,

try the below statement from the Automic database training:

select OH_Client, OH_Name
 from USR
 inner join OH on (OH_Idnr = USR_OH_Idnr)
 where USR_OH_Idnr not in (select USRG_USR_Idnr from USRG)
 and OH_DeleteFlag = 0
 order by OH_Client, OH_Name;

regards,
Peter

------------------------------
Automic Certified Professional/Expert & Broadcom Knight

For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
------------------------------

[Olgun Onur Ozmen]

Here it is :) 

select relationfinal.oh_client ,relationfinal.username,relationfinal.usr_firstname , relationfinal.usr_lastname , relationfinal.usr_email1, relationfinal.userisactive , relationfinal.ldapconnection,
decode(oh.oh_name,null,'Not Assigned',oh.oh_name) as RoleName from (
select  usg.USG_OH_Idnr, relation.*  from (
select  usrg.USRG_USR_IDNR, usrg.USRG_USG_Idnr, activeusers.* from (
select usr.USR_OH_IDNR, oh.OH_IDNR , oh.oh_client,
oh.oh_name as username,
usr.USR_FIRSTNAME,
usr.USR_LASTNAME,
usr.USR_EMAIL1,
decode(usr.usr_active,'1','Yes','0','No') as UserIsActive,
decode(usr.USR_Ldap,'1','Enabled','0','Disabled') as LDAPConnection
from automic.usr usr, automic.oh oh
where 1=1
and usr.USR_OH_Idnr=oh.OH_Idnr
and usr.usr_active='1'
and oh.oh_client='11' --  must write client number
and OH.oh_deleteflag='0') activeusers 
LEFT OUTER JOIN automic.usrg usrg  ON activeusers.USR_OH_IDNR =  usrg.USRG_USR_IDNR ) relation
LEFT OUTER JOIN automic.usg usg  ON relation.USRG_USG_Idnr =  usg.USG_OH_Idnr ) relationfinal
LEFT OUTER JOIN automic.oh oh  ON relationfinal.USG_OH_Idnr =  oh.OH_IDNR

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------

[Rick Murray]

Thanks.  Another query that I can make use of  ;-)

[Lester Chew]

can someone convert to MSSQL query?

[Christoph Rekers]

Hi @Lester Chew,

ChatGPT can (this is really great and at the same time really terrifying...this was my first attempt with ChatGPT)

SELECT
    relationfinal.oh_client,
    relationfinal.username,
    relationfinal.usr_firstname,
    relationfinal.usr_lastname,
    relationfinal.usr_email1,
    relationfinal.userisactive,
    relationfinal.ldapconnection,
    ISNULL(NULLIF(oh.oh_name, ''), 'Not Assigned') AS RoleName
FROM
    (
        SELECT
            usg.USG_OH_Idnr,
            relation.*
        FROM
            (
                SELECT
                    usrg.USRG_USR_IDNR,
                    usrg.USRG_USG_Idnr,
                    activeusers.*
                FROM
                    (
                        SELECT
                            usr.USR_OH_IDNR,
                            oh.OH_IDNR,
                            oh.oh_client,
                            oh.oh_name AS username,
                            usr.USR_FIRSTNAME,
                            usr.USR_LASTNAME,
                            usr.USR_EMAIL1,
                            CASE usr.usr_active WHEN '1' THEN 'Yes' ELSE 'No' END AS UserIsActive,
                            CASE usr.USR_Ldap WHEN '1' THEN 'Enabled' ELSE 'Disabled' END AS LDAPConnection
                        FROM
                            usr AS usr
                            JOIN oh AS oh ON usr.USR_OH_Idnr = oh.OH_Idnr
                        WHERE
                            usr.usr_active = '1'
                            AND oh.oh_client = '1000' -- must write client number
                            AND OH.oh_deleteflag = '0'
                    ) AS activeusers
                    LEFT OUTER JOIN usrg AS usrg ON activeusers.USR_OH_IDNR = usrg.USRG_USR_IDNR
            ) AS relation
            LEFT OUTER JOIN usg AS usg ON relation.USRG_USG_Idnr = usg.USG_OH_Idnr
    ) AS relationfinal
    LEFT OUTER JOIN oh AS oh ON relationfinal.USG_OH_Idnr = oh.OH_IDNR

Cheers

Christoph 

------------------------------
----------------------------------------------------------------
Automic AE Consultant and Trainer since 2000
----------------------------------------------------------------
------------------------------

[Lester Chew]

Yup it could be your best friend and yet be your worst enemy.  Thank you for the conversion to MSSQL.