Need help updating custom sql report on version10 -> Patch Schedule Not Completed
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 ?
-
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. - sash2003 4 years ago
Answers (1)
Top Answer
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.
Comments:
-
Thank you so much chackstell !!! I really appreciate your help. I tried that and it's worked. - sash2003 4 years ago
-
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. - sash2003 4 years ago-
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 - chucksteel 4 years ago-
Thank you very much ! - sash2003 4 years ago
