/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Need help updating custom sql report on version10 -> Patch Schedule Not Completed

04/23/2020 149 views

Hi Guys,

I need help with my sql report. It does not work anymore after we have upgraded to version 10.1.43

Here is the old sql script:

select
  M.NAME as 'Name'
  ,M.IP as 'IP Address'
  ,PS_MACHINE.STATUS as 'Status'
  ,if(isnull(PS_MS.PATCHED), "Not Available",
    concat("Patched: ", PS_MS.PATCHED, ", Not Patched: ", PS_MS.NOTPATCHED, ", Detect Failures: ", PS_MS.DETECT_FAILURES, ", Deploy Failures: ", PS_MS.DEPLOY_ROLLBACK_FAILURES)) as 'Last Patch Results'
  ,date_format(PS_MS.LAST_RUN, "%m/%d/%Y %H:%i:%s") as 'Date of Last Patch Results'
from
  PATCHLINK_SCHEDULE PS
  join PATCHLINK_SCHEDULE_RUN PS_RUN on PS.ID = PS_RUN.PATCHLINK_SCHEDULE_ID and PS.PATCHLINK_SCHEDULE_RUN_ID = PS_RUN.ID
  join PATCHLINK_SCHEDULE_RUN_MACHINE PS_MACHINE on PS_MACHINE.PATCHLINK_SCHEDULE_ID = PS.ID and PS_MACHINE.PATCHLINK_SCHEDULE_RUN_ID = PS_RUN.ID
  join MACHINE M on PS_MACHINE.MACHINE_ID = M.ID
  left join PATCHLINK_SCHEDULE_MACHINE_STATUS PS_MS on M.ID = PS_MS.MACHINE_ID and PS.ID = PS_MS.PATCHLINK_SCHEDULE_ID

# Set Patch Schedule Name below:
where PS.DESCRIPTION = "Weekly 3-Post Production (rooms only)Patches"
and PS_MACHINE.STATUS != "completed"

order by PS_MS.LAST_RUN



