"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
Answers (3)
I think i've got it.
ORG1.USER.ID = USER_LABEL_JT.USER_ID AND USER_LABEL_JT.LABEL_ID = 223
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 years ago-
Sorry Hobbsy, just seeing this. I just completed this script with the help of some people here. It is to assgn an "office coordinator" at the location that a new user is being onboarded to. We have an office coordinator queue as part of our onboarding to make sure a desk is set up and a badge is assigned etc.. Was a real bear but again, thanks to this forum I got it done. Lesson learned: Just because you can make it run in your favority SQL editor doesnt mean kace will run it which his disappointing. I really wish quest would step up to the plate.. Im sad to say this because Ive put a lot of work into kace, but our tech staff has had enough of kace and it looks like we will be moving on within the next year. People demand products that look and feel like the new tech.. and kace just doesnt have it. - barchetta 2 years ago