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

Compilation of SQL queries to the SEPM database

Created: 26 Sep 2013 • Updated: 17 Feb 2014 | 13 comments
Language Translations
SebastianZ's picture
+19 19 Votes
Login to vote

As it often happens not all the required information or more frequently not all information in the expected form can be retieved from the SEPM logs or reports - the necessity for direct queries to SEPM database arise. The purpose of this article is to present some of the most helpful and useful SQL queries that can make lifes of the SEPM administrators much easier when specific information is required in an easily exportable and custommizable form.

There is not really much Symantec documentation covering this topic beside the SQL Schema references - for these please refer to:

Symantec Endpoint Protection Manager 12.1.2 Database Schema
http://www.symantec.com/docs/DOC6039
Symantec™ Endpoint Protection Manager Database Schema Reference
http://www.symantec.com/docs/DOC4935

 

You can find quite a few Symantec Connect threads on the matter, one of real value is the following:

SQL Querys to the database
https://www-secure.symantec.com/connect/forums/sql-querys-database

Beside this several other threads with specific questions and queries can be found - all the information is bit scattered though which makes finding the appropriate and sometimes even working queries a really difficult task. I hope presenting this article will allow you for fast browsing and search of useful queries - please note most of queries presented are not created by myself but taken from different sources in order to make them available in one place. If you have interest in this topic you can follow this article - I will do my best to update it with more queries over the time.

The article points mainly at SEP 12.1 and above - I will as well limit the information included here to queries targeted at getting the information out of the database and not for any changes directly to SEPM database - as such are not recommended by Symantec Support and should be performed from SEPM console level.

Any feedback or suggestions are welcome. Please share as well what kind of queries you would like to use or require in your day to day administrative tasks.

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

