KACE Product Support

Report on a software title not installed on a computer
1 to 4 of 4

• CommentAuthordtuttle
  
• CommentTimeJul 24th 2009

[/align]I am trying to write a report that will tell me when a certain software isnt installed on computer (EX virus scanning tool). But I cant seem to get the sql right, and the report wizard isnt doing it either.


any ideas? or has someone done this? [/align]

• CommentAuthorbgatech
  
• CommentTimeJul 24th 2009

[/align]I don't have a report but I have a filter. You could setup a filter and then run a report based on the filter.

select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from MACHINE
where ((( (1 not in (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%etrust%')) ) AND (1 not in (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and SOFTWARE.DISPLAY_NAME like '%Sophos%')) )) [/align]

• CommentAuthorlindsm
  
• CommentTimeJul 28th 2009

[/align]The report wizard worked fairly well for mine, here is a copy of the SQL it generated. I made sure the piece of software I cared about was showing up in the software inventory, then using the name it showed there, I just did a computer check for any computer that didn't have a software title containing that name.

SELECT IP, LAST_SYNC, MAC, MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_NAME AS MACHINE_USER_NAME FROM MACHINE WHERE (1 NOT in (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME LIKE '%AVG 8.5%')) AND (1 NOT in (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME LIKE '%Trend Micro Officescan%')) ORDER BY INET_ATON(IP) asc,MACHINE.NAME asc,MACHINE.USER_NAME asc [/align]

• CommentAuthorChrisBlake
  
• CommentTimeJul 29th 2009

[/align]I use this:

select if(sum(if(DISPLAY_NAME like '%Sophos Anti-Virus%', 1, 0)) = 0, "Not Installed", "Installed") as INSTALLED, MACHINE.NAME,MACHINE.IP, MACHINE.LAST_SYNC, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, MACHINE.USER_NAME
from MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
group by MACHINE.NAME
order by INSTALLED, USER_NAME [/align][/align]