/build/static/layout/Breadcrumb_cap_w.png

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()



0 Comments   [ + ] Show comments

Answers (5)

Answer Summary:
Posted by: IgorAngelini 3 months ago
Second Degree Blue Belt
1

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 3 months 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 3 months 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 3 months ago
    • Really glad I could help, this one was fun to solve, not gonna lie. - IgorAngelini 3 months 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 3 months ago
Posted by: IgorAngelini 3 months ago
Second Degree Blue Belt
0

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


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 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 3 months 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 3 months 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 3 months ago
Posted by: IgorAngelini 3 months ago
Second Degree Blue Belt
0

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 3 months 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 3 months 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 3 months ago
Posted by: barchetta 3 months ago
Brown Belt
0

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....

Posted by: IgorAngelini 3 months ago
Second Degree Blue Belt
0

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 3 months 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 3 months 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 3 months 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 3 months 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 3 months 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 3 months 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 3 months 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 3 months ago
    • Yea, I think all those JOINS are not healthy XD - IgorAngelini 3 months ago
      • They work perfectly in MYSQL workbench. But not saying they are healthy, but work. - barchetta 3 months ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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