Endpoint Protection

Hi,

Can someone help me with a SQL query to extract from SQL DB for SEPM?

I'm able to find the tables for the requirement but not for all & I couldn't colide them as well since I'm not good in SQL queries.

Table Name: SEM5.SEM_COMPUTER:

Columns: COMPUTER_NAME, COMPUTER_DOMAIN_NAME, OPERATING_SYSTEM, CURRENT_LOGON_USER, TIME_STAMP, DISK_TOTAL

Which Table & column will have the SEP version & the SEP definition?

Also, can someone help me in creating all in a script please?

Hi Shiva,

This document contains the information you're interested in:

http://www.symantec.com/docs/DOC4324

You'll be wanting to take a look in the SEM_AGENT table to get started.

Hope that helps!

Adding DB schema for 12.1.5:

Symantec Endpoint Protection 12.1.5 Database Schema

Check this article for SQL queries

http://www.symantec.com/connect/articles/compilation-sql-queries-sepm-database

Hello,

In case you want to create the SQL Query only, then check the below:

Symantec™ Endpoint Protection Manager Database Schema Reference 12.1

http://www.symantec.com/docs/DOC4324

Also, Check this Thread: https://www-secure.symantec.com/connect/forums/sql-querys-database

Extract SEPM Reports into clear SQL query - A step by step process

https://www-secure.symantec.com/connect/articles/extract-sepm-reports-clear-sql-query-step-step-sepm-hack

Compilation of SQL queries to the SEPM database

https://www-secure.symantec.com/connect/articles/compilation-sql-queries-sepm-database

SEP Client Information Query. Query result shows:
♦ SEP computername
♦ Installed SEP Version
♦ AV definition revision with the timestamp of the last update
♦ Assignement to SEPM Group
♦ Operating System
♦ Logged-on User
♦ IP address
♦ Last scan time

SELECT DISTINCT "SEM_AGENT"."DELETED"
  , "PATTERN"."VERSION"
  , "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
ORDER BY "Computer Name"

Regards,