KACE Product Support

Hi Guys,

I would love to tell you a Big Thank you for your help here. I have my last KACE SQL report I am working on and I need your help. I tried to modify the old SQL script  which was working fine before the KACE upgrade to v.10.1.X but I am facing with issue here. (Looks like the P.RELEASEDATE column should be changed, not sure which column I need to put here, probably some other columns should be updated as well.)

The old script:

select
  M.NAME as 'Device Name'
  ,P.TITLE as 'Patch Name'
  ,date_format(P.RELEASEDATE, "%m/%d/%Y") as 'Released'
  ,datediff(now(), P.RELEASEDATE) as 'Age of Patch (days ago)'
  ,P.IMPACTID as 'Impact'
  ,PC.PATCHED as 'Installed'
  ,PC.UNPATCHED as 'Missing'
  ,PC.ERROR as 'Error'
  ,datediff(now(), M.LAST_INVENTORY) as 'Last Inventory (days ago)'

from
  MACHINE M
  join MACHINE_LABEL_JT MLJT on M.ID = MLJT.MACHINE_ID
  join LABEL DEVICE_LABEL on DEVICE_LABEL.ID = MLJT.LABEL_ID
  join PATCHLINK_MACHINE_STATUS PMS on M.ID = PMS.MACHINE_ID
  join PATCHLINK_PATCH_LABEL_JT PLJT on PLJT.PATCHUID = PMS.PATCHUID
  join LABEL PATCH_LABEL on PLJT.LABEL_ID = PATCH_LABEL.ID
  join KBSYS.PATCHLINK_PATCH P on PMS.PATCHUID = P.UID
  join PATCHLINK_PATCH_COUNT PC on P.UID = PC.PATCHUID
where
   (datediff(now(), P.RELEASEDATE) >= 30
   or datediff(now(), M.LAST_INVENTORY) >= 30)
  and DEVICE_LABEL.NAME = 'Devices: Windows Clients'
  and PATCH_LABEL.NAME = ' Post Production (rooms only) Patches'
  and PMS.STATUS != "PATCHED" order by M.NAME, P.TITLE

Modified:

select
  M.NAME as 'Device Name'
  ,P.TITLE as 'Patch Name'
  ,date_format(P.RELEASEDATE, "%m/%d/%Y") as 'Released'
  ,datediff(now(), P.RELEASEDATE) as 'Age of Patch (days ago)'
  ,P.IMPACTID as 'Impact'
  ,PC.PATCHED as 'Installed'
  ,PC.UNPATCHED as 'Missing'
  ,PC.ERROR as 'Error'
  ,datediff(now(), M.LAST_INVENTORY) as 'Last Inventory (days ago)'

from
  MACHINE M
  join MACHINE_LABEL_JT MLJT on M.ID = MLJT.MACHINE_ID
  join LABEL DEVICE_LABEL on DEVICE_LABEL.ID = MLJT.LABEL_ID
  join PATCH_MACHINE_STATUS PMS on M.ID = PMS.MACHINE_ID
  join PATCH_LABEL_JT PLJT on PLJT.PATCH_ID = PMS.PATCH_ID
  join LABEL PATCH_LABEL on PLJT.LABEL_ID = PATCH_LABEL.ID
  join KBSYS.PATCH P on PMS.PATCH_ID = P.ID
  join PATCH_COUNT PC on P.ID = PC.PATCH_ID
where
   (datediff(now(), P.RELEASEDATE) >= 30
   or datediff(now(), M.LAST_INVENTORY) >= 30)
  and DEVICE_LABEL.NAME = 'Devices: Windows Clients'
  and PATCH_LABEL.NAME = ' Post Production (rooms only) Patches'
  and PMS.STATUS != "PATCHED" order by M.NAME,
  P.TITLE

Error:

mysqli error: [1054: Unknown column 'P.RELEASEDATE' in 'field list'] in EXECUTE(\n"select\n M.NAME as 'Device Name'\n ,P.TITLE as 'Patch Name'\n ,date_format(P.RELEASEDATE, "%m/%d/%Y") as 'Released'\n ,datediff(now(), P.RELEASEDATE) as 'Age of Patch (days ago)'\n ,P.IMPACTID as 'Impact'\n ,PC.PATCHED as 'Installed'\n ,PC.UNPATCHED as 'Missing'\n ,PC.ERROR as 'Error'\n ,datediff(now(), M.LAST_INVENTORY) as 'Last Inventory (days ago)'\n\nfrom\n MACHINE M\n join MACHINE_LABEL_JT MLJT on M.ID = MLJT.MACHINE_ID\n join LABEL DEVICE_LABEL on DEVICE_LABEL.ID = MLJT.LABEL_ID\n join PATCH_MACHINE_STATUS PMS on M.ID = PMS.MACHINE_ID\n join PATCH_LABEL_JT PLJT on PLJT.PATCH_ID = PMS.PATCH_ID\n join LABEL PATCH_LABEL on PLJT.LABEL_ID = PATCH_LABEL.ID\n join KBSYS.PATCH P on PMS.PATCH_ID = P.ID\n join PATCH_COUNT PC on P.ID = PC.PATCH_ID\nwhere\n (datediff(now(), P.RELEASEDATE) >= 30\n or datediff(now(), M.LAST_INVENTORY) >= 30)\n and DEVICE_LABEL.NAME = 'Devices: Windows Clients'\n and PATCH_LABEL.NAME = ' Post Production (rooms only) Patches'\n and PMS.STATUS != "PATCHED" order by M.NAME, \n P.TITLE LIMIT 0")\n