Video Screencast Help

Compilation of SQL queries to the SEPM database

Created: 26 Sep 2013 • Updated: 17 Feb 2014 | 32 comments
Language Translations
SebastianZ's picture
+26 26 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 32 CommentsJump to latest comment

ℬrίαη's picture

Awesome article. Much needed.

​​

+1
Login to vote
Ambesh_444's picture

Thumbs up https://www-secure.symantec.com/connect/sites/defa...); padding-right: 27px; margin-right: 3px; font-weight: bold; line-height: 15px; color: rgb(114, 168, 38); text-decoration: none; font-family: helvetica, arial, clean, sans-serif; display: inline !important; background-position: 100% -60px; background-repeat: no-repeat no-repeat;">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
Social Media Support Lead
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.

+1
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?

0
Login to vote
adutchman's picture

We have a rather large list of Explicit Group Update Providers (over 300).

Examples:

  • 192.168.1.0 is mapped to Host Name: myserver1.mydomain.local
  • 192.168.2.0 is mapped to Host Name: myserver2.mydomain.local

I can't seem to find where this information is stored in the SEP database.

Does anyone have a SQL query that can retrieve this Explicit GUP list?

The following columns are desired...

  • Client Subnet network address
  • GUP Mapping Type
  • GUP Mapping Value
  • GUP Port
0
Login to vote
souvik29's picture

Really grt article.

0
Login to vote
Sonihal's picture

This need to be pinned to the top.

0
Login to vote
Dawood H's picture

Does anyone have an SQL query for mapping policies against groups?

There is a Report for this on SEPM console (Reports -> Audit Reports) but the output is in easy to navigate.

An SQL query that would pull this information in a tabular manner will be really helpful.

0
Login to vote
interchk's picture

Enjoy >>>>>>

Select COMPUTER_NAME, Name, sa.PROFILE_SERIAL_NO
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

+2
Login to vote
souvik29's picture

Hi Guys,

Can anybody help me with "Duplicate Host name/Computer name query with time stamp"?

Help will be much appreciated.

0
Login to vote
interchk's picture

it will give you both online and offline. You can delete based on the timestamp

DECLARE @TimeZoneDiff int   
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())
SELECT UPPER([COMPUTER_NAME]) as HOST_NAME
    ,case when comp.status=1 then 'ONLINE'
    else 'OFFLINE' end as Status
    ,dateadd(s,convert(bigint,SA.TIME_STAMP)/1000,'01-01-1970 00:00:00') AS TIME_STAMP
   , sa.[COMPUTER_ID]
   , [HARDWARE_KEY]
   , [CURRENT_LOGIN_USER]
   , [IP_ADDR1_TEXT]
FROM [V_SEM_COMPUTER]as sa
LEFT OUTER JOIN SEM_AGENT as comp ON SA.COMPUTER_ID = comp.COMPUTER_ID
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

+1
Login to vote
interchk's picture

enjoy >>>>>>

DECLARE @TimeZoneDiff int   
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())
SELECT UPPER([COMPUTER_NAME]) as HOST_NAME
    ,case when comp.status=1 then 'ONLINE'
    else 'OFFLINE' end as Status
    ,dateadd(s,convert(bigint,SA.TIME_STAMP)/1000,'01-01-1970 00:00:00') AS TIME_STAMP
   , sa.[COMPUTER_ID]
   , [HARDWARE_KEY]
   , [CURRENT_LOGIN_USER]
   , [IP_ADDR1_TEXT]
FROM [V_SEM_COMPUTER]as sa
LEFT OUTER JOIN SEM_AGENT as comp ON SA.COMPUTER_ID = comp.COMPUTER_ID
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

0
Login to vote
Marlin's picture

Great info here.  But can I make a request?

What's the simplest query that could retrieve computername and serial number?  The serial number is displayed at the very bottom of an individual client properties dialog but is not present in any of the prepared reports in the SEPM console.  We would find it very useful to have the serial number expressed in the client detail reports from the SEPM, but a simple list of computername and serial number could be combined with the existing reports to give us a more useful client listing.

Thanks!

PS - There are numerous requests for this kind of report going back to 2010.  https://www-secure.symantec.com/connect/idea/endpoint-asset-number-and-serial-number-collection-and-report

+1
Login to vote
interchk's picture

@Marlin

SEPM database > simplest query

Select 
COMPUTER_NAME, BIOS_SERIALNUMBER 
from 
SEM_COMPUTER

0
Login to vote
John Santana's picture

Many thanks for sharing such a great script here !

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

0
Login to vote
John Santana's picture

Hi All,

Using the latest DB schema for SEP 12.1.6, how can I get the SQL script to display which clients is not using PTP or NTP components enabled ?

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

0
Login to vote
Dawood H's picture

Does anyone have an SQL query for mapping policies against groups?

There is a Report for this on SEPM console (Reports -> Audit Reports) but the output isn't easy to navigate.

An SQL query that would pull this information in a tabular manner will be really helpful.

+1
Login to vote
interchk's picture

select Name, Sa.PROFILE_SERIAL_NO, COUNT(*) as Total
from 
SEM_AGENT as SA
LEFT OUTER JOIN IDENTITY_MAP as ID_MAP ON ID_MAP.ID = SA.GROUP_ID
group by sa.PROFILE_SERIAL_NO, NAME
order by Total desc

+1
Login to vote
mariusz.komuda's picture

Hello Guys, 

Could you please help me with one topic?

I need SQL statemet to list oomputers secured by SEP with assigned name and IP of GUP. 

I will be very grateful

Regards

Mariusz

0
Login to vote
SMoore's picture

Anyone get "Arithmetic overflow error converting expression to data type int" when running the SEP Client Information Query? I'm running the query against a 2008 R2 database. Date conversions like this is new to me so hoping someone has some training wheels available.

0
Login to vote
jcyun's picture

Hello Guys

Very good material
There is one question.
We know how to combine the "SEP Client infomation query", "computer status check" on the two query statement in the query above?

Thanks!

+1
Login to vote
arvinder1's picture

I have issue with date format. I am trying to use the query to send management report that contains some dates so I use TIME_STAMP and the query works ok, gives me the output in format 2016-07-20 12:50:48.000 when I use 

,dateadd(s,convert(bigint,SA.TIME_STAMP)/1000,'01-01-1970 00:00:00') AS TIME_STAMP

but, I have to export this to .csv file and email the csv to management. Everything else works ok, but the date gets truncated and I only see 50:48:0 in the TIME_STAMP column instead of full date. Even saving the query results in SQL management studio in a text file and importing into excel removes the date. I want the date to show up in format MM/DD/YYYY with no time in the column instead just like its displayed in the SEPM GUI.

Does anyone know how to do that?

Thanks

0
Login to vote
ColonialAdmin's picture

Greate article!

Select 
COMPUTER_NAME, BIOS_SERIALNUMBER ,Operation_system as Operating System
from 
SEM_COMPUTER

Does anyone know how to get this query to pull computer name, serial number, and only windows 7 clients for the past year?

0
Login to vote