/build/static/layout/Breadcrumb_cap_w.png
07/23/2018 308 views
I am not a SQL guy at all, so I need help here.  Have imported "Department" from AD into User Custom Field 1.  I need this field to show up on a couple of simple reports, so I can sort the report by Department.
Answer Summary:
7 Comments   [ + ] Show comments

Comments

  • You just need to add CUSTOM_FIELD_VALUE0 to your Report but the Wizard should give you the option of adding the Custom fields to your reports.
  • I can get it to show on a new report using the wizard, but I am looking to add this to an existing report that the wizard won't modify. I tried to copy and paste the appropriate lines into the existing report, but I am a SQL dumby. Thanks for the help, I will keep plugging away as time permits.
  • To add the Custom field to existing reports, open the SQL editor in KACE, and drop in the text CUSTOM_FIELD_VALUE0.

    Example your script might look something like this:-

    SELECT ID AS TICK,
    CREATED,
    TITLE,
    TIME_CLOSED
    FROM HD_TICKET

    With your line added it would look like this:-

    SELECT ID AS TICK,
    CREATED,
    TITLE,
    CUSTOM_FIELD_VALUE0 AS DEPT,
    TIME_CLOSED
    FROM HD_TICKET
  • This still did not work. Maybe I am not communicating clearly. I am trying a different track now, because I think your responses have steered me in that direction. See next comment...
  • I don't display Department, which is a Custom_1 in the user table, on tickets. Since I couldn't make it appear in a report of closed tickets, maybe it's just easier to work on getting it displayed on tickets, then it will be easier to include in a report. Plus, I can then sort open tickets by department in my ticket list, etc. So, now looking for the way to select Custom_1 from the User table to display as Custom_3 on a ticket. This should be much easier.
  • Chuck, I put your update statement in exactly as you have it. I get this in Last Run Log...
    07/30/2018 16:20:49> Starting: 07/30/2018 16:20:49 07/30/2018 16:20:49> Executing Select Query... 07/30/2018 16:20:49> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<TICKET_IDS>' at line 5] in EXECUTE("update HD_TICKET as T set T.CUSTOM_FIELD_VALUE3 = ( select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE where USER_FIELD_VALUE.USER_ID = SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID = 1) where T.ID = <TICKET_IDS>;")
    • Make sure that they are actually open and closing angle brackets around TICKET_IDS. Sometimes when copying and pasting from the web they get replaced with encoding.

      Also, if you are using a rule to update multiple tickets and not just on ticket save you will need to change "where T.ID = <TICKET_IDS>" to "WHERE T.ID in (<TICKET_IDS>)"
      • Thanks, Chuck. Retyped the statement, just to make sure there were no formatting issues. Still no joy. Quest support, on the phone for another issue, looked at it and said <TICKET_IDS> was probably the issue because they couldn't find that field?

        SQL Statement...
        update HD_TICKET as T set T.CUSTOM_FIELD_VALUE3=(select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE where USER_FIELD_VALUE.USER_ID=SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID=1) where T.ID=<TICKET_IDS>;

        Last Run Log...
        07/31/2018 09:48:10> Starting: 07/31/2018 09:48:10 07/31/2018 09:48:10> Executing Select Query... 07/31/2018 09:48:10> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<TICKET_IDS>; and (HD_TICKET.ID = 673)' at line 1] in EXECUTE("update HD_TICKET as T set T.CUSTOM_FIELD_VALUE3=(select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE where USER_FIELD_VALUE.USER_ID=SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID=1) where T.ID=<TICKET_IDS>; and (HD_TICKET.ID = 673) ")
    • Try removing the semicolon from the end of my statement.

      It's unfortunate that the Quest tech support people don't know that the <TICKET_IDS> variable is populated by the results of the select statement. Can you post your select statement? It might not be returning the ID of the ticket in the ID field.
      • Um, I don't have a select statement here. Just the update statement you posted. Again, I am a SQL dumby.
      • Then that's the problem. There needs to be a select statement to populate the <TICKET_IDS> variable. I can post one that will work.
      • Chuck, I am gonna owe you a six pack when this is done. That definitely got us closer. I can now see the run log saying it updated a row, and appened a comment just so I could be sure. But, the Department field i am displaying is still blank. I know I am so very close...
      • Here is my latest run log...
        07/31/2018 12:39:42> Starting: 07/31/2018 12:39:42 07/31/2018 12:39:42> Executing Select Query... 07/31/2018 12:39:42> selected 1 rows 07/31/2018 12:39:42> Adding ticket comments... 07/31/2018 12:39:42> updated 1 tickets 07/31/2018 12:39:42> Executing Update Query... 07/31/2018 12:39:42> updated 1 rows 07/31/2018 12:39:42> Ending: 07/31/2018 12:39:42
  • Custom ticket fields are 0 based in the database, so custom field 1 is CUSTOM_FIELD_VALUE0. If you were referring to the custom field three in the UI, then the database statement should be CUSTOM_FIELD_VALUE2.
    • Chuck, I am so indebted to you, it's ridiculous. I know this is what these forums are for, but your patience with me here was amazing. Thank you, thank you, thank you! If you are ever in Dallas or just east of Dallas, I will buy you a beer! Or three!
    • Teach a man to fish...I just got the field added to the report as well, so that makes me super happy and will get some colleagues off my back. "ChuckSteel" will be whispered with Reverence around this office for a while.

