/build/static/layout/Breadcrumb_cap_w.png

Best Practices Question


K1000 Reporting By Location

07/27/2017 1024 views
Hello, 

I was wondering if anyone could help me with a report based on location. We had one based on the department but in one of the last revisions they changed it to an asset number. I would like to be able to pull "Submitted Tickets Monthly by Location". Any help would be much appreciated. 
4 Comments   [ + ] Show comments

Comments

  • How are you defining Location for your tickets and where in your tickets are the associated location values being stored?
  • Thank you for the quick response. The location is being defined by the asset name.
  • In what way, is the Asset name prefixed in someway? In which case you just need to write a report that has an "if AssetName begins with" type query
  • This is what I have currently:

    SELECT HD_TICKET.CUSTOM_FIELD_VALUE3, HD_TICKET.ID, HD_TICKET.TITLE, date_format(HD_TICKET.CREATED, '%M') as MONTH, O.FULL_NAME AS OWNER_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED, HD_PRIORITY.NAME AS PRIORITY, HD_CATEGORY.NAME AS CATEGORY, HD_STATUS.NAME AS STATUS, HD_IMPACT.NAME AS IMPACT, S.FULL_NAME AS SUBMITTER_NAME FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) ORDER BY CUSTOM_FIELD_VALUE3

    Basically the report used to pull the "Department", since they have removed that and is considered "location" now I can't seem to pull the name just the location ID.
    • This is my rule:
      SELECT HD_TICKET.ID AS ID FROM HD_TICKET
      JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
      WHERE HD_TICKET.SUBMITTER_ID <>0

      Update SQL:
      UPDATE HD_TICKET T
      JOIN USER S ON (S.ID = T.SUBMITTER_ID)
      SET T.CUSTOM_FIELD_VALUE3 = LOCATION_ID WHERE T.ID=<TICKET_IDS>

Be the first to answer this question

 
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