I needed a couple of reports and couldn’t find exactly what I needed, so I found a way to do them and thought I would share.
1) A report needed on machines where a script had run to install/update some software (or anything for that matter), and the script ran but had other versions still installed. For instance:
M.NAME AS SYSTEM_NAME,
MACHINE_CUSTOM_INVENTORY.ID = M.ID
AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 2246) AS OU_LOCATION, CONCAT ('https://your.k1000.com/adminui/machine.php?ID=',M.ID) AS MACHINE_LINK
ORG1.KBOT_LOG KL ON (KL.MACHINE_ID = M.ID)
MACHINE_SOFTWARE_JT MSJT ON (MSJT.MACHINE_ID = M.ID)
SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID)
MACHINE_CUSTOM_INVENTORY ON (MACHINE_CUSTOM_INVENTORY.ID=M.ID)
((KL.KBOT_ID RLIKE '144|143')
AND (S.DISPLAY_NAME RLIKE 'JAVA|7 update 13')
AND (S.DISPLAY_NAME NOT RLIKE ('7 UPDATE 13|AUTO UPDATER|FX')))
ORDER BY DISPLAY_NAME
This sql report for instance will display all machines where the 7 update 13 install script(s) have run (my kbot_id 144, 143), and report back which machines have 7 update 13 installed, but other java versions as well, excluding the 7 update 13, auto updater, and FX from the results. The select statement for column "OU_LOCATION" is for a LDAP label and displays the computer's OU, which is useful for applying labels to scripts by showing you which ones need it. The concat statement is there to allow me to highlight the link to the machine so that I can right click and open in a new tab directly from the results, without having to copy and paste in the computer inventory (works in Firefox, haven't tested in other browsers). When you create these sql reports, it doesn’t make the computer name clickable like it does in the wizard scripts. I haven’t been able to find a solution for that yet, hence the concat. Please comment if you know of a way to make it link.
2) The second report I needed was to show which labels were applied to scripts, so that I wouldn’t have to go in and click on each one just to find out. This script will tell me those results, including only the ones where the script is enabled. This can be changed to just show all by removing the AND (K.ENABLED=1) from the sql:
SELECT DISTINCT K.NAME AS SCRIPT_NAME, L.NAME AS COMPUTER_LABEL
FROM KBOT K
KBOT_LABEL_JT KLJT ON (KLJT.KBOT_ID=K.ID)
LABEL L ON (L.ID=KLJT.LABEL_ID)
WHERE ((K.NAME RLIKE 'JAVA') AND (L.NAME NOT RLIKE'TESTING') AND (K.ENABLED=1))
ORDER BY K.NAME, L.NAME
Here this will search for scripts with java in the title and the label does not contain 'testing', to filter out our test label.
Both of these can be modified to search for your names/scripts by modifying the search terms