select i.COMPUTER_NAME
, AGENT_VERSION
, pat.version as AV_REVISION
, dateadd(s,convert(bigint,LAST_UPDATE_TIME)/1000,'01-01-1970 00:00:00') LASTUPDATETIME
, g.name as GROUP_NAME
, i.OPERATION_SYSTEM
, i.CURRENT_LOGIN_USER
, i.MAC_addr1 "MAC Address"
, IP_ADDR1_TEXT "IP Address"
, i.DELETED "Marked for deletion"
from sem_agent as sa with (nolock) left outer join pattern pat on sa.pattern_idx=pat.pattern_idx
inner join v_sem_computer i on i.computer_id=sa.computer_id
inner join identity_map g on g.id=sa.group_id
inner join identity_map p on p.id=sa.last_server_id
inner join identity_map s on s.id=sa.domain_id
inner join identity_map q on q.id=sa.last_site_id
where
(sa.agent_type='105' or sa.agent_type='151') and sa.deleted='0' and I.DELETED = 0
order by group_name, operation_system, i.COMPUTER_name

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_CLIENT"."COMPUTER_NAME" "Computer Name"
  , "SEM_AGENT"."AGENT_VERSION" "SEP Version"
  ,    "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System"
  , "PATTERN"."VERSION" "AV Revision"
  , 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') "Last Update Time"
  , dateadd(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time"
  , "SEM_CLIENT"."USER_NAME" "User Name"
  , "IP_ADDR1_TEXT" "IP Address"
  , "IDENTITY_MAP"."NAME" "Group Name"
  , "SEM_AGENT"."DELETED" "Marked for deletion"
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"

SEP Client ID information contains:
♦ SEP computername
♦ Client ID, Computer ID, Hardware ID
♦ Client Status
♦ Client creation and last update timestamp
♦ Operating System information
♦ SEP Client Version

SELECT [Comp].[COMPUTER_NAME]
, [Agent].[DELETED]
, [Agent].[CURRENT_CLIENT_ID]
, [Comp].[HARDWARE_KEY]
, [Comp].[COMPUTER_ID]
, dateadd(s,convert(bigint,[Comp].[TIME_STAMP])/1000,'01-01-1970 13:00:00') as Time_Stamp
, [Agent].[STATUS]
, dateadd(s,convert(bigint,[Agent].[CREATION_TIME])/1000,'01-01-1970 13:00:00') as Creation_Time
, dateadd(s,convert(bigint,[Agent].[LAST_UPDATE_TIME])/1000,'01-01-1970 13:00:00') as Last_Update_Time
, [Comp].[OPERATION_SYSTEM]
, [Agent].[AGENT_VERSION]

FROM [dbo].[SEM_AGENT] as [Agent] inner join [dbo].[SEM_COMPUTER] as [Comp] on [Agent].[COMPUTER_ID]=[Comp].[COMPUTER_ID]

/* where [Comp].[COMPUTER_ID] like 'Computer ID as used in the database' */
/* where [Comp].[COMPUTER_NAME] like 'NetBIOS name of computer' */
/* where [Agent].[CURRENT_CLIENT_ID] like 'Unique ID as displayed on General tab of client properties in console' */

order by [Comp].[COMPUTER_NAME] asc

SEP to SEPM Server mapping - easy query to match the managed SEP clients with respective SEPM Servers. Results contain:
♦ SEP client name
♦ SEPM Server ID
♦ Status of the client
♦ IP (decimal) of the client

SELECT SEM_CLIENT.COMPUTER_NAME as Computer, SEM_AGENT.LAST_SERVER_ID as Server, SEM_AGENT.STATUS as Status, SEM_COMPUTER.IP_ADDR1 as IP
From SEM_CLIENT, SEM_AGENT, SEM_COMPUTER
Where SEM_AGENT.COMPUTER_ID = SEM_CLIENT.COMPUTER_ID and SEM_AGENT.STATUS = 1 and SEM_CLIENT.COMPUTER_NAME = SEM_COMPUTER.COMPUTER_NAME

SEP Client System Logs - Query results contain:
♦ Time of the log entry,
♦ SEP client name,
♦ Name of the SEPM managing the client,
♦ Event description from system log
♦ Events are ordered by Time of the event

select DATEADD(s, CONVERT(bigint, l.EVENT_TIME)/1000, '01/01/1970 00:00:00') as Time , c.NAME, l.HOST_NAME, l.EVENT_DESC
from V_SERVERS c,
(select * from AGENT_SYSTEM_LOG_1 union select * from AGENT_SYSTEM_LOG_2) l
where c.ID = l.server_id
order by l.EVENT_TIME desc;

Computer Status check - query for listing the computers with either offline (STATUS = 0) or online status (STATUS = 1). Results give the client computer ID and name.

select SEM_AGENT.COMPUTER_ID, SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.STATUS
from SEM_AGENT
left join SEM_COMPUTER on SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
where sem_agent.STATUS = 0

GUP Server list - shows enabled GUPs (IP address) alongside with the group and subnet assignement.

select Name,IP_ADDR1_TEXT,left(IP_ADDR1_TEXT,Len(IP_ADDR1_TEXT)-CHARINDEX('.',Reverse(IP_ADDR1_TEXT))) as Network from SEM_AGENT as SA 
LEFT OUTER JOIN 
V_SEM_COMPUTER as COMP ON SA.COMPUTER_ID = COMP.COMPUTER_ID
LEFT OUTER JOIN 
IDENTITY_MAP as ID_MAP ON ID_MAP.ID = SA.GROUP_ID
where 
SA.AP_ONOFF!=2 and SA.DELETED='0' and MAJOR_VERSION != '5' and SA.AGENT_TYPE='105'  and SA.computer_id in (select computer_id from GUP_list)
group by
left(IP_ADDR1_TEXT,Len(IP_ADDR1_TEXT)-CHARINDEX('.',Reverse(IP_ADDR1_TEXT))),name,ip_addr1_text
DECLARE @TimeZoneDiff int  
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())
SELECT [GUP_LIST].[GUP_ID]
,[GUP_LIST].[COMPUTER_ID]
,UPPER([SEM_COMPUTER].[COMPUTER_NAME])
,[GUP_LIST].[IP_ADDRESS]
,CAST((case when IP_ADDRESS < 0 then 0xFFFFFFFF + IP_ADDRESS else IP_ADDRESS end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDRESS < 0 then 0xFFFFFFFF + IP_ADDRESS else IP_ADDRESS end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDRESS < 0 then 0xFFFFFFFF + IP_ADDRESS else IP_ADDRESS end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDRESS < 0 then 0xFFFFFFFF + IP_ADDRESS else IP_ADDRESS end & 0xFF as VARCHAR) as GUP_IP_ADDRESS
,[GUP_LIST].[PORT]
,[GUP_LIST].[USN]
,dateadd(minute, @TimeZoneDiff, dateadd(second, [GUP_LIST].[TIME_STAMP]/1000, '01-01-1970 00:00:00')) as [Time Stamp]
,[GUP_LIST].[DELETED]
  FROM [dbo].[GUP_LIST] LEFT OUTER JOIN
dbo.SEM_COMPUTER ON dbo.GUP_LIST.COMPUTER_ID = dbo.SEM_COMPUTER.COMPUTER_ID

Database content size - only refers to stored definition size and does not include client install packages

SELECT SUM(DATALENGTH(CONTENT))/1024.0/1024/1024
AS "content size (GB)" FROM BINARY_FILE
WHERE TYPE='DownloadedContentFile'

Duplicated HWID query - result shows:
♦ Client computer ID and IP address
♦ Logged-in user
♦ Hardware Id

DECLARE @TimeZoneDiff int   
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())
SELECT UPPER([COMPUTER_NAME])
   , [COMPUTER_ID]
   , [HARDWARE_KEY]
   , [CURRENT_LOGIN_USER]
   , dateadd(minute, @TimeZoneDiff, dateadd(second, [TIME_STAMP]/1000, '01/01/1970')) as [Time Stamp]
   , [IP_ADDR1_TEXT]
