Endpoint Protection

Trying to write an SQL query

What is the Database Table and Column for 

1. Virus and Spyware Protection date

2. Proactive Threat Protection date

3. Network Threat Protection date

Thanks!

There is a table called PATTERN

This keeps the content versions the clients keep

In this table there is a column called PATTERN_TYPE

Symantec™ Endpoint Protection Manager Database Schema Reference 12.1

Thanks ... I'll try out a couple of queries

Two Tables will give you want you want. PATTERN and SEM_CONTENT

SEP_AGENT joined to the PATTERN table will give you the Virus and SpyWare Protection definitions

SELECT
 SA.AGENT_ID
,DEF.VERSION as [Virus and SpyWare Protection]
FROM SEM_AGENT SA
JOIN PATTERN DEF ON DEF.PATTERN_IDX = SA.PATTERN_IDX

SEP_CONTENT AND PATTERN joined with the Pattern_Type of BASH_CONTENT will give you the Proactive Threat Protection Definitions

select
sc.agent_id, PTP.VERSION
from SEM_CONTENT sc
join PATTERN ptp on ptp.PATTERN_IDX = sc.PATTERN_IDX and ptp.PATTERN_TYPE = 'BASH_CONTENT'

SEP_CONTENT AND PATTERN joined with the Pattern_Type of DUEC_SIG will give you the Network Threat Protection Definitions

select
sc.agent_id, NTP.VERSION
from SEM_CONTENT sc
join PATTERN ntp on ntp.PATTERN_IDX = sc.PATTERN_IDX and ntp.PATTERN_TYPE = 'DEUCE_SIG'

Below is SQL query I use to give me the Virus, NTP, PTP definitions of SEP clients.

select

sg.NAME as [SEPM Group]
,CASE WHEN SA.STATUS = '0' THEN 'Offline' ELSE 'Online' END AS [Status]
,c.COMPUTER_NAME as [Computer Name]
,c.OPERATION_SYSTEM as [Operating System]
,c.IP_ADDR1_TEXT as [IP Address]
,sa.AGENT_VERSION as [SEP Version]
,def.VERSION as [Virus and SpyWare Protection]
,p.VERSION as [Proactive Threat Protection]
,n.VERSION as [Network Threat Protection]
,CASE WHEN dateadd(s,convert(bigint,SA.LAST_DOWNLOAD_TIME)/1000,'01-01-1970 0:00:00') = '01-01-1970 0:00:00' THEN ''
ELSE CONVERT(varCHAR(20), dateadd(s,convert(bigint,SA.LAST_DOWNLOAD_TIME)/1000,'01-01-1970 0:00:00'), 100) END as [Last DownLoad Time]

from V_SEM_COMPUTER c
left join SEM_AGENT sa on sa.COMPUTER_ID = c.COMPUTER_ID
JOIN IDENTITY_MAP SG ON sG.ID = sa.GROUP_ID -- sepm group
left join PATTERN def on def.PATTERN_IDX = sa.PATTERN_IDX -- Virus Definition join

-- Proactive Threat Protection subquery
left join (
select sc.agent_id, PTP.VERSION from SEM_CONTENT sc
join PATTERN ptp on ptp.PATTERN_IDX = sc.PATTERN_IDX and ptp.PATTERN_TYPE = 'BASH_CONTENT'
) p on p.AGENT_ID = sa.AGENT_ID

-- Network Threat Protection subquery
left join (
select sc.agent_id, NTP.VERSION from SEM_CONTENT sc
join PATTERN ntp on ntp.PATTERN_IDX = sc.PATTERN_IDX and ntp.PATTERN_TYPE = 'DEUCE_SIG'
) n on n.AGENT_ID = sa.AGENT_ID

order by sg.NAME,c.COMPUTER_NAME