K1000 Inventory: Report/Label for Machines with IE 9 or 10
I followed the blog post http://www.itninja.com/blog/view/kace-custom-inventory-rules-101 to create custom inventory rules to detect IE 9 and 10. I would like to have a report that tells me which machines have IE9 or 10 installed. I created a smart label for the new custom inventory. I'm having a hard time creating a report to give me all machines with the smart label "IE9" or "IE10".
Answers (1)
I based mine on the display name I gave them
select S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER_FULLNAME as LAST_USER
FROM SOFTWARE S
left join MACHINE_SOFTWARE_JT MSJ on S.ID = MSJ.SOFTWARE_ID
left join MACHINE M on MSJ.MACHINE_ID = M.ID
where S.DISPLAY_NAME like 'Microsoft Internet Explorer 10%'
and not IS_PATCH
order by S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME
select S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER_FULLNAME as LAST_USER
FROM SOFTWARE S
left join MACHINE_SOFTWARE_JT MSJ on S.ID = MSJ.SOFTWARE_ID
left join MACHINE M on MSJ.MACHINE_ID = M.ID
where S.DISPLAY_NAME like 'Microsoft Internet Explorer 9%'
and not IS_PATCH
order by S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME
Comments:
-
Thanks... I actually just figured it out by creating a smart label and getting the SQL that way. Since I used two smart labels and a custom inventory rule like in the tutorial the software name is more unique and easily identifiable. Here's what the wizard spit out:
select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1
where (( (1 in (select 1 from ORG1.SOFTWARE, ORG1.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%(CIR) Internet Explorer%')) )) - lmland 11 years ago
