Endpoint Protection

Compilation of SQL queries to the SEPM database 

09-26-2013 10:06 AM

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

 

Statistics
0 Favorited
30 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

10-07-2019 10:58 AM

For me the easiset way to get a count of SEP Client Versions is to go into the SEPM and then the Reports tab, choose Computer Status and Symantec Endpoint Protection Product Versions.

10-07-2019 10:46 AM

Is there an easy way to get a count of the SEPM client versions?

03-11-2019 05:39 AM

HI Geeks,

I am looking for the SQL Query for below situation:

Situation:

Once any change made in SEPM AV ,firewall and other policies, there is an audit log generated at SEPM console.

Requirement:

Which SQL query to run in SEPM(Remote SQL) to pull audit logs.

Quick response appreciated.

Regards

Saqib Raza

04-26-2018 09:32 AM

SELECT
COMPUTER_NAME,
IP_ADDR1_TEXT,
CURRENT_LOGIN_USER,
AV_DEF,
IPS_DEF,
SONAR_DEF,
MRCLEAN_DEF,
AGENT_VERSION,
COMPUTER_DOMAIN_NAME,
OPERATION_SYSTEM,
STATUS,
LAST_UPDATE_TIME,
LAST_SCAN_TIME,
FREE_MEM,
FREE_DISK
FROM [sem5].[dbo].[AGENT_DEF_CACHE]
INNER JOIN SEM_AGENT ON SEM_AGENT.AGENT_ID=AGENT_DEF_CACHE.AGENT_ID
INNER JOIN V_SEM_COMPUTER ON V_SEM_COMPUTER.COMPUTER_ID=SEM_AGENT.COMPUTER_ID
WHERE V_SEM_COMPUTER.DELETED=0
ORDER BY AV_DEF DESC

 

For alerting on all definition sets

04-24-2018 07:57 AM

I have been using this search query to find the tables that contain the data I need and then I create the query. I hope you guys find this helpful. I found this on StackOverflow https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

 

04-23-2018 04:00 PM

Is there a SQL Query to also show Host and IPS definitions? I see a lot for Virus Definitions, I would also like to see IPS definitions.  

03-13-2018 12:13 PM

Great one. So we have to perform a cleanup tasks for Admins from SEPM console. So can i pull a query with list of admins and their last login timestap.

 

As per the report i can start cleanup activty

03-12-2018 10:29 AM

Did you find a way to get component status?

03-12-2018 10:28 AM

@daveshaefer, did you find a way to get component status? I need the same. Thanks!

02-13-2018 11:39 AM

There is a view in SEPM called "Protection Technology" it lists Name, Health State, Logon User, IPAddress,  Last Scan, AntiVirus Status, Firewall Status,......there are 21 categories.  Can someone do this view with SQL? I am really looking to see if "Status" is Enabled or Disabled (Memory Exploit, Network Intrusion, Browser Intrusion, Tamper Protection Status, Early Launch.

 

Thanks

Dave

 

08-23-2016 10:54 AM

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?

08-09-2016 03:51 PM

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

07-21-2016 01:14 AM

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!

07-06-2016 10:20 AM

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.

05-12-2016 01:02 AM

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

 

04-12-2016 08:00 AM

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

03-21-2016 04:47 AM

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.

03-20-2016 06:55 PM

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 ?

03-17-2016 08:05 PM

Many thanks for sharing such a great script here !

10-25-2015 04:30 AM

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

10-25-2015 04:30 AM

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

10-25-2015 04:27 AM

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

10-25-2015 04:19 AM

@Marlin

SEPM database > simplest query

Select 
COMPUTER_NAME, BIOS_SERIALNUMBER 
from 
SEM_COMPUTER

09-08-2015 10:56 AM

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

 

 

 

08-31-2015 08:32 AM

Hi Guys,

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

Help will be much appreciated.

 

 

06-09-2015 07:47 AM

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.

05-26-2015 10:52 AM

This need to be pinned to the top.

02-26-2015 12:44 PM

Really grt article.

02-26-2015 10:04 AM

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

 

12-03-2014 05:23 AM

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

09-03-2014 01:35 AM

Awesome.  Useful and Good information.

08-31-2014 09:31 PM

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.

07-29-2014 08:16 PM

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

07-29-2014 03:12 PM

Awesome.  Good work

12-26-2013 10:50 PM

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;

12-10-2013 07:15 AM

Good information.

10-05-2013 11:20 PM

So good!

09-27-2013 02:54 AM

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

09-27-2013 02:50 AM

Thumbs Up For Great Artical

 

Some Of SQL query also available this thread

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

09-27-2013 02:41 AM

Great article, as usual !!!

09-27-2013 12:48 AM

Thumbs up SebastianZ

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

09-26-2013 04:58 PM

Awesome article. Much needed.

Related Entries and Links

No Related Resource entered.