Automic Workload Automation

 View Only
Expand all | Collapse all

SQLI query to show users with groups

  • 1.  SQLI query to show users with groups

    Posted Sep 04, 2019 07:27 AM
    Hello Community,

    i would like to write an SQLI query that shows all users (including FirstName and LastName) and their groups.

    "Show all users" would be like:

    select USR.USR_FirstName, USR.USR_LastName, USR.USR_EMail1
    from USR
    order by USR_LastName

    But how can i show the groups of the users too?

    Can anybody help please?

    Kind regards

    Andre


  • 2.  RE: SQLI query to show users with groups

    Posted Sep 04, 2019 07:32 AM
    ​Hi,

    You need to SQL-join the table with the groups.

    I think it's USG for the group names, and you need to join it via USGR for the user-to-group relations.

    Hth,


  • 3.  RE: SQLI query to show users with groups

    Posted Sep 04, 2019 07:35 AM
    Hey, thanks for the really quick answer!

    The table USG doesn't seem to contain the group names. Also i'm not firm enough in SQL on how to perform the join syntax. Unfortunately.

    Further help would be really great.


  • 4.  RE: SQLI query to show users with groups

    Posted Sep 04, 2019 08:10 AM
    Edited by Diane Craddock Sep 11, 2019 09:14 AM

    ​Hi.

    The group names are an Object in the OH table. USRG binds users to (potentially more than one) group identifiers, USR maps those group identifiers to a unique ID (or vice versa :) and that unique ID is the group name in OH.

    Here is a thread that has asked almost the same question:

    https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1435&MID=801626&CommunityKey=2e1b01c9-f310-4635-829f-aead2f6587c4&tab=digestviewer

    I tried the solution from that thread on my Oracle DB, works (even though I'm not 100% sure I understand how).  This gives you account names and their user groups, and obviously, if an account has multiple groups, he is output multiple times.

    That's only account names however. One can link that to USR probably by joining "OH.OH_IDNR on USR.USR_OH_IDNR" to include the data from the USR table. But I'm sorry, I just tried this for a brief time and I couldn't get it to work right away, I'm also no SQL query expert either. I'd need to invest more time into this which I don't have right now. Does your company possibly have a DBA or sql expert who can help with contructing that join statement?

    Best regards,

    Carsten




  • 5.  RE: SQLI query to show users with groups

    Posted Sep 04, 2019 08:36 AM
    Thanks so much Carsten!

    The query works great. I'll try to add the columns for First- and LastName.

    Afterwards i will come back to this thread to post the results.

    Best regards


  • 6.  RE: SQLI query to show users with groups

    Posted Sep 04, 2019 11:49 AM
    Edited by Pete Wirfs Sep 04, 2019 11:57 AM


    ------------------------------
    Pete
    ------------------------------



  • 7.  RE: SQLI query to show users with groups

    Posted Sep 05, 2019 03:56 AM
    Hi Pete! Thanks for your code.

    Unfortunately it doens't work right. The error is: "DB-Fehlermeldung: 'U00003754 Datenbank-Fehler in SQLI-Variable: 'U00003590 UCUDB - DB-Fehler: 'OCIStmtExecute', 'ERROR ', '', 'ORA-00923: Schlüsselwort FROM nicht an erwarteter Stelle gefunden''

    It seems that the statement is somehow not wellformed.

    Best regards

    Andre



  • 8.  RE: SQLI query to show users with groups

    Posted Sep 05, 2019 02:11 AM
    ​Hi Andre,

    you're welcome, good to hear that other query gives you a starting point. If you do happen to figure out that join across those two tables, certainly feel free to post it as I'm sure that will benefit others in the future :)

    Best regards,
    Carsten



  • 9.  RE: SQLI query to show users with groups

    Posted Sep 05, 2019 05:31 AM
    Hi Carsten,

    here's the code we are happy with:

    select a.OH_NAME, b.OH_Name, USR_FirstName,USR_LastName
    from
    OH a,
    oh b,
    USRG, USR
    where
    a.OH_IDNR = USRG_USR_Idnr and
    b.OH_Idnr = USRG_USG_Idnr and
    usr_oh_idnr=a.oh_idnr
    order by 1

    Kind regards

    Andre

    ------------------------------
    Automic Engineer
    comdirect Bank AG
    ------------------------------



  • 10.  RE: SQLI query to show users with groups

    Posted Sep 05, 2019 05:15 AM
    Edited by Frank Muffke Sep 05, 2019 05:16 AM
    Ahoi

    this one should fit your needs:
    select a.OH_Client as "Client", a.OH_NAME as "User Name", USR_FIRSTNAME, USR_LASTNAME, USR_EMAIL1,b.OH_Name as "USRG Name" from OH a, oh b, USRG, USR
    where a.OH_IDNR = USR_OH_IDNR
    and a.OH_CLIENT not in (0)
    and b.OH_CLIENT not in (0)
    and a.OH_IDNR = USRG_USR_Idnr
    and b.OH_Idnr = USRG_USG_Idnr
    and a.OH_DELETEFLAG = 0
    and b.OH_DELETEFLAG = 0
    order by 1,3;

    cheers, Wolfgang

    ------------------------------
    I know I do really know it!
    ------------------------------



  • 11.  RE: SQLI query to show users with groups

    Posted Sep 05, 2019 07:02 AM
    Thanks a lot Wolfgang!

    Your code works as well :) It's basically the same output our code creates.

    Kind regards

    Andre

    ------------------------------
    Automic Engineer
    comdirect Bank AG
    ------------------------------



  • 12.  RE: SQLI query to show users with groups

    Posted Sep 09, 2019 05:04 AM
    Hi Wolfgang,

    is there something like a "close thread" feature here? This topic may be closed but i can't find the appropriate button or link.

    Cheers, André

    ------------------------------
    Automic Engineer
    comdirect Bank AG
    ------------------------------



  • 13.  RE: SQLI query to show users with groups

    Posted Sep 10, 2019 05:34 AM
    Sry no idea...

    cheers, Wolfgang

    ------------------------------
    I know I do really know it!
    ------------------------------



  • 14.  RE: SQLI query to show users with groups

    Posted Sep 11, 2019 05:10 AM
    > is there something like a "close thread" feature here?

    I don't think there is. BUT you can probably still edit your original ​post and prefix the subject with "(resolved)" or something.

    Kudos for good online etiquette :)

    Best,
    Carsten


  • 15.  RE: SQLI query to show users with groups

    Posted Sep 11, 2019 06:38 AM
    I don't think there is. BUT you can probably still edit your original ​post and prefix the subject with "(resolved)" or something.

    Unfortunately not :(

    Thanks Carsten for your reply :) It would be a very helpful feature for the community here.


    ------------------------------
    Automic Engineer
    comdirect Bank AG
    ------------------------------