/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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
Second Degree 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
Second Degree 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
Second Degree 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
Second Degree 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
Second Degree 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
Second Degree Blue Belt

Please log in to comment