Endpoint Protection

Hello All,

I am trying to understand the database schema for the SEP 11.0 MR2 database, my plan is to create a script that will report on two items:

1.  The actual computer that has virus definitions out of date, as the virus definition distribution report does not break down to a list of computers that have out of date definitions.
2.  Notification of a computer that has a last online time that exceeds a period of X amount of days.

This script will email out helpdesk when computers have been identified that fall into the above criteria.  My problem is that whilst I can see this information in the SEP Management Console (by right clicking on a computer, selecting Edit Properties from the context menu, then clicking on the Client tab), I cannot figure out which table or tables this information is stored in, I have figured out that the Computer table contains a large amount of the information displayed here but where the 'Current Virus Definitions' and 'Last Online Time' fields are stored I cannot figure out.

Anybody out there who can help?  Maybe Symantec could come up with a document which details which table and field name the properties on objects such as the clients are located?

Thanks.

Gary.

1. the DB schema, if not mistaken has changed with MR3.
2. Symantec as previously indicated that they have a consultant that can maybe provide further information.  Unfortunately it was mentionned once and not heard about again.

Might be a good place to start.

Schema Reference Guide for SEP MR2:
ftp://ftp.symantec.com/public/english_us_canada/products/symantec_endpoint_protection/11.0/manuals/mr2/schema_reference_guide.pdf

Hope that helps!

thanks for the PDF file... been reading it...

Thanks for the information, I know the Schema has changed with MR3, it probably changes with all Maintenance Releases. 

I already have the document posted by David-Z but it does not help the current situation where using the schema I am unable to work out the field within the table that correlates to the areas within the Management GUI I am trying to gain the information from within a script.

Any more ideas David-Z, maybe you can look yourself and help?

it is possibel ro have GUP information in profile.xml

Hi spar1grep-2.
Did you ever manage to get this report working? I have exactly the same requirements as you.

btw, if you've upgraded to RU5, this is the doc. Some of the views look prmising. Will need to dig deeper.
ftp://ftp.entsupport.symantec.com/pub/support/documentation/Schema_Reference_Guide_SEP11.0.5.pdf

Pg 135 of this document for the SEM Agent schema looks like what we are looking for.
Please let me know what you managed to do.

Use bcpsem5
SELECT DISTINCT "SEM_AGENT"."DELETED","PATTERN"."VERSION","PATTERN"."PATTERNDATE","SEM_AGENT"."AGENT_VERSION","SEM_CLIENT"."COMPUTER_NAME" "Computer Name",
"SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System",dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00') CREATION_DTTM, dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970 00:00:00') Lastupdatetime,
 DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time",
"PATTERN"."PATTERNDATE" "Pattern Date", "SEM_CLIENT"."USER_NAME" "User Name",
 "V_SEM_COMPUTER"."IP_ADDR1_TEXT" "IP Address", "IDENTITY_MAP"."NAME" "Group Name"
 FROM   (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT" ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID") AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID")) AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER" "SEM_COMPUTER" ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID") AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID")) AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN" ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP" ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER" ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID" and "SEM_AGENT"."DELETED"=0 and "V_SEM_COMPUTER"."IP_ADDR1_TEXT" in ('10.28.4.25','10.31.4.25','10.225.6.11','10.225.5.254','10.61.36.25','10.1.3.31','10.1.3.32','10.11.48.7','10.13.240.25','10.1.89.25','10.7.4.32','10.19.4.40','10.2.225.191','10.25.8.25','10.68.28.25','10.34.28.25','10.226.5.193','10.226.5.194','10.1.7.253','10.1.6.55','10.225.6.214','10.225.6.215','192.168.240.55','10.3.172.10','10.1.10.201','10.1.10.202','132.180.8.128','132.180.8.129','10.225.4.50','10.225.4.158','10.4.40.7','10.152.40.20','10.20.148.52')

Hi Soni.
Thank you for that. Will have to see how I can make this fit for my environment.