/build/static/layout/Breadcrumb_cap_w.png

Need help updating custom sql report on version10

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




0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 4 years ago
Red Belt
0

Be sure to check this KB article (which is unfortunately not complete, but it's a start).

https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0

I also highly recommend downloading a tool like MySQL WorkBench to view the database, it will make updating these queries much easier.

That being said:

RELEASEDATE -> CREATION_DATE

IMPACTID -> SEVERITY




Comments:
  • Thank you very much for your help again ! Unfortunately, I am not able to open this page it says "Permission required". Any ideas ? - sash2003 4 years ago

Don't be a Stranger!

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

Sign up! or login

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