I used the SQL Info from a Smart Label to create a report that would show all my pc's that don't have anti-virus installed. Here's the code I used:


select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 not 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 '%Trend Micro OfficeScan Client%')) ))


On the Smart Label, there are only 5 computers listed as NOT having anti-virus.

Attachment

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
THe output is unreadable because you are selecting every column in these tables.

How about

select MACHINE.NAME, MACHINE.IP, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
from ORG1.MACHINE
LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
where (( (1 not 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 '%Trend Micro OfficeScan Client%')) ))
Answered 03/10/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
That got it. Thanks.
Answered 03/11/2011 by: jmcelvoy
Second Degree Blue Belt

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