Answer Chosen by the Author

2
You can do this (setting the custom field in the ticket) to the user's department. Create a new rule that runs on ticket save. I have my custom field's default value set to "Set on Save" and my rule matches those tickets. If you want to update historical tickets, then you might have to change the rule slightly.

This update statement will then change the custom ticket field to contain the custom user field:
update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE3 = (
select USER_FIELD_VALUE.FIELD_VALUE FROM USER_FIELD_VALUE
where USER_FIELD_VALUE.USER_ID = SUBMITTER_ID and USER_FIELD_VALUE.FIELD_ID = 1) where 
        T.ID = <TICKET_IDS>;

Here is a simple select statement you can use:
SELECT HD_TICKET.ID
FROM HD_TICKET
JOIN USER_FIELD_VALUE DEPARTMENT on DEPARTMENT.USER_ID = SUBMITTER_ID and DEPARTMENT.FIELD_ID = 1
WHERE HD_TICKET.CUSTOM_FIELD_VALUE3 != DEPARTMENT.FIELD_VALUE
This will select tickets where the custom field value is not the same as the submitter's department.


Also, to add custom user fields to a report, you need to add a join statement to the USER_FIELD_VALUE table:
JOIN USER_FIELD_VALUE DEPARTMENT as DEPARTMENT.USER_ID = SUBMITTER_ID and DEPARTMENT.FIELD_ID = 1

This will allow you to select the department as DEPARTMENT.FIELD_VALUE as DEPARTMENT. A super basic report for open tickets would then look like this:
SELECT HD_TICKET.ID, SUBMITTER.FULL_NAME, DEPARTMENT.FIELD_VALUE as DEPARTMENT
FROM HD_TICKET
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
JOIN USER_FIELD_VALUE DEPARTMENT on DEPARTMENT.USER_ID = HD_TICKET.SUBMITTER_ID and DEPARTMENT.FIELD_ID = 1
WHERE HD_TICKET.TIME_CLOSED = "0000-00-00 00:00:00"
You can add your other custom user fields with additional join statements. For instance, we have the AD description in custom user field 3.
JOIN USER_FIELD_VALUE DESCRIPTION on DESCRIPTION.USER_ID = HD_TICKET.SUBMITTER_ID and DESCRIPTION.FIELD_ID = 3




Answered 07/30/2018 by: chucksteel
Red Belt