Hello all, I have a report that I can run and find out when a single select is selected and find when a program is down. Now this sql code only runs for that given day. How would I add to this sql code to be able to make changes and select a date or dates I would like this to run. Below is the code I have so far for my report. Thank you.

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%') ORDER BY HD_TICKET.CREATED asc
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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.
Answered 02/15/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks, My next question would be how could I statically add the date range and change when needed to this sql report. Thank you.
Answered 02/15/2011 by: Lanman145
Blue Belt

Please log in to comment
0
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
Answered 02/15/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
What if I wanted to look up stats for the timeframe of 12/1/2010 thru 12/30/2010 or any other date range?
Answered 02/15/2011 by: Lanman145
Blue Belt

Please log in to comment
0
that line could be:

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


or
and MONTH(CREATED)=12 and YEAR(CREATED) =2010
Answered 02/15/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 02/16/2011 by: Lanman145
Blue Belt

Please log in to comment
0
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
Answered 02/16/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 02/16/2011 by: Lanman145
Blue Belt

Please log in to comment
0
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.
Answered 02/16/2011 by: Lanman145
Blue Belt

Please log in to comment
0
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'
Answered 02/16/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Great, Thank you, works perfect.
Answered 02/16/2011 by: Lanman145
Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity