I am looking for a SQL report to monitor the results of a scheduled script run...not a 'run now or 'run now status' report. 

Most of the scripts I run are scheduled for after hours and I need to check status to see which ones failed and which ones were succcessful by each script and machine name.  Having to look at all the agent logs for each one is too time consuming and a real pain.

10 Comments   [ + ] Show Comments

Comments

  • Below is what I use for a Scripting 'Run Now Status' report if helps at all.

    Select Distinct
    M.NAME, M.IP, M.USER_LOGGED, KM.REQUEST_TIME, KM.KBOT_LOG_ID, KB.NAME AS Script
    From
    MACHINE M
    Left Join KBOT_RUN_MACHINE KM On M.ID = KM.MACHINE_ID
    Left Join KBOT_RUN K On K.ID = KM.KBOT_RUN_ID
    Left Join KBOT KB On KB.ID = K.KBOT_ID
    Where
    KM.KBOT_LOG_ID IS NOT NULL
    AND KM.REQUEST_TIME > DATE_SUB(NOW(), INTERVAL 30 DAY)
    Order By KM.KBOT_LOG_ID, M.NAME
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • This is where I'd start:
    SELECT * FROM ORG1.KBOT_LOG
    WHERE KBOT_ID = 581
    and DATE(START_TIME) = DATE(NOW())

    This will get you the log entries for script ID 581 that ran today. The status is recorded as a number so you'd need to check and see what those equate to for your scripts.
    • I need to change the date to reflect the last x-days
  • Still just looking for a report listing Kscript(s) I created and ran on a schedule. Example is a script I created to remove Java 6u31 from group of machines. I have to look at each machine details to see if ran and was successful or not. Would be nice to have a column list of the script(s), machine name, and if pass or fail...thanks!
  • Here are the column items needed in the CSV report: K-script name, system name, login user, IP, some type of scripting log status (i.e. pass or fail) and a timeline (i.e. ran within last x-days or other). The report wizard is worthless for this as I have spent days trying to get the results...:(
  • good day

    did you get any luck with this issue bnerison

    i take it that you looking for a report to display the success and failures when running it via a script?

    i might have a solution for you
  • This is what worked for me

    Upload your software as a manage install and upload it as a dependency
    after is has been uploaded create a manage install but the (MANAGED ACTION should be DISABLED and add all the labels you are targetting or machines and then save.

    Run your script targetting the labels or machines that you needed just like you have added it to the manage install

    Under the reporting tab copy this script below and then editing the report just add the name of the software WHERE DISPLAY_NAME = 'Name of software'

    and WALLA!!! the report should present the name, software installed and not installed via csv




    SELECT DISTINCT DATE_FORMAT(MI.CREATED,'%Y/%m/%d') as CREATED_DATE,
    S.DISPLAY_NAME,
    S.DISPLAY_VERSION,

    CASE
    WHEN MS.MACHINE_ID > 0 THEN 'Installed'
    WHEN MIA.ATTEMPT_COUNT > 0 THEN CONCAT('Not Installed (',MIA.ATTEMPT_COUNT,' of ', MI.MAX_ATTEMPT_COUNT,' attempts)')
    ELSE 'Not Installed'
    END AS STATUS,
    M.NAME,
    M.IP,
    M.LAST_SYNC,
    M.USER_NAME
    FROM SOFTWARE S
    JOIN MI
    ON ( S.ID = MI.SOFTWARE_ID )
    JOIN MI_LABEL_JT MIL
    ON ( MI.ID = MIL.MI_ID )
    JOIN LABEL L
    ON ( MIL.LABEL_ID = L.ID )
    JOIN MACHINE_LABEL_JT ML
    ON ( L.ID = ML.LABEL_ID )
    JOIN MACHINE M
    ON ( ML.MACHINE_ID = M.ID )
    JOIN SOFTWARE_OS_JT SO
    ON ( SO.SOFTWARE_ID = S.ID
    AND SO.OS_ID = M.OS_ID )
    LEFT JOIN MACHINE_SOFTWARE_JT MS
    ON ( M.ID = MS.MACHINE_ID
    AND MS.SOFTWARE_ID = S.ID )
    LEFT JOIN MI_ATTEMPT MIA
    ON ( MIA.MI_ID = MI.ID
    AND MIA.MACHINE_ID = M.ID )
    WHERE DISPLAY_NAME = 'Name of software'
    ORDER BY S.DISPLAY_NAME,
    S.DISPLAY_VERSION,
    STATUS

    let me know if this helps....... :-)
    • Awesome...I will give it a try thanks.

      What would be your suggestion if the k-script is not designed to install anything but do something else instead (WOL machines, copy a file over, run a command line to do whatever, run a non-install task, etc.)?
    • I finally got this work as you indicated above and the results are what is expected. My question is what would be your suggestion if the k-script is not designed to install anything but do something else instead (WOL machines, copy a file over, run a command line to do whatever, run a non-install task, etc.)?
  • I tried you suggestion but didn't work. Not sure how the managed install (MI) is tied into the k-script? Since I have to configure all the items related to and to complete the install from the script was in the need for a MI item? I'm still playing with this...
  • hi bnerison

    apologies for the late reply

    What is it that is haunting you with the K1000?
    with regards to deploying,scripting and reporting etc........
  • Hi burtono, thanks for your help on this!
    At this point our biggest issue is obtaining results when running a non-install script (by either using the run now or a scheduled one for later date). Here are some examples (WOL machines, copy a file over, remove old versions of Java, reboot PC's, run a command line to do whatever, run a non-install task, etc.). There are quite a few build-in non-install scripts on the Kace box and we create others but no way to report the run results if scheduled after hours. I would like to come in the next day and pull up the results of a script that I ran. There are some suggestion with in my post here however may not be working as noted.
  • I am still looking for answers to this question...
Please log in to comment

Community Chosen Answer

1

Here is Chuck's SQL query updated slightly to show user run scripts for the last 24 hours

SELECT KBOT_LOG.ID, KBOT_LOG.START_TIME, MACHINE.NAME, KBOT.NAME, KBOT.ID, KBOT_LOG_DETAIL.TEXT, KBOT_CRON_SCHEDULE.* FROM ORG1.KBOT_LOG JOIN MACHINE on KBOT_LOG.MACHINE_ID = MACHINE.ID JOIN KBOT on KBOT_LOG.KBOT_ID = KBOT.ID JOIN KBOT_CRON_SCHEDULE on KBOT_LOG.KBOT_ID = KBOT_CRON_SCHEDULE.KBOT_ID JOIN KBOT_LOG_DETAIL on KBOT_LOG_DETAIL.ID = KBOT_LOG.OUTPUT_DETAIL_ID 

WHERE KBOT.ID > 100 and Enabled=1 and type !="meter" and type !="system"  

and KBOT_LOG.START_TIME >  DATE_SUB(NOW(), INTERVAL 1 DAY)

Answered 03/04/2014 by: Jbr32
Tenth Degree Black Belt

  • Thanks but I don't get any results back with this one either...:(
    • I would try to remove things from the where clause one piece at a time. Note that if you eliminate everything in the where clause it could case your box to hang as you are trying to produce a result set from a table that can have tens of thousands of entries.
      • Thanks...I did what you suggested and got all the way down to WHERE KBOT.ID > 100 but still no results returned.
      • Change the > 100 to > 8 and see if that works. Have you tried to open up the DB via MySQL WorkBench to browse the KBOT table?
Please log in to comment

Answers

0

Here's a report that includes some details on scripts run today:

SELECT KBOT_LOG.ID, KBOT_LOG.START_TIME, MACHINE.NAME, KBOT.NAME, KBOT.ID, KBOT_LOG_DETAIL.TEXT, KBOT_CRON_SCHEDULE.*FROM ORG1.KBOT_LOGJOIN MACHINE on KBOT_LOG.MACHINE_ID = MACHINE.IDJOIN KBOT on KBOT_LOG.KBOT_ID = KBOT.IDJOIN KBOT_CRON_SCHEDULE on KBOT_LOG.KBOT_ID = KBOT_CRON_SCHEDULE.KBOT_IDJOIN KBOT_LOG_DETAIL on KBOT_LOG_DETAIL.ID = KBOT_LOG.OUTPUT_DETAIL_IDWHERE KBOT.ID = 581

This pulls from several tables to show when the script ran and the schedule for the script, as well as the log detail.

 

Answered 02/21/2014 by: chucksteel
Red Belt

  • This one did not provide any results, here is the SQL from your response (ID=6 worked with your first one) please verify:
    SELECT KBOT_LOG.ID, KBOT_LOG.START_TIME, MACHINE.NAME, KBOT.NAME, KBOT.ID, KBOT_LOG_DETAIL.TEXT, KBOT_CRON_SCHEDULE.
    *FROM ORG1.KBOT_LOG
    JOIN MACHINE on KBOT_LOG.MACHINE_ID = MACHINE.ID
    JOIN KBOT on KBOT_LOG.KBOT_ID = KBOT.ID
    JOIN KBOT_CRON_SCHEDULE on KBOT_LOG.KBOT_ID = KBOT_CRON_SCHEDULE.KBOT_ID
    JOIN KBOT_LOG_DETAIL on KBOT_LOG_DETAIL.ID = KBOT_LOG.OUTPUT_DETAIL_ID
    WHERE KBOT.ID = 6
    • I can't tell from the post, but it looks like there might not be a space between KBOT_CRON_SCHEDULE.* and FROM. The post makes it look like that space might have been lost which would certainly cause problem. Actually it looks like all my carriage returns got removed when I pasted into ITNinja but you corrected the other ones.
      • Here is what I tried but no luck - KBOT_CRON_SCHEDULE.* FROM ORG1.KBOT_LOG
      • When you get a chance please verify the SQL in this reports and resend to me so I can test...thanks
      • Here is what works for me:
        SELECT KBOT_LOG.ID, KBOT_LOG.START_TIME, MACHINE.NAME, KBOT.NAME, KBOT.ID, KBOT_LOG_DETAIL.TEXT, KBOT_CRON_SCHEDULE.*
        FROM ORG1.KBOT_LOG
        JOIN MACHINE on KBOT_LOG.MACHINE_ID = MACHINE.ID
        JOIN KBOT on KBOT_LOG.KBOT_ID = KBOT.ID
        JOIN KBOT_CRON_SCHEDULE on KBOT_LOG.KBOT_ID = KBOT_CRON_SCHEDULE.KBOT_ID
        JOIN KBOT_LOG_DETAIL on KBOT_LOG_DETAIL.ID = KBOT_LOG.OUTPUT_DETAIL_ID
        WHERE KBOT.ID = 581

        Are you using the query in a tool like MySQL Workbench or just pasting it into a new report and seeing what comes out?
      • Still nothing comes out no matter what ID I use. I am just pasting it into just pasting it into a new report and seeing what comes out? Is there a way to add the ID field to my 'Run Now Status' report? I tried but failed.
      • I'd recommend using a tool like MySQL Workbench and running the query there. It makes it much easier to figure out what might be going wrong.
Please log in to comment
Answer this question or Comment on this question for clarity