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".

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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

Answered 04/19/2013 by: SMal.tmcc
Red Belt

  • 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%')) ))
Please log in to comment
Answer this question or Comment on this question for clarity