Good Day,

Can someone please help me with a custom report that shows the computer name, bios serial number, operating system, software installed, software version. The report should only display software as in Add remove programs NOT all the custom inventory rules I created.

Thank you.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
The report should also exclude all patches.
Answered 11/29/2010 by: anthonyw
Orange Senior Belt

Please log in to comment
0
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?
Answered 11/29/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I'm looking for all software titles.
Answered 11/29/2010 by: anthonyw
Orange Senior Belt

Please log in to comment
0
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
Answered 11/29/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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
Answered 11/29/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Ah, yes. Thank you, Gerald... I forgot to exclude the custom rules.
Answered 11/29/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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....
Answered 11/29/2010 by: anthonyw
Orange Senior Belt

Please log in to comment
0
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.
Answered 11/30/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity