How do you Create a Report only on laptops that do not have a Specific Software Installed?
I'm looking for help in creating a report that reports all laptops that do NOT have 'Sophos SafeGuard % Client' installed.
I have a report that will list all machines that do not have it installed, but I cant' figure out how to limit it to just laptops.
SELECT NAME AS 'Machine Name' FROM MACHINE WHERE NAME NOT IN (SELECT M.NAME FROM MACHINE M JOIN MACHINE_SOFTWARE_JT MSJ ON (MSJ.MACHINE_ID = M.ID) JOIN SOFTWARE S ON (S.ID = MSJ.SOFTWARE_ID) WHERE S.DISPLAY_NAME like
'Sophos SafeGuard%Client') ORDER BY NAME
-
we created a smart label for the laptops - SMal.tmcc 11 years ago
-
What would need to be added to the SQL to tell it to use that label? - rbroome 11 years ago
Answers (1)
Try this:
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 ((( CHASSIS_TYPE = 'laptop') AND (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 '%Sophos SafeGuard%Client%')) ))
Comments:
-
This works, is there an easy way only to display the Machine Name in the report? - rbroome 11 years ago
-
Just the machine name? I'm not sure. Perhaps someone else has a thought. - rockhead44 11 years ago
-
yes, the other info is great, but i just want the machine name in the report if possible. - rbroome 11 years ago
-
Yes just change MACHINE.* to MACHINE.NAME. - lmland 11 years ago
-
Ah, very nice. - rockhead44 11 years ago
-
Thanks for all the help! This is what I ended up with.
select MACHINE.Name
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 ((( CHASSIS_TYPE = 'laptop') AND (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 '%Sophos SafeGuard%Client%')) )) Order By Name - rbroome 11 years ago
