KACE Product Support

The report should also exclude all patches.

You can exclude the custom inventory rules, but you can't specify only those programs found in Add/Remove Programs. The KBOX agent discovers applications installed on the machine in a few different ways - one of which is Add/Remove Programs info in the registry. Patches are easily excluded with WHERE ISPATCH != 1.

Are you looking for all software titles, or a specific filtered set?

I'm looking for all software titles.

SELECT M.NAME, M.BIOS_SERIAL_NUMBER, M.OS_NAME, S.DISPLAY_NAME, S.DISPLAY_VERSION FROM MACHINE M
JOIN MACHINE_SOFTWARE_JT MSJT ON (M.ID = MSJT.MACHINE_ID)
JOIN SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID)
WHERE S.IS_PATCH != 1
ORDER BY M.NAME, S.DISPLAY_NAME

and custom inventory rules are excluded by using.

RULE_FLAG=0

or IS_MANUAL=0

If there are any additional ones that you wanted to exclude you could do something more fancy like put them in a label and exclude software from that label

This is just a snippet to add in the correct places to airwolf's query:

LEFT JOIN (select SOFTWARE_ID SID FROM SOFTWARE_LABEL_JT JOIN LABEL L ON L.ID=LABEL_ID WHERE L.NAME IN
('exclude_labelname1', 'excludelabename2', 'etc') /* add exclusion labels here */
) SL ON SL.SID=S.ID
....
WHERE
SID IS NULL

Ah, yes. Thank you, Gerald... I forgot to exclude the custom rules.

I've used the following query:

SELECT M.NAME, M.BIOS_SERIAL_NUMBER, M.OS_NAME, S.DISPLAY_NAME, S.DISPLAY_VERSION FROM MACHINE M
JOIN MACHINE_SOFTWARE_JT MSJT ON (M.ID = MSJT.MACHINE_ID)
JOIN SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID)
WHERE S.IS_PATCH != 1
and RULE_FLAG=0
or IS_MANUAL=0
ORDER BY M.NAME, S.DISPLAY_NAME

It seems to remove all the custom inventory rules that do not have software associated with it but not the ones where I uploaded software. Can these also be removed?

I know I might be pushing it but is it possible to also include the "Last Used ON" from Add remove programs?

Thank you for all the assistance....

You need to use order of operations in your WHERE clause. As is, it is evaluating as WHERE (S.IS_PATCH != 1 and RULE_FLAG=0) or IS_MANUAL=0. You need to wrap the OR in parenthesis.

SELECT M.NAME, M.BIOS_SERIAL_NUMBER, M.OS_NAME, S.DISPLAY_NAME, S.DISPLAY_VERSION FROM MACHINE M
JOIN MACHINE_SOFTWARE_JT MSJT ON (M.ID = MSJT.MACHINE_ID)
JOIN SOFTWARE S ON (S.ID = MSJT.SOFTWARE_ID)
WHERE S.IS_PATCH != 1
and (RULE_FLAG=0
or IS_MANUAL=0)
ORDER BY M.NAME, S.DISPLAY_NAME

As for the Last Used On info, you'll have to create custom inventory rules for that. The KBOX does not collect this information on detected software titles.