/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


I need an Aging Report in Kace

04/22/2016 793 views

I need an aging  SQL report in kace, that will show progression over time... for example in last 7 days how many tickets with the category of login::windows were there.. then 14 days, 31 days, 180 days, etc. The SQL I have pulls every closed ticket with the category logins::windows; But I need something similar for all categories. To sum it up I need to see the progression over time of whether we are receiving more or less issues in a particular category. Any help would be much appreciated.


select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE,
       CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_CLOSED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_CLOSED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_CLOSED) - 1), 'd ',
              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
              SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_CLOSED))),
              '%kh %im')) AS TIME_CLOSED,
       HD_PRIORITY.NAME as PRIORITY,
       HD_CATEGORY.NAME as CATEGORY,
       HD_TICKET.CUSTOM_FIELD_VALUE4 as AFFECTED_USER,
       HD_STATUS.NAME as STATUS,
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
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_STATUS.STATE = 'closed' AND HD_CATEGORY.NAME = 'logins::windows' 

order by HD_CATEGORY.ORDINAL, SUBMITTER_NAME, HD_PRIORITY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL, OWNER_NAME

1 Comment   [ + ] Show comment

Comments

  • If you want those specific date ranges then it will be difficult. I would recommend grouping by month to get information on trends.

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