Hello,

 

I am trying to create a report that will list machines with both SEP 11 and SEP 12 installed. We have multiple versions and I thought I had my SQL correct but it is returning 0 results and I know for a fact that there are machines in our environment that have both versions listed as being installed.

 

Here is what I have so far. Any help would be greatly appreciated.

 

SELECT

 

S.DISPLAY_NAME AS SOFTWARE_NAME,

 

S.DISPLAY_VERSION AS VERSION,

 

M.`NAME` AS MACHINE_NAME,

 

M.USER_FULLNAME AS LAST_USER,

 

M.IP

 

FROM

 

SOFTWARE AS S

 

JOIN MACHINE_SOFTWARE_JT AS MSJ ON S.ID = MSJ.SOFTWARE_ID

 

LEFT JOIN MACHINE AS M ON MSJ.MACHINE_ID = M.ID

 

where S.DISPLAY_NAME like 'symantec endpoint protection%'

 

and S.DISPLAY_VERSION like '12.1.2015.2015%'

 

or S.DISPLAY_VERSION like '12.1.2100.2093%'

 

and S.DISPLAY_VERSION like '11.0.5002.333%'

 

or S.DISPLAY_VERSION like '11.0.4202.75%'

 

or S.DISPLAY_VERSION like '11.0.6100.645%'

 

and not IS_PATCH

 

order by S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

3

It works for me as is. I also tried changing to enclose the ORs in (). Which also worked.

SELECT S.DISPLAY_NAME AS SOFTWARE_NAME,

S.DISPLAY_VERSION AS VERSION,

M.NAME AS MACHINE_NAME,

M.USER_FULLNAME AS LAST_USER,M.IP

FROM SOFTWARE S

JOIN MACHINE_SOFTWARE_JT AS MSJ ON S.ID = MSJ.SOFTWARE_ID

LEFT JOIN MACHINE AS M ON MSJ.MACHINE_ID = M.ID

where S.DISPLAY_NAME like 'symantec endpoint protection%'

and (S.DISPLAY_VERSION like '12.1.2015.2015%'

or S.DISPLAY_VERSION like '12.1.2100.2093%'

or S.DISPLAY_VERSION like '11.0.5002.333%'

or S.DISPLAY_VERSION like '11.0.4202.75%'

or S.DISPLAY_VERSION like '11.0.6100.645%')

and not IS_PATCH

order by S.DISPLAY_NAME, S.DISPLAY_VERSION, M.NAME

Answered 07/29/2013 by: dugullett
Red Belt

  • I guess I stated it incorrectly. The query does return results. However it is not filtering the results. What I am needing is to only return results where a machine has more than one version of SEP installed.

    Here is another query that I have that does what I am looking for with the exception that I need to add the other versions into this, and each time I try it also stops filtering correctly. Is there a way to take the other versions and incorporate them into this SQL?

    so essentially it would work like this...

    output any pc that has SEP 12.1.2015.2015 or 12.1.2100.2093
    and SEP 11.0.5002.333 or 11.0.4202.75 or 11.0.6100.645

    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 ((( (1 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_VERSION = '12.1.2015.2015')) )

    AND (1 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_VERSION = '11.0.5002.333')) ))
    • That's strange. I tested this with Sophos since I don't use Symantec. I created a custom inventory for 11. I used this query. It returned 0 results even though I had one machine with both 11 and 10.0.10.

      SELECT S.DISPLAY_NAME AS SOFTWARE_NAME,
      GROUP_CONCAT(DISTINCT S.DISPLAY_VERSION SEPARATOR '\n') AS VERSION,
      M.NAME AS MACHINE_NAME,M.USER_FULLNAME AS LAST_USER,M.IP
      FROM SOFTWARE S
      JOIN MACHINE_SOFTWARE_JT AS MSJ ON S.ID = MSJ.SOFTWARE_ID
      LEFT JOIN MACHINE AS M ON MSJ.MACHINE_ID = M.ID
      where S.DISPLAY_NAME like 'SOPHOS ANTI-VIRUS%'
      AND S.DISPLAY_VERSION LIKE '11%'
      AND S.DISPLAY_VERSION LIKE '10.0.10%'
      group by M.NAME
      order by S.DISPLAY_VERSION

      I changed it to an "OR" and it returned results. It's almost like it doesn't recognize that AND.

      SELECT S.DISPLAY_NAME AS SOFTWARE_NAME,
      GROUP_CONCAT(DISTINCT S.DISPLAY_VERSION SEPARATOR '\n') AS VERSION,
      M.NAME AS MACHINE_NAME,M.USER_FULLNAME AS LAST_USER,M.IP
      FROM SOFTWARE S
      JOIN MACHINE_SOFTWARE_JT AS MSJ ON S.ID = MSJ.SOFTWARE_ID
      LEFT JOIN MACHINE AS M ON MSJ.MACHINE_ID = M.ID
      where S.DISPLAY_NAME like 'SOPHOS ANTI-VIRUS%'
      AND S.DISPLAY_VERSION LIKE '11%'
      OR S.DISPLAY_VERSION LIKE '10.0.10%'
      group by M.NAME
      order by S.DISPLAY_VERSION

      I'll keep working on it.
Please log in to comment
Answer this question or Comment on this question for clarity