Order by rand () not working well
Really struggling with a script which determines who the owner of the ticket will be.. When I run this in Mysql Workbench it works fine. But when I run it on a ticket I get the same result every time (not randmomized). When I run it as a report in KACE it just runs forever and winds up in error:
Error Running Report
Internal error (6). Please contact KACE customer support.My plan is to run an update after this and assign the owner of the ticket. I figured Id also use a limit by 1 after the random sort. Question on that, does all information from the select apply to the update? Like in this case, use user_id still user_id? I've tried moving all of this to update as well and no change. So for example, my update would look something like this:
SET
HD_TICKET.OWNER_ID = USER_ID
Below is what Im running in a report where Ive explicitly entered a ticket # to look at. Been stumped for two months looking at this on and off. When I Comment out the rand() it works fine.
SELECT
DISTINCT
ORG1.USER_LABEL_JT.USER_ID
#, HD_TICKET.ID
FROM
(HD_TICKET, USER, ASSET)
JOIN ASSET A1 ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN HD_TICKET HD1 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
#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
(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
(S.STATE = "Opened"
and (SERVICE.ID = 21 or SERVICE.ID = 31) /* Employee onboard */
and ST.ORDINAL = 3 /* Stage 3 */)
and ORG1.HD_TICKET.ID = 17090
#AND HD_TICKET.OWNER_ID = '0'
ORDER BY RAND()
Answers (5)
Top Answer
Well, I kinda remade the query but I think it works now, I tried and didn't crash my kbox XD
------------------
SELECT ORG1.HD_TICKET.ID ID
FROM ORG1.HD_TICKET
LEFT JOIN ORG1.HD_TICKET_CHANGE TC
ON TC.HD_TICKET_ID = ORG1.HD_TICKET.ID
WHERE ORG1.HD_TICKET.OWNER_ID = 0
AND TC.ID = <CHANGE_ID>
------------------
UPDATE ORG1.HD_TICKET T
SET T.OWNER_ID = (
SELECT ULJ.USER_ID
FROM ORG1.USER_LABEL_JT ULJ
LEFT JOIN USER U
ON U.ID = ULJ.USER_ID
LEFT JOIN (SELECT ID, NAME FROM ASSET WHERE ASSET.ASSET_TYPE_ID = 1) A
ON A.ID = U.LOCATION_ID
WHERE ULJ.LABEL_ID = 223
AND T.CUSTOM_FIELD_VALUE3 = A.NAME
ORDER BY RAND()
LIMIT 1
)
WHERE T.ID in (<TICKET_IDS>)
------------------
Edit: I modified and ran the subquery in Beekeepers Studio and the result returned on avarege in 0.09s, and if I remove all filters and the LIMIT to get all 3269 (nice) users it returns in 0.3s average, so kinda performant I guess
Comments:
-
Thank you so much.. Havent tested yet, this is a totally new approach and Im learning a ton.. so thank you. Will advise! - barchetta 2 years ago
-
Plugged it in and it worked right out of the box. What a great contribution.. I dont understand what the asset_type_id=1 is yet. Must be a location classification perhaps? Thank you so much. I hope others learn some things from this very long thread.. maybe the longest yet :) - barchetta 2 years ago
-
The asset_type=1 is just to get location assets only, reduce the select a bit, it's not actually needed, I could just JOIN ASSET, but it would join everything, this way I can cut on column and rows. I appended the select query to this answer, so it's complete. - IgorAngelini 2 years ago
-
Really glad I could help, this one was fun to solve, not gonna lie. - IgorAngelini 2 years ago
-
Was fun for me to get all the ducks in a row and understand where kace stored everything in tables. This helped me immensely. I was stuck in a rut and just needed a new approach. The one thing I learned here is just because a sql editor "works" doesnt mean it will work in kace. Which is a bummer. I guess that is why I kept hanging on to what I had.. couldnt understand why kace wouldnt run it. Thanks again. - barchetta 2 years ago
-
Im not sure if you will see this @igorangelini but I just wanted to let you know that I have not forgotten all of the help you gave me here. You really got me out of a jamb. This rule has been in production for some time now and works well. Im about to take it and use it with some minor tweaks to solve a similar problem. Thank you. - barchetta 2 years ago
-
Really glad I could help. I learned a lot about KACE's database, and SQL in general helping people here, so it was a win-win. Sadly, I don't have enough time nowadays to keep helping here. But it was fun while it lasted. - IgorAngelini 2 years ago
I'm not the best person to help you because we don't use Help Desk, but here is my 2 cents anywoo:
Try using the select SQL to select one ticket and use the update to randomize the user.
Here is the update query
SELECT ORG1.USER_LABEL_JT.USER_ID
FROM ORG1.USER_LABEL_JT
WHERE ORG1.USER_LABEL_JT.LABEL_ID = 223
ORDER BY RAND()
LIMIT 1
)
WHERE HD_TICKET.ID in (<TICKET_IDS>)
Here I'm using a subquery to get one random user from your LABEL.ID 223 and set he/her as your ticket owner, note that if you select 10 tickets, those tickets you have the same owner.
If that doesn't help, let me know.
Comments:
-
Thanks, I tried this and it errored unless I added join statements into the update area.. which is kinda why I was asking if all the select information is passed on to the update. Doesnt seem like it.
I ended up with this.. it runs for approx 7 minutes and then it pulls in the wrong person who is unrelated.
I'll throw in my SELECT below it.. It is of course changed because Im not running a report. I opened a ticket on this (the report) and KACE wants me to double my RAM to 8gb for the VM.. which Im not optimistic about. If you have any more suggestions let me know. I know Im asking a lot here.. this isnt an easy one.. So really appreciate you even took a wack at it. Kinda shed some light on some things.. One thing I will say, is it is documented in forums that rand () in a sub query in maria DB can cause performance issues. But hell, I dont care how long it takes unless it brings all other processes to a hault. Here is how long this one took.. pretty humorus.
01/26/2022 11:37:50> Starting: 01/26/2022 11:37:50 .... updated 1 rows 01/26/2022 11:46:03> Ending: 01/26/2022 11:46:03
UPDATE
(HD_TICKET,USER)
JOIN ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN HD_TICKET AS HD1 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 =(
SELECT ORG1.USER_LABEL_JT.USER_ID
FROM
ORG1.USER_LABEL_JT
WHERE ORG1.USER_LABEL_JT.LABEL_ID = 223
ORDER BY RAND()
LIMIT 1
)
WHERE
HD_TICKET.ID = <TICKET_IDS>
SELECT
distinct HD_TICKET.ID
from
(HD_TICKET, USER)
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
JOIN ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN HD_TICKET AS HD1 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
,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings
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
S.STATE = "Opened"
and (SERVICE.ID = 21 or SERVICE.ID = 31) /* Employee onboard */
and ST.ORDINAL = 3 /* Stage 3 */ - barchetta 2 years ago -
I tried adding all of the conditions into the where statement.. ran for 7 minutes and then the ticket was "Unassigned". I take the same update and convert it to a select as below and run in mysql and not only does it run instantly but it runs correctly (randomizes).
SELECT
ORG1.USER_LABEL_JT.USER_ID
FROM
(HD_TICKET, USER)
JOIN
ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN
HD_TICKET AS HD1 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.USER_LABEL_JT.LABEL_ID = 223
AND ORG1.USER.LOCATION_ID = ORG1.ASSET.ID
AND ORG1.USER.ID = USER_LABEL_JT.USER_ID
AND ORG1.LABEL.ID = 223
AND ORG1.HD_TICKET.ID = 17090
ORDER BY RAND()
LIMIT 1 - barchetta 2 years ago -
I'm at home right now, so I can't even test this, but I helped a guy not a log time ago (relativity speaking, I'm kinda new here) and I made an update query work (link below), I notice one difference.
Try changing this:
UPDATE HD_TICKET
SET HD_TICKET.OWNER_ID = [...] you know the rest
See if it runs, tomorrow when I get some free time at the office I will take another go at it, let me know what changes with this modification.
By the way, how many users on the label 223? Are we talking tens? Hundreds? Thousands? I don't know the inner workings of MariaDB (or SQL thinking of it, I'm that "learn as you go along" kinda guy), this ORDER BY RAND() should not be such a performance hog, at least not without a significant number of rows to order, I need to read more on that.
https://www.itninja.com/question/ticket-custom-field-for-location - IgorAngelini 2 years ago
Ok, let's start fresh.
I'm gonna write what I understood that you want, if it's correct, great, if not please correct me
You want that a ticket that doesn't have an owner, be automatically assigned one random user from the LABEL.ID = 223 as the owner.
Assuming that the statement above is correct, try the following:
Change the frequency to "On Ticket Save"
------------------
SELECT ORG1.HD_TICKET.ID ID
FROM ORG1.HD_TICKET
LEFT JOIN ORG1.HD_TICKET_CHANGE TC
ON TC.HD_TICKET_ID = ORG1.HD_TICKET.ID
WHERE ORG1.HD_TICKET.OWNER_ID = 0
AND TC.ID = <CHANGE_ID>
------------------
UPDATE ORG1.HD_TICKET T
SET T.OWNER_ID = (
SELECT ORG1.USER_LABEL_JT.USER_ID
FROM ORG1.USER_LABEL_JT
WHERE ORG1.USER_LABEL_JT.LABEL_ID = 223
ORDER BY RAND()
LIMIT 1
)
WHERE T.ID in (<TICKET_IDS>)
------------------
You really don't need all those joins, the only thing the select query needs to get is ONE ID, if you are not going to WHERE or SELECT the info on the table you are joining, you don't need that table, you are just making the process unnecessarily less performant. (That changes for the report, of course)
In the UPDATE query, you just need to get that ONE ticket ID and get ONE random user to change the HD_TICKET.OWNER_ID field.
With this rule, anytime, any ticket, in any queue, new or old, has the owner set to "unassigned" (including when it's created) it's going to select that ticket, and change the HD.OWNER_ID. Fell free to refine the select query.
An example, if you want to only change tickets from queue ID 3, on WHERE add:
AND HD_TICKET.HD_QUEUE_ID = 3
Note that you don't need to join HD_QUEUE, you just need its ID.
If you want to select Status, however, as there are multiple ones in the same table (mine for example, has an "opened" status with ID 1 and ID 10, because I created 2 queues), in this case, for future proofing is good to WHERE it based on the name of the status, not its ID, to do this just:
LEFT JOIN HD_STATUS
ON HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
[...]WHERE[...]
AND HD_STATUS.NAME = "Opened"
I'm WHEREing it, so I need to JOIN it.
Hope this helps.
Comments:
-
Thanks.. I will try this.. I stopped last night in frustration.. and now going to try again. but I didnt go into detail on the logic but can likely modify logic you provided. Here is the logic.
If user is a member of label 223 and the users location name is same as custom_field_value3 then randomize the sort, select 1 and make the owner. That is why I have call the joins and the where:
WHERE
ORG1.USER_LABEL_JT.LABEL_ID = 223
AND ORG1.USER.LOCATION_ID = ORG1.ASSET.ID
AND ORG1.USER.ID = USER_LABEL_JT.USER_ID
AND ORG1.LABEL.ID = 223
What I tried last night is in the select statement assigning a variable to the result. Which worked fine in SQL editor.. but when I brought it over to kace it didnt seem to pass that variable down into UPDATE.
Anyway, Im going to look at what you provided and see if I can make it work.
A HUGE thank you for spending all this time... Will respond back as soon as I can.. hopefully no fires this morning to put out. - barchetta 2 years ago-
Ok let me see if a got what you want.
When a ticket is created, change the owner of the ticket to one random user from the label ID 223 from the same location as the ticket submitter.
Correct? - IgorAngelini 2 years ago
-
Here is an example of a short scriot that works in SQL editor. it doesnt have all of the arguments I need to make sure it is the right process ticket.. but it successfully matches the city to the user location and then the user to the label. Maybe will help you.. this took me hours and hours to figure out.
SELECT
ORG1.USER_LABEL_JT.USER_ID
FROM
(HD_TICKET, USER)
JOIN
ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN
HD_TICKET AS HD1 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.USER_LABEL_JT.LABEL_ID = 223
AND ORG1.USER.LOCATION_ID = ORG1.ASSET.ID
AND ORG1.USER.ID = USER_LABEL_JT.USER_ID
AND ORG1.LABEL.ID = 223
AND ORG1.HD_TICKET.ID = 17090
ORDER BY RAND()
LIMIT 1 - barchetta 2 years ago
No fires so I can try this.. Step 1. Ran your rule exactly as is and it selected one of the users from the label.. Now to patch in the join statements and the where conditions.... here we go....
Ok let me see if a got what you want.
When a ticket is created, change the owner of the ticket to one random user from the label ID 223 from the same location as the ticket submitter.
Correct?
If so, try this update query:
UPDATE ORG1.HD_TICKET T
SET T.OWNER_ID = (
SELECT ULJ.USER_ID
FROM ORG1.USER_LABEL_JT ULJ
WHERE ULJ.LABEL_ID =223
AND (SELECT U.LOCATION_ID
FROM USER U
WHERE U.ID = T.SUBMITTER_ID)
=
(SELECT UU.LOCATION_ID
FROM USER UU
LEFT JOIN USER_LABEL_JT
ON UU.ID = USER_LABEL_JT.USER_ID
WHERE USER_LABEL_JT.LABEL_ID = 223)
ORDER BY RAND()
LIMIT 1
)
WHERE T.ID in (<TICKET_IDS>)
Edit: I think that ULJ.LABEL_ID =223 is redundant, as I'm filtering the subquery, but I'm going to leave it there any way
Comments:
-
Close.. but not the ticket submitter, same location as defined in Custom Field value 3. This is an onboard of a new user.. so new user custom field 3 must match that of any users location (say for example New York. So maybe that is 100.. then out of those 100, who is a member of label 223? that should be 1-5 . Sorry for all this confusion, I was trying to simplify my original post. - barchetta 2 years ago
-
Yea, but you kinda what something hyper specific, so the more details the better. As soon as I get a window at work will try to reformulate the update query - IgorAngelini 2 years ago
-
But just to clarify, the ticket's Custom Field Value 3, correct? What exactly is on there, is it the name of the location or the id? If the name, is it EXACTLY equal to your asset location name?
And if it's the name, oh boy I'm gonna have fun with that shi... coff coff beautiful self-referenced ASSET table! - IgorAngelini 2 years ago-
In custom field 3 the actual city name exists EX. "New York". And yes, it is EXACTLY equal to the location name, I use a sql query in the field to pull a list of all locations. If you look at my joins, you will see how I did the city name match. Again, My joins and my where statement work PERFECTLY in My sql workbench. It took my hours and hours to get the city name to match and it does! - barchetta 2 years ago
-
Here is an example of a short script that works in SQL editor. it doesnt have all of the arguments I need to make sure it is the right process ticket.. but it successfully matches the city to the user location and then the user to the label. Maybe will help you.. this took me hours and hours to figure out.
SELECT
ORG1.USER_LABEL_JT.USER_ID
FROM
(HD_TICKET, USER)
JOIN
ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN
HD_TICKET AS HD1 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.USER_LABEL_JT.LABEL_ID = 223
AND ORG1.USER.LOCATION_ID = ORG1.ASSET.ID
AND ORG1.USER.ID = USER_LABEL_JT.USER_ID
AND ORG1.LABEL.ID = 223
AND ORG1.HD_TICKET.ID = 17090
ORDER BY RAND()
LIMIT 1 - barchetta 2 years ago -
After the server rebooted I commented out my joins and left what is under FROM.. and it still took 2 minutes to run.. so clearly I do not know what Im doing when it comes to scripting in kace.
UPDATE
ORG1.HD_TICKET T
SET T.OWNER_ID = (
SELECT
ORG1.USER_LABEL_JT.USER_ID
FROM
(HD_TICKET, USER,ORG1.USER_LABEL_JT)
#JOIN ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
#JOIN HD_TICKET AS HD1 ON ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 = ASSET.NAME
# JOIN USER_LABEL_JT AS JT1 ON USER_LABEL_JT.USER_ID = USER.ID
#JOIN LABEL ON LABEL.ID AND USER_LABEL_JT.LABEL_ID
WHERE
ORG1.USER_LABEL_JT.LABEL_ID = 223
ORDER BY RAND()
LIMIT 1
)
WHERE T.ID in (<TICKET_IDS>) - barchetta 2 years ago -
This is pretty crazy. the following script works like 50% of the time.. half the time it gets the wrong user. It comes up with a correct user quite often. However, it takes 30 seconds to run. Our user DB is maybe 1000 so it shouldnt take that long.
UPDATE
ORG1.HD_TICKET T
SET T.OWNER_ID = (
SELECT
ORG1.USER_LABEL_JT.USER_ID
FROM
(HD_TICKET, USER)
JOIN ASSET ON ORG1.ASSET.ID = ORG1.USER.LOCATION_ID
JOIN HD_TICKET AS HD1 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.USER_LABEL_JT.LABEL_ID = 223
AND ORG1.USER.LOCATION_ID = ORG1.ASSET.ID
AND ORG1.USER.ID = USER_LABEL_JT.USER_ID
ORDER BY RAND()
LIMIT 1
)
WHERE T.ID in (<TICKET_IDS>) - barchetta 2 years ago
-
Meanwhile, I threw all the joins I had into the script you wrote in update... ran it.. and now my server is "crashed".. been running for over 20 minutes.. cant get back into it. Oh the joy. - barchetta 2 years ago
-
Yea, I think all those JOINS are not healthy XD - IgorAngelini 2 years ago
-
They work perfectly in MYSQL workbench. But not saying they are healthy, but work. - barchetta 2 years ago