/build/static/layout/Breadcrumb_cap_w.png

"if member of" ldap label sql?

Can someone give me an example of a where statement which says if a user is a member member of an ldap label?  


Im stuck here:


ORG1.USER.LOCATION_ID = ORG1.ASSET.ID  AND 


I need the and to check to see if the user has a location_ID the same as the Asset.ID also is a member of a group.  I hope this clear.  In fact for giggles, here is the whole thing thus far. If I remove the AND it runs and comes back with users list amungst the other declared fields. Keep in mind, I barely know how to spell sql... Im struggling through this but it is fun to get this far on my own.


SELECT 


`ASSET`.`ID` AS ASSETID,

`ASSET`.`NAME` AS ASSETNAME,

ORG1.USER.LOCATION_ID AS LOCID,

ORG1.HD_TICKET.ID AS TICKID,

ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 AS NULOC,

ORG1.USER.FULL_NAME,

ORG1.LABEL.ID,

ORG1.USER_LABEL_JT.USER_ID 

FROM 

USER

JOIN ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID

JOIN HD_TICKET ON ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 = ASSET.NAME

JOIN USER_LABEL_JT ON USER_LABEL_JT.USER_ID = USER.ID

JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID

WHERE

ORG1.HD_TICKET.HD_QUEUE_ID = 35 AND

 ORG1.HD_TICKET.ID = 16991 AND

ORG1.USER.LOCATION_ID = ORG1.ASSET.ID  AND 




0 Comments   [ + ] Show comments

Answers (3)

Posted by: barchetta 1 month ago
Blue Belt
0

Anybody? Bueller? :)

Posted by: barchetta 1 month ago
Blue Belt
0

I think i've got it.

ORG1.USER.ID = USER_LABEL_JT.USER_ID AND USER_LABEL_JT.LABEL_ID = 223

Posted by: Hobbsy 1 month ago
Red Belt
0

What are you planning to do with this SQL when you get it? What is the purpose of the code is it a report or a ticket rule??


Comments:
  • Sorry Hobbsy... Im just catching up here. It is for a ticket rule to systematically assign a user (as owner of a ticket) if member of that label AND the location field matches an onboarded user to assign the office manager a ticket to perform required office prep tasks.

    I completed it, but now I was told there could be more than one office manager per location and was asked to randomize the ticket assignments. Its been a real bear because I have no idea what Im doing. It was done until that last request.. for some reason when I try to randomize Im getting a user that isnt even in the same location. Working in mysql but at a loss. If I remove that last line it essentially works.. but I think it just chooses the first user in the result. I SUSPECT I have to somehow move everything from update except the set command to the select section but I just am failing to make that happen.

    SELECT
    distinct HD_TICKET.ID
    from
    HD_TICKET
    join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID and C.ID = <CHANGE_ID>
    join HD_SERVICE_TICKET ST on HD_TICKET.SERVICE_TICKET_ID = ST.ID
    join HD_SERVICE SERVICE on ST.HD_SERVICE_ID = SERVICE.ID
    join HD_TICKET PARENT on HD_TICKET.PARENT_ID = PARENT.ID and PARENT.IS_PARENT
    join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID
    ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings
    where
    S.STATE = "Opened"
    and (SERVICE.ID = 21 or SERVICE.ID = 31) /* Employee onboard */
    and ST.ORDINAL = 3 /* Stage 3 */


    UPDATE

    USER
    JOIN ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
    JOIN HD_TICKET ON ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 = ASSET.NAME
    JOIN USER_LABEL_JT ON USER_LABEL_JT.USER_ID = USER.ID
    JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID

    SET
    HD_TICKET.OWNER_ID = USER_LABEL_JT.USER_ID

    WHERE
    ORG1.HD_TICKET.HD_QUEUE_ID = 35 AND
    ORG1.USER.LOCATION_ID = ORG1.ASSET.ID and
    ORG1.USER.ID = USER_LABEL_JT.USER_ID AND USER_LABEL_JT.LABEL_ID = 223 AND
    ORG1.LABEL.ID = 223 AND
    #HD_TICKET.OWNER_ID = '0' AND
    HD_TICKET.ID = <TICKET_IDS>
    ORDER BY RAND () - barchetta 3 weeks ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