Hello,

I have been trying to get a report/list of all my scripts that have a schedule and what that schedule is. I have over 100 scripts and really don't want to manually record what their schedules are. Is there an easier way of doing that either through a KACE report or a SQL Query?

Attached is a picture of what part of the script I'm trying to report on.

0OMYrW.png

Thanks,
JK
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Script schedules can be stored either in the IM_CRON table or the KBOT_CRON_SCHEDULE. This query should return scripts that have a schedule in one of those tables.

SELECT KBOT.NAME, 
CASE 
    when KBOT.IM_CRON_ID = 0 then concat(KBOT_CRON_SCHEDULE.MINUTES," ", KBOT_CRON_SCHEDULE.HOURS," ", KBOT_CRON_SCHEDULE.DAYS_OF_MONTH," ", KBOT_CRON_SCHEDULE.MONTHS," ", KBOT_CRON_SCHEDULE.DAYS_OF_WEEK)
    else concat(IM_CRON.MINUTES," ", IM_CRON.HOURS," ", IM_CRON.DAYS_OF_MONTH," ", IM_CRON.MONTHS," ", IM_CRON.DAYS_OF_WEEK," ")
end as CRON,
CASE 
    when KBOT.IM_CRON_ID = 0 then "KBOT_CRON_SCHEDULE"
    else "IM_CRON"
end as CRONTYPE 
FROM ORG1.KBOT
LEFT JOIN KBOT_CRON_SCHEDULE on KBOT_CRON_SCHEDULE.KBOT_ID = KBOT.ID
LEFT JOIN IM_CRON on IM_CRON.ID = KBOT.IM_CRON_ID
HAVING CRON like "%"
ORDER BY KBOT.NAME  


Answered 09/21/2017 by: chucksteel
Red Belt

  • This worked great!

    I am actually in the process of learning MySQL and SQL.

    I was very close to getting this query myself but I was stumped only getting like 12 records.

    I had done LEFT JOIN IM_CRON on IM_CRON.ID = KBOT.ID

    Which I realize now isn't correct.

    Thanks,
    JK
    • If you haven't already setup a program like MySQL Workbench to connect to the database, then I highly recommend doing so. The tables are fairly well laid out and generally easy to follow the connections between them.
      • Yeah, that is what I did. I think it is interesting to learn.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share