Help with unique identifiers when pulling reports from kace helpdesk
I have a helpdesk setup for cutomer service tickets for all of our locations across the country. each location has a manager that we assign as the owner of the tickets for their location. All the locations use a 3 character airport code (example - SAN for San Diego) We have 4 regional managers that are over certain groups of locations. I have reports that are scheduled to run weekly and monthly off this helpdesk. Each Regional Manager gets a report of all tickets broken down by location for his or her area. My issue is that the reports are starting to grab locations from other areas into the wrong report. The problem is that for each managers report i used the 3 character airport code in the report creator to specify which locations go into each report. Example of my issue: I have one report for the west coast. One of the locations it should pull is SAN - San Diego. That same report is pulling in our Miami location because the manager in miami has SAN in his last name (Santiago). Is there a way in the report creation tool to add spaces before and after the SAN so it wont include everything that has the letters SAN in them? I just want it to pull the one with the matching 3 digit code.
Answers (0)
Be the first to answer this question
SELECT HD_TICKET.CUSTOM_FIELD_VALUE4,O.FULL_NAME AS OWNER_NAME,HD_TICKET.CUSTOM_FIELD_VALUE7,HD_TICKET.ID,HD_TICKET.CREATED,HD_TICKET.TIME_OPENED,HD_TICKET.TIME_CLOSED FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 4) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) AND ((O.FULL_NAME like '%SEA%') OR (O.FULL_NAME like '%PDX%') OR (O.FULL_NAME like '%RNO%') OR (O.FULL_NAME like '%LAS%') OR (O.FULL_NAME like '%OGG%') OR (O.FULL_NAME like '%HNL%') OR (O.FULL_NAME like '%SJC%') OR (O.FULL_NAME like '%SFO%') OR (O.FULL_NAME like '%SAN%') OR (O.FULL_NAME like '%SNA%') OR (O.FULL_NAME like '%ONT%') OR (O.FULL_NAME like '%OAK%') OR (O.FULL_NAME like '%LAX%') OR (O.FULL_NAME like '%BUR%')) ORDER BY CUSTOM_FIELD_VALUE4,OWNER_NAME,CUSTOM_FIELD_VALUE7
you can see i use the Owner Full Name to pull in which locations should be in the report.
The Display Name (OWNER FULL NAME) for the managers is like this: MIA - Eddie Santiago. He is the manager for Miami, but since his name last name has SAN in it then his tickets get pulled into the report for SAN - San Diego. all of our managers names are done that way so we know which person is where and who to assign tickets to. if i could get kace to see the space before and after the 3 digit airport code then it shouldn't include ones that have those letters in people's actual name. - Crispyominus 11 years ago
(O.FULL_NAME like '%SAN%')
becomes
(O.FULL_NAME like ' SAN%') - grayematter 11 years ago