/build/static/layout/Breadcrumb_cap_w.png
01/31/2019 208 views

I have service desk report that I run every month and would like to add ldap query to differentiate users by what ldap label they belong to. So I created a new ldap label named "Sites - Users" and imported users so they can get associated with the label I created. Now I need to add the label on to the report I already have.

Any help would be appreciated.

SELECT 

HD_TICKET.ID,

HD_CATEGORY.NAME AS CATEGORY,

HD_TICKET.CREATED,

HD_TICKET.TIME_CLOSED,

HD_STATUS.NAME AS STATUS_NAME,

S.FULL_NAME AS SUBMITTER_NAME,

O.FULL_NAME AS OWNER_NAME,

HD_TICKET.CUSTOM_FIELD_VALUE2,

MONTH(HD_TICKET.CREATED) AS 'Month',

HD_TICKET.CUSTOM_FIELD_VALUE3

FROM 

HD_TICKET  JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) 

JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CREATED between '2019/01/01' and '2019/01/31')  ORDER BY CREATED

5 Comments   [ + ] Show comments

Comments

  • Do you want the report to only return tickets where users in the Sites - Users label were submitters?
  • I am hoping I can return all users but maybe have a column for User Label on my report if possible. So that I can see all submitters whether they are on the label or not.

    Does that make sense?
    • Will there be other labels in the future that you want to add to the report in that column? So right now you have users with no label and users with "Sites - Users". Will you eventually have "Site2 - Users", etc.? If you just want to differentiate between users that have this specific label or not, that is different than showing a type of label for every user (whether it be blank or something else).

      It might help if you explain your end goal and what you are trying to accomplish.
      • Thanks Chuck.

        I run the report above every to look at our tickets. We have HQ employees and Field employees and we are trying to find out how many of our tickets are from HQ users and how many from field users. I have 1 ldap label for "Sites - Users" and I will create one for "CORP - Users".

        Maybe I was thinking about it the wrong way where anyone that showed with Sites - Users would be in this label and everyone else that is not in this label or blank would be HQ user.

        I am really not a sql guy and tried using the wizard but had no luck.

        Much appreciated!
  • Would it be simpler to add something like this to the query CS_DOMAIN not like '%company.local%' to go after the domain those user's machines belong to or do something like LABEL.NAME = 'sites - Users'?
  • How are you differentiating between HQ and Field employees during your LDAP import? The easiest solution would be to have that field you are using placed into a custom user field. Including user fields in the ticket report will be easier than checking for the presence or absence of a label.
  • I have 2 imports that brings my users from AD (HQ OU and Sites OU). Then I created a new ldap label "Sites - Users" thinking that might help differentiating the users.

There are no answers at this time