Showing posts with label Symantec Endpoint Protection. Show all posts
Showing posts with label Symantec Endpoint Protection. Show all posts

Thursday, April 4, 2013

Symantec Endpoint Management Server Reporting

I was looking for a query that will enable us to query the Symantec Endpoint Management Server's Database for the SEP client's information for Virus Definitions, Last Scan Time, Last Virus Detected time, Current infection state of the SEP Client but was unable to find any.
Hence I have put together a little SQL query that is to be reference against the SEM5 database which will do the job.


select
L1.Computer_name
, dateadd(second, max(L1.CReation_time)/1000 + 8*60*60, '19700101') ' MAchine Creation Time'
--,max(L1.CReation_time) 'Machine Creation Time'
,dateadd(second, max(L1.LAST_UPDATE_TIME)/1000 + 8*60*60, '19700101') 'Last Machine Status Update Time'
 , Case when  L1.[INFECTED] = 1 then 'Yes'
when L1.[INFECTED] = 0 then 'No'
End as 'System Infected'
 ,dateadd(second, max(L1.[LAST_SCAN_TIME])/1000 + 8*60*60, '19700101') 'Last Scan Time'
      ,dateadd(second, max(L1.[LAST_VIRUS_TIME])/1000 + 8*60*60, '19700101') 'Last Virus Detected'
      ,max(L1.version) 'Current Definition Version'
      ,max(L1.patterndate) 'Current Pattern Date'
from
(

SELECT distinct CID.Computer_NAME
,CID.operation_SYSTEM
--,CID.current_login_user
      ,[CREATION_TIME]
      ,[LAST_UPDATE_TIME]
      ,[INFECTED]
      ,[LAST_SCAN_TIME]
      ,[LAST_VIRUS_TIME]
      ,PAT.version
      ,PAT.patterndate
, avengine_onoff
      
  FROM [sem5_DB].[dbo].[SEM_Agent]
  left join dbo.SEM_COMPUTER CID on SEM_AGENT.computer_ID = CID.Computer_ID
left join dbo.PATTERN PAT on SEM_Agent.pattern_IDX = PAT.pattern_IDX
where CID.deleted = 0
)L1
group by L1.Computer_name,L1.infected,L1.LAST_UPDATE_TIME