Notify parent ticket owner on child ticket close

select HD_TICKET.TITLE,
HD_TICKET.ID,
UO.EMAIL as OWNER_EMAIL,
UOP.EMAIL as PARENT_OWNER_EMAIL,
UO.FULL_NAME as OWNER,
HTC.COMMENT as COMMENT

from HD_TICKET
join HD_TICKET TP on TP.ID = HD_TICKET.PARENT_ID
left join USER UO on UO.ID = HD_TICKET.OWNER_ID
left join USER US on US.ID = HD_TICKET.SUBMITTER_ID
left join USER UOP on UOP.ID = TP.OWNER_ID
left join USER USP on USP.ID = TP.SUBMITTER_ID
join HD_TICKET_CHANGE HTC on HTC.HD_TICKET_ID = HD_TICKET.ID
join HD_TICKET_CHANGE_FIELD HTCF on HTCF.HD_TICKET_CHANGE_ID = HTC.ID

where HTCF.HD_TICKET_CHANGE_ID = <CHANGE_ID>
and HD_TICKET.PARENT_ID != 0
and HTCF.FIELD_CHANGED = 'STATUS_NAME'
and HTCF.AFTER_VALUE = 'Closed'
-- ------------
/*
email column: $parent_owner_email

$owner has closed a child ticket for one of your tickets:

Description: $title
Notes: $comment

View details here: http://kace1000/userui/ticket.php?ID=$id
*/

-----------------------------

Identify duplicate serial numbers

SELECT

M.NAME,

M.BIOS_SERIAL_NUMBER,

M.USER_LOGGED,

M.IP

FROM

MACHINE M

WHERE M.BIOS_SERIAL_NUMBER

IN

 (  SELECT M.BIOS_SERIAL_NUMBER FROM MACHINE M  GROUP BY BIOS_SERIAL_NUMBER   HAVING (COUNT(BIOS_SERIAL_NUMBER ) > 1) ) 

-----------------------------

Add asset location to built-in report for machines failing patches

SELECT   PP.TITLE AS DISPLAY_NAME, 

    M.NAME AS ComputerName,

    AL.NAME as LOC,

    SYSTEM_DESCRIPTION,  IP,  MAC,

    M.USER_LOGGED as USER_LOGGED,

FROM  PATCHLINK_MACHINE_STATUS MS

        JOIN  KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID

        JOIN  MACHINE M ON M.ID = MS.MACHINE_ID

        left join  ASSET AM ON AM.MAPPED_ID = M.ID

        left join  ASSET_ASSOCIATION AAM ON AAM.ASSET_ID = AM.ID

        left join  ASSET AL ON AL.ID = AAM.ASSOCIATED_ASSET_ID

WHERE

    (MS.DEPLOY_ATTEMPT_COUNT >= MS.MAX_DEPLOY_ATTEMPT

        and MS.STATUS != 'PATCHED')

        or MS.STATUS = 'FAIL'

        or (MS.DEPLOY_STATUS = 'FAIL'

        and MS.STATUS != 'PATCHED')

GROUP BY PP.TITLE

-----------------------------

Track RAM changes over past week

SELECT

M.NAME,

M.IP,

AL.NAME as LOC,

AH.FIELD_NAME,

AH.TIME

FROM ASSET AM

join ASSET_HISTORY AH on AH.ASSET_ID = AM.ID

join MACHINE M on M.ID = AM.MAPPED_ID

join ASSET_ASSOCIATION AAM on AAM.ASSET_ID = AM.ID

join ASSET AL

on AL.ID  = AAM.ASSOCIATED_ASSET_ID and AL.ASSET_TYPE_ID = 1

where AM.ASSET_TYPE_ID = 5

And AH.FIELD_NAME = 'RAM_TOTAL'

 and AH.TIME > DATE_SUB(now(),INTERVAL 8 DAY)