Need SQL code for 'Run now status' results/report from a set of machines
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
Answers (1)
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.
Comments:
-
Thanks Chuck! I can figure out how to add the join but not the KL.STATUS line and where? - bnerison 9 years ago
-
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 - chucksteel 9 years ago-
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. - bnerison 9 years ago
