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:

SELECT DISTINCT

    S.DISPLAY_NAME,

    M.NAME AS SYSTEM_NAME,

    (SELECT

            MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE

        FROM

            MACHINE_CUSTOM_INVENTORY

        WHERE

            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

FROM

    MACHINE M

        INNER JOIN

    ORG1.KBOT_LOG KL ON (KL.MACHINE_ID = M.ID)

        INNER JOIN

    MACHINE_SOFTWARE_JT MSJT ON (MSJT.MACHINE_ID = M.ID)

        INNER JOIN

    SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID)

INNER JOIN

MACHINE_CUSTOM_INVENTORY ON (MACHINE_CUSTOM_INVENTORY.ID=M.ID)

where

    ((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

INNER JOIN

KBOT_LABEL_JT KLJT ON (KLJT.KBOT_ID=K.ID)

INNER JOIN

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