mysqli error: [1146: Table 'ORG3.PATCHLINK_SCHEDULE' doesn't exist] in EXECUTE(\n"select\n M.NAME as 'Name'\n ,M.IP as 'IP Address'\n ,PS_MACHINE.STATUS as 'Status'\n ,if(isnull(PS_MS.PATCHED), "Not Available",\n concat("Patched: ", PS_MS.PATCHED, ", Not Patched: ", PS_MS.NOTPATCHED, ", Detect Failures: ", PS_MS.DETECT_FAILURES, ", Deploy Failures: ", PS_MS.DEPLOY_ROLLBACK_FAILURES)) as 'Last Patch Results'\n ,date_format(PS_MS.LAST_RUN, "%m/%d/%Y %H:%i:%s") as 'Date of Last Patch Results'\nfrom\n PATCHLINK_SCHEDULE PS\n join PATCHLINK_SCHEDULE_RUN PS_RUN on PS.ID = PS_RUN.PATCHLINK_SCHEDULE_ID and PS.PATCHLINK_SCHEDULE_RUN_ID = PS_RUN.ID\n join PATCHLINK_SCHEDULE_RUN_MACHINE PS_MACHINE on PS_MACHINE.PATCHLINK_SCHEDULE_ID = PS.ID and PS_MACHINE.PATCHLINK_SCHEDULE_RUN_ID = PS_RUN.ID\n join MACHINE M on PS_MACHINE.MACHINE_ID = M.ID\n left join PATCHLINK_SCHEDULE_MACHINE_STATUS PS_MS on M.ID = PS_MS.MACHINE_ID and PS.ID = PS_MS.PATCHLINK_SCHEDULE_ID\n\n# Set Patch Schedule Name below:\nwhere PS.DESCRIPTION = "Weekly 3-Post Production (rooms only)Patches"\nand PS_MACHINE.STATUS != "completed"\n\norder by PS_MS.LAST_RUN LIMIT 0")\n



Could you please help ?

Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Hi Team, any ideas on my second question ? From my understanding I have Issue with msql user privileges but I would like to know a second opinion.

Answer Chosen by the Author

1

Here you go:

select
  M.NAME as 'Name'
  ,M.IP as 'IP Address'
  ,PS_MACHINE.STATUS as 'Status'
  ,if(isnull(PS_MS.PATCHED), "Not Available",
    concat("Patched: ", PS_MS.PATCHED, ", Not Patched: ", PS_MS.NOTPATCHED, ", Detect Failures: ", PS_MS.DETECT_FAILURES, ", Deploy Failures: ", PS_MS.DEPLOY_ROLLBACK_FAILURES)) as 'Last Patch Results'
  ,date_format(PS_MS.LAST_RUN, "%m/%d/%Y %H:%i:%s") as 'Date of Last Patch Results'
from
  PATCH_SCHEDULE PS
  join PATCH_SCHEDULE_RUN PS_RUN on PS.ID = PS_RUN.PATCH_SCHEDULE_ID and PS.PATCH_SCHEDULE_RUN_ID = PS_RUN.ID
  join PATCH_SCHEDULE_RUN_MACHINE PS_MACHINE on PS_MACHINE.PATCH_SCHEDULE_ID = PS.ID and PS_MACHINE.PATCH_SCHEDULE_RUN_ID = PS_RUN.ID
  join MACHINE M on PS_MACHINE.MACHINE_ID = M.ID
  left join PATCH_SCHEDULE_MACHINE_STATUS PS_MS on M.ID = PS_MS.MACHINE_ID and PS.ID = PS_MS.PATCH_SCHEDULE_ID
# Set Patch Schedule Name below:
where PS.DESCRIPTION = "Weekly 3-Post Production (rooms only)Patches"
and PS_MACHINE.STATUS != "completed"
order by PS_MS.LAST_RUN

For most reports you just need to replace the word PATCHLINK with PATCH and they should work. That's all I did in this case.

Answered 04/24/2020 by: chucksteel
Red Belt

  • Thank you so much chackstell !!! I really appreciate your help. I tried that and it's worked.
  • Hi chucksteel, could you please help one more time. I have issue with another report as well. I replaced PATCHLINK with PATCH on another MSQL report but this method did not work out now.

    Here is the old MSQL script.

    SELECT CONCAT(M.NAME, "\\", M.USER) AS MACHINE_NAME,
    P.IDENTIFIER AS KB_ARTICLE,
    P.TITLE AS DISPLAY_NAME
    FROM MACHINE_LABEL_JT MJ,
    MACHINE M,
    LABEL L,
    PATCHLINK_MACHINE_STATUS S,
    KBSYS.PATCHLINK_PATCH P
    WHERE M.ID=MJ.MACHINE_ID
    AND L.ID=MJ.LABEL_ID
    AND M.ID = S.MACHINE_ID
    AND S.PATCHUID = P.UID
    AND S.STATUS = 'PATCHED'
    AND S.DEPLOY_STATUS_DT > DATE_SUB(NOW(),INTERVAL 3 DAY)
    AND (( 1 IN (SELECT 1
    FROM PATCHLINK_PATCH_STATUS
    WHERE P.UID = PATCHLINK_PATCH_STATUS.PATCHUID
    AND PATCHLINK_PATCH_STATUS.STATUS IN ( 0 )) ))
    AND L.NAME RLIKE 'Test Group'
    ORDER BY MACHINE_NAME,
    P.TITLE

    Modified SQL script:

    SELECT CONCAT(M.NAME, "\\", M.USER) AS MACHINE_NAME,
    P.IDENTIFIER AS KB_ARTICLE,
    P.TITLE AS DISPLAY_NAME
    FROM MACHINE_LABEL_JT MJ,
    MACHINE M,
    LABEL L,
    PATCH_MACHINE_STATUS S,
    KBSYS.PATCH_PATCH P
    WHERE M.ID=MJ.MACHINE_ID
    AND L.ID=MJ.LABEL_ID
    AND M.ID = S.MACHINE_ID
    AND S.PATCHUID = P.UID
    AND S.STATUS = 'PATCHED'
    AND S.DEPLOY_STATUS_DT > DATE_SUB(NOW(),INTERVAL 3 DAY)
    AND (( 1 IN (SELECT 1
    FROM PATCH_PATCH_STATUS
    WHERE P.UID = PATCH_PATCH_STATUS.PATCHUID
    AND PATCH_PATCH_STATUS.STATUS IN ( 0 )) ))
    AND L.NAME RLIKE 'Test Group'
    ORDER BY MACHINE_NAME,
    P.TITLE

    ERROR:

    mysqli error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'PATCH_PATCH'] in EXECUTE(\n"SELECT CONCAT(M.NAME, "\\\\", M.USER) AS MACHINE_NAME,\nP.IDENTIFIER AS KB_ARTICLE,\nP.TITLE AS DISPLAY_NAME\nFROM MACHINE_LABEL_JT MJ,\nMACHINE M,\nLABEL L,\nPATCH_MACHINE_STATUS S,\nKBSYS.PATCH_PATCH P\nWHERE M.ID=MJ.MACHINE_ID\nAND L.ID=MJ.LABEL_ID\nAND M.ID = S.MACHINE_ID\nAND S.PATCHUID = P.UID\nAND S.STATUS = 'PATCHED'\nAND S.DEPLOY_STATUS_DT > DATE_SUB(NOW(),INTERVAL 3 DAY)\nAND (( 1 IN (SELECT 1\nFROM PATCH_PATCH_STATUS\nWHERE P.UID = PATCH_PATCH_STATUS.PATCHUID\nAND PATCH_PATCH_STATUS.STATUS IN ( 0 )) ))\nAND L.NAME RLIKE 'Test Group'\nORDER BY MACHINE_NAME,\nP.TITLE LIMIT 0")\n

    I would really appreciate your help.
    • Yeah, this one is a little more complicated.

      SELECT CONCAT(M.NAME, "\\", M.USER) AS MACHINE_NAME,
      P.PATCH_IDENTIFIER AS KB_ARTICLE,
      P.TITLE AS DISPLAY_NAME
      FROM MACHINE_LABEL_JT MJ,
      MACHINE M,
      LABEL L,
      PATCH_MACHINE_STATUS S,
      KBSYS.PATCH P
      WHERE M.ID=MJ.MACHINE_ID
      AND L.ID=MJ.LABEL_ID
      AND M.ID = S.MACHINE_ID
      AND S.PATCH_ID = P.ID
      AND S.DETECT_STATUS = 'PATCHED'
      AND S.DEPLOY_STATUS_DT > DATE_SUB(NOW(),INTERVAL 3 DAY)
      AND (( 1 IN (SELECT 1
      FROM PATCH_STATUS
      WHERE P.ID = PATCH_STATUS.PATCH_ID
      AND PATCH_STATUS.STATUS IN ( 0 )) ))
      AND L.NAME RLIKE 'Test Group'
      ORDER BY MACHINE_NAME,
      P.TITLE

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