/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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