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
Tenth Degree Black 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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share