Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

SEP 11/12 database fields to determine latest AV, PTP, NTP defs, last successful communication time

Created: 23 Jan 2014 • Updated: 23 Jan 2014 | 3 comments
This issue has been solved. See solution.

We are viewing network reports that show numerous IP addresses that are pulling large bandwidth from our SEPM servers, and are trying to find a trend.

 

I am creating a custom report that takes an IP address, and queries the SQL database for various information about the SEP client. Here is the SQL query so far:

 

SELECT dbo.sem_computer.COMPUTER_NAME,[IP_ADDR1_TEXT],(dbo.SEM_AGENT.FREE_DISK/1073741824)as 'Free Disk Space (GB)',
(dbo.SEM_COMPUTER.DISK_TOTAL/1073741824) as 'Total Disk Space (GB)',
(dbo.SEM_AGENT.FREE_MEM/1073741824) as 'Free Memory (GB)', (dbo.SEM_COMPUTER.MEMORY/1073741824) as 'Total Memory (GB)',
dbo.SEM_COMPUTER.OPERATION_SYSTEM,NAME, SEM_AGENT.MAJOR_VERSION
FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT, dbo.SEM_AGENT
WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
AND dbo.SEM_CLIENT.COMPUTER_ID = dbo.SEM_COMPUTER.COMPUTER_ID
AND dbo.SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
AND [IP_ADDR1_TEXT] = 'TYPE IN THE IP ADDRESS'

 

 

What tables and colums have the date of Antivirus, Proative Threat Protection, Network Threat protection definitions, and the last time the SEP client successfully communicated with the SEPM server?

Operating Systems:

Comments 3 CommentsJump to latest comment

RSASKA's picture

Wow, I was reading https://www-secure.symantec.com/connect/articles/compilation-sql-queries-sepm-database and it is giving me even more ideas on how to develop trend of SEP clients that are pulling high bandwidth from our SEPM servers!

The Enemy's greatest fear is that you'll discover who you really are, what you're really worth, and where you're headed.

 

.Brian's picture

The DB schema is also a good reference. I've attached it for you.

 

AttachmentSize
Schema_Reference_Guide_SEP12.1.pdf 1.34 MB

Please click the "Mark as solution" link at bottom left on the post that best answers your question. This will benefit admins looking for a solution to the same problem.