When I execute a run now script on a label of machines I need a SQL report to display the results (by name, IP, user logged in, success or failure).  I have a code listed below but it doesn't tell me whether it was a success or failure?  Please add the missing line(s) so I have a pass or fail column or indicator.

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

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

The status is stored in KBOT_LOG so you need to add a join:

LEFT JOIN KBOT_LOG KL on KL.ID = KM.KBOT_LOG_ID

You can then select KL.STATUS which returns a 1, 2, or 3. I believe that a status of 1 is success.

Answered 05/08/2014 by: chucksteel
Red Belt

  • Thanks Chuck! I can figure out how to add the join but not the KL.STATUS line and where?
    • In MySQL the columns that you want to select are in the Select portion of the query in a comma separated list. Right now you are selecting:
      M.NAME, M.IP, M.USER_LOGGED, KM.REQUEST_TIME, KM.KBOT_LOG_ID, KB.NAME AS Script
      To add another column to the list just add it to the end of that line:
      , KL.STATUS
      • Thanks, that helps! Now I have to verify what each status (1,2,3,4 etc.) actually mean as my scripts have multiple tasks components with in that may have failed on task component but was configure to continue on failure and still end up as a 'run successes' in the run now detail. So determining an accurate results report in crucial. I have very little knowledge of SQL and how to pull info from the kace log files so really appreciate your assistance. Thanks again.
Please log in to comment
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