FROM [V_SEM_COMPUTER]
WHERE [COMPUTER_NAME] in
   (
      SELECT [COMPUTER_NAME]
      FROM [V_SEM_COMPUTER]
      WHERE [DELETED] = 0
      GROUP BY [COMPUTER_NAME]
      HAVING COUNT([COMPUTER_NAME]) >1
   )
ORDER BY [COMPUTER_NAME]
   , [Time Stamp] DESC

Virus definitions query - gives following information:
♦ SEP client computer name
♦ Virus definitions
♦ Last check-in of client to SEPM
♦ SEP Client Version

SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM SEM_COMPUTER
INNER JOIN SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
INNER JOIN SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM SEM_COMPUTER
INNER JOIN SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
ORDER BY SEM_COMPUTER.COMPUTER_NAME ASC, SEM_AGENT.LAST_UPDATE_TIME DESC;

Hardware and BIOS check - following information is returned:
♦ SEP Client computer name
♦ IP Address
♦ MAC Address
♦ Group assignement
♦ BIOS Version
♦ OS Version
♦ Processor Type
♦ Processor Count
♦ RAM Memory in bytes

select DISTINCT SC.COMPUTER_NAME,
    SC.IP_ADDR1_TEXT,
    MAC_ADDR1,
    G.NAME "Group Name",
    SC.BIOS_VERSION,
    OPERATION_SYSTEM,
    PROCESSOR_TYPE,
    PROCESSOR_NUM,
    MEMORY

FROM dbo.V_SEM_COMPUTER SC
INNER JOIN dbo.V_AGENT_SYSTEM_LOG ASL
ON SC.COMPUTER_ID = ASL.COMPUTER_ID
INNER JOIN dbo.V_GROUPS G
ON ASL.GROUP_ID = G.ID

where G.NAME NOT LIKE '%YOUR_VIRTUAL_GROUP_FOR_CLIENTS%'
AND
G.NAME NOT LIKE '%YOUR_VIRTUAL_GROUP_FOR_SERVERS%'
AND
SC.BIOS_VERSION IN (
    select BIOS_VERSION
    from dbo.V_SEM_COMPUTER
    where BIOS_VERSION like '%INTEL  - 6040000%'
    OR BIOS_VERSION LIKE '%XEN%'
    OR BIOS_VERSION LIKE '%VBOX%'
    OR BIOS_VERSION LIKE '%VRTUAL%'
)
order by SC.COMPUTER_NAME
;

Computer/user mode query - reports on either computer mode ([POLICY_MODE] = 1) or user mode ([POLICY_MODE] = 0) on SEP Client

SELECT UPPER([COMPUTER_NAME]) as COMPUTER_NAME
, [USER_NAME]
, [POLICY_MODE]
FROM [SEM_CLIENT]
WHERE [POLICY_MODE] = 1
ORDER BY COMPUTER_NAME


SELECT UPPER([COMPUTER_NAME]) as COMPUTER_NAME
, [USER_NAME]
, [POLICY_MODE]
FROM [SEM_CLIENT]
WHERE [POLICY_MODE] = 0
ORDER BY COMPUTER_NAME

