ITNinja

You can't use variables in reporting in the KBOX (yet). I hope this is added at some point, but you'll have to statically define your date range in the report. Now, it IS possible to create a report for, let's say, the past 7 days. But you can't prompt for a date range or anything.

Thanks, My next question would be how could I statically add the date range and change when needed to this sql report. Thank you.

If we assume your date range is "tickets for your querys that were created in the last 7 days"

SELECT HD_TICKET.CREATED AS CREATED, HD_TICKET.TIME_OPENED AS OPENED,HD_TICKET.TIME_CLOSED AS CLOSED, HD_TICKET.CUSTOM_FIELD_VALUE1 AS CMS,
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED)as DOWNTIME FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CUSTOM_FIELD_VALUE1 LIKE '%Y%')

and CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)

ORDER BY HD_TICKET.CREATEDasc


if you wanted to know the same results but filters based on a threshold for downtime (which you're measuring in seconds) in your query you would do something like

SELECT HD_TICKET.CREATED AS CREATED, HD_TICKET.TIME_OPENED AS OPENED,HD_TICKET.TIME_CLOSED AS CLOSED, HD_TICKET.CUSTOM_FIELD_VALUE1 AS CMS,
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED)as DOWNTIME FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CUSTOM_FIELD_VALUE1 LIKE '%Y%')

and UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED) > 3600 /*>1 hour*/

ORDER BY HD_TICKET.CREATEDasc

What if I wanted to look up stats for the timeframe of 12/1/2010 thru 12/30/2010 or any other date range?

that line could be:

and CREATED > '2010-12-30' and CREATED < '2011-01-01'

or
and MONTH(CREATED)=12 and YEAR(CREATED) =2010


Thank you. I also have one more question the sql below I have tried to add the date creating line but i get this error, mysql error: [1052: Column 'CREATED' in where clause is ambiguous] in EXECUTE(
"select

Please let me know where I have gone wrong, Thank you.

SQL code as of now,

select
HD_TICKET.TITLE,
HD_PRIORITY.NAME as PRIORITY,
HD_TICKET.CREATED AS CREATED,
HD_TICKET.TIME_CLOSED AS CLOSED,
HD_CATEGORY.NAME as CATEGORY,
HD_IMPACT.NAME as IMPACT,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER,
UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED)-UNIX_TIMESTAMP(HD_TICKET.CREATED) as DOWNTIME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ( HD_PRIORITY.NAME LIKE '%Critical%') and CREATED > '2011-02-1' and CREATED < '2011-02-15' ORDER BY HD_TICKET.TITLE asc

At least two of those tables have a column called CREATED so you have to qualify it whenever you use it E.g. HD_TICKET.CREATED, MACHINE.CREATED etc

I apologize on not understanding this, my sql is weak, If I remove the laste statement, and CREATED > '2011-02-1' and CREATED < '2011-02-15'
I can get this report to work fine. What do you mean by quailify those tables.

I apologize on not understanding this, my sql is weak, If I remove the laste statement, and CREATED > '2011-02-1' and CREATED < '2011-02-15'
I can get this report to work fine. What do you mean by quailify those tables.

if that's the only problem then you only have to change that part:

HD_TICKET.CREATED > '2011-02-1' and HD_TICKET.CREATED < '2011-02-15'


Great, Thank you, works perfect.