Need help tweaking report that will list computers with multiple versions of SEP installed.
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
Answers (1)
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
Comments:
-
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')) )) - jparkins 10 years ago-
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. - dugullett 10 years ago
