/build/static/layout/Breadcrumb_cap_w.png
09/20/2017 867 views
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:
0 Comments   [ + ] Show comments

Comments


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.

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