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]

[/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]
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
For mine, I created a label filter (Reporting, Filters). In the filter I just checked to make sure that the name of software I had inventoried previously was not on the machine. Was fairly easy. Machine Filter, Software Titles, Does not Contain, "Sophos" or whatever you have previously inventoried.
Answered 09/10/2009 by: lindsamw
Orange Senior Belt

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