Comments 13 CommentsJump to latest comment

.Brian's picture

Awesome article. Much needed.

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.

+1
Login to vote
Ambesh_444's picture

Thumbs up SebastianZ

Your article is really helpful for us. Good luck for next.

Thank& Regards,

Ambesh

"Your satisfaction is very important to us. If you find above information helpful or it has resolved your issue. Please don't forget to mark the thread as solved."

+2
Login to vote
Javier I.'s picture

Great article, as usual !!!yes

+1
Login to vote
James007's picture

Thumbs Up For Great Artical

Some Of SQL query also available this thread

http://www.symantec.com/connect/forums/sql-querys-database

+1
Login to vote
SebastianZ's picture

Thanks, I have mentioned that thread in the article above as well :D

0
Login to vote
yang_zhang's picture

So good!

If a forum post solves your problem, please flag it as a solution. If you like an article, blog post or download vote it up.
+1
Login to vote
Chetan Savade's picture

Good information.

Chetan Savade
Sr.Technical Support Engineer, Endpoint Security
Enterprise Technical Support
CCNA | CCNP | MCSE | SCTS |

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.<

0
Login to vote
digglife's picture

I wrote a simple perl script for query data from sem database via ODBC,but error occurred when I executed it.

Use of uninitialized value in die at testsepm.pl line 26.

which means the statement handler was uninitialized.

I tested my sql statement with ODBC query tool , it's OK.And it's even hard to believe that the two perl modules I used got problems.

Weird thing is, If I only query one single table, there will be no error.Anything related to WHERE or JOIN statement will generate a failure.Anyone has a clue why?

use strict;
use warnings;
use DBI;
use DBD::ODBC;

my $query = q#
SELECT b.[COMPUTER_NAME] AS HOSTNAME
      ,b.[IP_ADDR1_TEXT] AS IP_ADDRESS
      ,b.[OPERATION_SYSTEM]
      ,b.[SERVICE_PACK]
      ,a.[FREE_DISK]
      ,a.[AGENT_VERSION]
      ,DATEADD(s, a.LAST_UPDATE_TIME/1000, '1970-01-01') AS LAST_UPDATE_TIME
      ,DATEADD(s, a.LAST_SCAN_TIME/1000, '1970-01-01') AS LAST_SCAN_TIME
      ,c.[PATTERNDATE] AS DEFINITION_VERSION
      ,a.[STATUS]
      ,a.[INFECTED]
      ,a.[REBOOT_REQUIRED]
  FROM (SEM_AGENT AS a
  LEFT JOIN SEM_COMPUTER AS b ON a.COMPUTER_ID = b.COMPUTER_ID)
  LEFT JOIN PATTERN AS c ON a.PATTERN_IDX = c.PATTERN_IDX
#;

my $DSN = 'Driver={SQL Anywhere 12};ServerName=SEPM_sepmcloud;'.
          'DatabaseFile=E:\Program Files (x86)\Symantec\Symantec Endpoint Protection Manager\db\sem5;'.
          'CommLinks=tcpip(IP=127.0.0.1:2638)';
my $dbh = DBI->connect( "dbi:ODBC:$DSN","dba","passw0rd",{RaiseError=>1} )
    or die "[WARN] Connection to SEPM Failed :",$DBI::errstr;

print "[INFO] Connected To Local SEPM Database.\n";

my $sth;
$sth = $dbh->prepare($query)
    or die "[WARN] Can't prepare query statement: ",$sth->errstr;
$sth->execute()
    or die "[WARN] Can't Execute SQL Statements : ",$sth->errstr;
my @cloudlist;
while ( my @row = $sth->fetchrow() ) {
    print "@row\n";
    push @cloudlist,\@row;
}
$sth->finish;
$dbh->disconnect;
0
Login to vote
JAunmc's picture

Awesome.  Good work

0
Login to vote
Manipillai's picture

Awesome article. really it is use full for me, Thanks .

>MK

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.<

0
Login to vote
wattsdown's picture

Much appreciated. Symantec support could not give me the query for "last time status changed" and LastUpdateTime in the first query above did it for me.

0
Login to vote
SKP's picture

Awesome.  Useful and Good information.

0
Login to vote
Lumia@720's picture

Does it applies to RU5 as well? With SQL Database?

* Cheers *

0
Login to vote