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

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • we created a smart label for the laptops
  • What would need to be added to the SQL to tell it to use that label?
Please log in to comment

Community Chosen Answer

2

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%')) ))

Answered 10/23/2013 by: rockhead44
Red Belt

  • This works, is there an easy way only to display the Machine Name in the report?
    • Just the machine name? I'm not sure. Perhaps someone else has a thought.
      • yes, the other info is great, but i just want the machine name in the report if possible.
  • Yes just change MACHINE.* to MACHINE.NAME.
    • Ah, very nice.
      • 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
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity