/build/static/layout/Breadcrumb_cap_w.png
03/27/2019 117 views

Hello, i have quite a few patch schedules for mac, windows dekstop, laptops, servers etc.

However some of them have smart labels and some just list the individual machines.

I would like to have a report listing the what devices are listed in each patch schedule, there is one already setup in kbox called patch schedules and its selected devices however this only lists the patch schedule where the devices are smart labels and not the individual ones.

Therefore does anyone know how to create another report that lists the same but includes the patch schedules where individual devices are added?


thanks


 

0 Comments   [ + ] Show comments

Comments


All Answers

0

This is the best I could get.

This will give you a report for all devices within all patch schedules that have Status of Completed, you can take away the completed if you like but it will show you duplicate machines.


SELECT PATCHLINK_SCHEDULE_ID, NAME FROM PATCHLINK_SCHEDULE_RUN_MACHINE JOIN MACHINE ON PATCHLINK_SCHEDULE_RUN_MACHINE.MACHINE_ID = MACHINE.ID WHERE PATCHLINK_SCHEDULE_RUN_MACHINE.STATUS = "completed"


You can also add after completed

and PATCHLINK_SCHEDULE_ID = 3

Just replace 3 with the Individual ID's of each schedule

Hope this kind of helps.

Answered 03/28/2019 by: Ziggi
Orange Senior Belt

  • Here's a version that adds some useful columns:
    SELECT MACHINE.NAME, PATCHLINK_SCHEDULE_RUN_MACHINE.STATUS,
    PATCHLINK_SCHEDULE_RUN.LAST_UPDATED, PATCHLINK_SCHEDULE_RUN.RUN_TIME
    FROM PATCHLINK_SCHEDULE_RUN_MACHINE
    JOIN MACHINE ON PATCHLINK_SCHEDULE_RUN_MACHINE.MACHINE_ID = MACHINE.ID
    JOIN PATCHLINK_SCHEDULE on PATCHLINK_SCHEDULE.ID = PATCHLINK_SCHEDULE_RUN_MACHINE.PATCHLINK_SCHEDULE_ID
    JOIN PATCHLINK_SCHEDULE_RUN on PATCHLINK_SCHEDULE_RUN.ID = (SELECT MAX(PATCHLINK_SCHEDULE_RUN_ID)
    FROM PATCHLINK_SCHEDULE_RUN_MACHINE
    WHERE PATCHLINK_SCHEDULE_ID = PATCHLINK_SCHEDULE.ID)
    WHERE PATCHLINK_SCHEDULE.DESCRIPTION = "Patch Production Deploy"
    and PATCHLINK_SCHEDULE_RUN_MACHINE.PATCHLINK_SCHEDULE_RUN_ID =
    (SELECT MAX(PATCHLINK_SCHEDULE_RUN_ID)
    FROM PATCHLINK_SCHEDULE_RUN_MACHINE
    WHERE PATCHLINK_SCHEDULE_ID = PATCHLINK_SCHEDULE.ID)
    ORDER BY MACHINE.NAME

    Change the PATCHLINK_SCHEDULE.DESCRIPTION = "Patch Production Deploy" to match the name of the schedule you want to target.
    • Mnay Thanks all good