ITNinja

What version of KBOX is this and if you edit the SQL (do not save in this mode or you'll lose the wizard) what is the SQL you get?

KBOX Systems Management Appliance v5.1.31237

SQL Select Statement:
SELECT HD_STATUS.NAME AS STATUS, HD_TICKET.MODIFIED AS HD_TICKET_MODIFIED, O.FULL_NAME AS OWNER_NAME, HD_TICKET.ID AS HD_TICKET_ID, S.FULL_NAME AS SUBMITTER_NAME FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ( HD_STATUS.NAME LIKE '%Open%') ORDER BY HD_TICKET.MODIFIED asc,O.FULL_NAME asc

hey cawilson,

I think this is what your looking for.
SELECT HD_STATUS.NAME AS STATUS,
HD_TICKET.MODIFIED AS HD_TICKET_MODIFIED,
O.FULL_NAME AS OWNER_NAME,
HD_TICKET.ID AS HD_TICKET_ID,
S.FULL_NAME AS SUBMITTER_NAME
FROM HD_TICKET
JOIN HD_STATUS
ON ( HD_STATUS.ID = HD_TICKET.HD_STATUS_ID )
LEFT JOIN USER O
ON ( O.ID = HD_TICKET.OWNER_ID )
LEFT JOIN USER S
ON ( S.ID = HD_TICKET.SUBMITTER_ID )
WHERE HD_TICKET.HD_QUEUE_ID = 1
AND HD_STATUS.NAME LIKE '%Open%'
AND HD_TICKET.MODIFIED > DATE_SUB(NOW(),INTERVAL 60 MINUTE)
ORDER BY HD_TICKET.MODIFIED ASC,
O.FULL_NAME ASC

However i would suggest changing status name to status state.

This will show all tickets with an open status, not the word open in the name.
SELECT HD_STATUS.NAME AS STATUS,
HD_TICKET.MODIFIED AS HD_TICKET_MODIFIED,
O.FULL_NAME AS OWNER_NAME,
HD_TICKET.ID AS HD_TICKET_ID,
S.FULL_NAME AS SUBMITTER_NAME
FROM HD_TICKET
JOIN HD_STATUS
ON ( HD_STATUS.ID = HD_TICKET.HD_STATUS_ID )
LEFT JOIN USER O
ON ( O.ID = HD_TICKET.OWNER_ID )
LEFT JOIN USER S
ON ( S.ID = HD_TICKET.SUBMITTER_ID )
WHERE HD_TICKET.HD_QUEUE_ID = 1
AND HD_STATUS.STATE LIKE '%Open%'
AND HD_TICKET.MODIFIED > DATE_SUB(NOW(),INTERVAL 60 MINUTE)
ORDER BY HD_TICKET.MODIFIED ASC,
O.FULL_NAME ASC

In a preliminary test it looks as though the second option you provided works.

I've set it up for testing and will confirm

Thanks for your input!

Ok - so not so fine.

First,
Even though I have the option turned on to only send reports when results are present I receive the reports throughout the night when I know no one is working (we only offer support from 9-5).
When I open the report there are tickets identified as having been modified.
I open the specified ticket and it does not show any modifications as having happened in the history.
Any idea what it thinks is happening? Maybe you can advise how to include a section on what the modification is in the report?

Second,
The submmitter_name does not always fill in the report even though a submitter is chosen for every ticket

Third,
I also wanted to modify the report to include the ticket title

Again, any help with this would be greatly appreciated.