In the interest of keeping things together, here are some that I use

Taken from 'generate report about Virus definition installed on All SEP CLIENTS managed by SEPM'

 select i.computer_name
, agent_version
, pat.version as vd_version
, dateadd(s,convert(bigint,LAST_UPDATE_TIME)/1000,'01-01-1970 00:00:00') lastupdatetime
, g.name as group_name
, OPERATION_SYSTEM
, i.CURRENT_LOGIN_USER
, i.MAC_addr1
, i.ip_addr1_text
, i.DELETED
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 

Another query for PTP at 'Need query to get PTP definitions from SQL'. While I link directly to grozdanis comment that is marked as the answer, the code is my modification of his code as seen further down the comment stream.

 SELECT SEM_COMPUTER.COMPUTER_NAME
, PATTERN.PATTERN_TYPE
, PATTERN.PATTERNDATE
, PATTERN.REVISION
FROM SEM_COMPUTER INNER JOIN
SEM_AGENT ON SEM_COMPUTER.COMPUTER_ID = SEM_AGENT.COMPUTER_ID
INNER JOIN SEM_CONTENT ON SEM_AGENT.AGENT_ID = SEM_CONTENT.AGENT_ID
INNER JOIN PATTERN ON SEM_CONTENT.PATTERN_IDX = PATTERN.PATTERN_IDX
WHERE (PATTERN.PATTERN_TYPE = 'SYKNAPPS_CAL')
ORDER BY SEM_COMPUTER.COMPUTER_NAME, PATTERN.PATTERN_TYPE, PATTERN.PATTERNDATE, PATTERN.REVISION 

You might also want to look into the .hta code for the GUP Content Distribution monitor for SQL relating to GUPs.

PS If you want others to share code, it would be nice if you started first. Show us that you are serious about this.

Please mark the post that best solves your problem as the answer to this thread.

Thanks and here you are...

Risk Distribution by Group (7 days)...

select REPLACE (G.NAME, 'My Company\','')  as Clientgroup, SUM(A.NoOfViruses) as NrOfViruses
from Alerts as A with (NOLOCK)
INNER JOIN IDENTITY_MAP G with (NOLOCK) on G.ID=A.Clientgroup_Idx
where A.Alertdatetime >= DATEADD(day, -7, CURRENT_TIMESTAMP)and   A.Mother_Idx = ''    and   A.DELETED = 0  

group by G.NAME order by NrOfViruses desc

Translate every row in the table alerts...

SELECT ALERTS.ALERTDATETIME,  ALERTS.ALERTINSERTTIME, ALERTS.ALERTENDDATETIME, USER_NAME, V_SEM_COMPUTER.COMPUTER_NAME, V_SEM_COMPUTER.IP_ADDR1_TEXT, VIRUS.VIRUSNAME, SOURCE, NOOFVIRUSES, FILEPATH, DESCRIPTION, A1.Actualaction,
A2.Actualaction as Requestedaction, A3.Actualaction as Secondaryaction, SOURCE_COMPUTER_NAME, SOURCE_COMPUTER_IP
FROM ALERTS
INNER JOIN V_SEM_COMPUTER ON COMPUTER_IDX = COMPUTER_ID
INNER JOIN VIRUS ON ALERTS.VIRUSNAME_IDX = VIRUS.VIRUSNAME_IDX
INNER JOIN Actualaction A1 on ALERTS.Actualaction_idx = A1.Actualaction_idx
INNER JOIN Actualaction A2 on ALERTS.Requestedaction_idx = A2.Actualaction_idx 
INNER JOIN Actualaction A3 on ALERTS.Secondaryaction_Idx = A3.Actualaction_idx
WHERE ALERTDATETIME >= DATEADD(day, -7, CURRENT_TIMESTAMP)
order by ALERTDATETIME

Sometimes, due to imaging, or VDI or corruption or user error, etc etc; you get duplicate computer objects in the database.

This hardware ID is stored in the SEPHWID.XML file which you can't see in the SEPM console. The related CLIENT_ID you can see in the console when viewing the properties for the computer object.

Running this scrip will show you those objects.

USE Antivirus_SEM5 /* Your DB name */
SELECT COMPUTER_NAME
   , COMPUTER_DOMAIN_NAME
   , HARDWARE_KEY
   , COMPUTER_ID
   , CLIENT_ID
   , DOMAIN_ID
   , GROUP_ID
   , DESCRIPTION
   , USER_NAME
   , dateadd(s,convert(bigint,CREATION_TIME)/1000,'01-01-1970 00:00:00') CREATION_TIME
   , dateadd(s,convert(bigint,TIME_STAMP)/1000,'01-01-1970 00:00:00') TIME_STAMP
FROM SEM_CLIENT 
WHERE HARDWARE_KEY IN ( 
   SELECT HARDWARE_KEY 
   FROM SEM_CLIENT 
   GROUP BY HARDWARE_KEY 
   HAVING COUNT(HARDWARE_KEY) >1) AND HARDWARE_KEY != ''
ORDER BY HARDWARE_KEY, COMPUTER_NAME

Please mark the post that best solves your problem as the answer to this thread.

You can use this script to generate report and send email for specific machines 

https://www-secure.symantec.com/connect/articles/script-monitoring-av-status-clients-desktoplaptop

SEPHWID.XML has the Hardware ID that supposedly uniquely identifies a computer object in the DB.

This Hardware ID is also recorded in the Registry in key

HKEY_LOCAL_MACHINE\SOFTWARE\Symantec\Symantec Endpoint Protection\SMC\Sylink\Sylink\HardwareID 

Unfortunately, this Hardware ID is not displayed in the SEP console. Looking at the client properties, you will see a Unique ID on the General tab.

The query below ties these two facts together, useful when you need to find duplicate Hardware IDs and their client objects.

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 [Antivirus_SEM5].[dbo].[SEM_AGENT] as [Agent] inner join [Antivirus_SEM5].[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

The WHERE clauses have been individually commented out. Delete the beginning /* and ending */ for the required WHERE clause.

Please mark the post that best solves your problem as the answer to this thread.

What a pity?

Please mark the post that best solves your problem as the answer to this thread.

Here is a small and modest piece of code I'm using sometimes to check the size of the stored content in GB (without client packages) in the database (works for both DB types):

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

You should checkout IT Analytics. You can pretty much create any report or dashboard you want with excel, SQL report builder, etc...

It already shops with a number of reports and dashboards.

The only real thing I see missing from analytics is the ability to report on learned applications

Does anyone have a SQL query for learned applications?

I'm trying to run a query and include the learned application information like file name, size, checksum, etc... as well as client information such as client name, domain, SEPM group, etc...

Getting the SEPM group name to return in a query is where I've had issues.

Thanks to soni posted here (a condensed & useful result compared to [SEM_COMPUTER]:

SELECT DISTINCT "SEM_AGENT"."DELETED"
  ,"PATTERN"."VERSION"
  ,"PATTERN"."PATTERNDATE"
  ,"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"

Please mark the post that best solves your problem as the answer to this thread.

This query will find entries in the database that have duplicate computer names (based on finding duplicate HW IDs from above.)

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

This will list the machine with the most recent contact at the top.

Please mark the post that best solves your problem as the answer to this thread.

A lot of queries with time stamps have this format

dateadd(s,convert(bigint,[TIME_STAMP])/1000,' 01-01-1970 00:00:00') as [Calculated Time Stamp]

To display the time field in your local time zone, change the '00:00:00' field after 1970 to your timezone offset from UTC.

PS I guess, but am not sure, that for people west of the Greenwich meridian, that time would be in 1969?

PPS Don't forget the DST offset too.

Please mark the post that best solves your problem as the answer to this thread.

SELECT COMPUTER_NAME FROM SEM_CLIENT WHERE EXTRA_FEATURE=1 AND DELETED=0;

Hi,

Does anyone have a query to show all of the machines with out of date definition files?

I've been working on this for a while and can't come up with it. I've been looking at the following tables:

Pattern, Notification, SEM_COMPUTER, Notificationalerts

Thank you

Hi All,

The table ALERTS contains informations about security alerts

Thanks and Regards

Prakash Kamalakannan

Based on a post for a report on what clients are downloading from GUPs, here is a rough query that needs more refinement, but gives the basic info.

SELECT [COMPUTER_ID]
      ,[HARDWARE_KEY]
      ,[HOST_NAME]
      ,[TIME_STAMP]
      ,[EVENT_ID]
      ,[EVENT_TIME]
      ,[SEVERITY]
      ,[AGENT_ID]
      ,[CATEGORY]
      ,[EVENT_SOURCE]
      ,[EVENT_DESC]
      ,[LOG_IDX]
  FROM [Antivirus_SEM5].[dbo].[AGENT_SYSTEM_LOG_1]
  WHERE [EVENT_SOURCE] = 'SYLINK'
UNION ALL
  SELECT [COMPUTER_ID]
      ,[HARDWARE_KEY]
      ,[HOST_NAME]
      ,[TIME_STAMP]
      ,[EVENT_ID]
      ,[EVENT_TIME]
      ,[SEVERITY]
      ,[AGENT_ID]
      ,[CATEGORY]
      ,[EVENT_SOURCE]
      ,[EVENT_DESC]
      ,[LOG_IDX]
  FROM [Antivirus_SEM5].[dbo].[AGENT_SYSTEM_LOG_2]
  WHERE [EVENT_SOURCE] = 'SYLINK'
ORDER BY [HOST_NAME], [HARDWARE_KEY]



This does still need some cleaning up. Things like Time_Stamp, Event_Time. What do Severity & Category mean. Where does LOG_IDX link to.

Just saving it for posterity.

Please mark the post that best solves your problem as the answer to this thread.

A list of GUP servers

kinoranyi.zoltan asked how to extract a list of GUP servers from the DB. This script is taken from that thread:

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 [Antivirus_SEM5].[dbo].[GUP_LIST] LEFT OUTER JOIN
                      dbo.SEM_COMPUTER ON dbo.GUP_LIST.COMPUTER_ID = dbo.SEM_COMPUTER.COMPUTER_ID

I myself am not a fan of the IP address conversion, I'd rather display the IP_ADDR_TEXT field from V_SEM_COMPUTER. It does publish the conversion again, thanks to Pete_4u2002.

Please mark the post that best solves your problem as the answer to this thread.

what is more and more important with virtualisation is to check whether your virtual systems are in the correct groups that may have specific policies for the virtual environment.

Currently I have included Citrix, Intel VMWare, Virtual Box and Hyper-V. (Feel free to comment for other virtual platforms.

select
    DISTINCT SC.COMPUTER_NAME,
    SC.IP_ADDR1_TEXT,
    G.NAME,
    SC.BIOS_VERSION
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
;

In addition you can run this sql via bcp and write the result set to a file that can be used for the MoveClients.vbs to automatically proceed with the clearing of your systems.

For example to find all XEN Systems not in your default xen group and to create the Hostgroups file used to move these into a seperate group would be...

select
    DISTINCT SC.COMPUTER_NAME,
    ',' as "Seperator1",
    'My Company\XEN-Systems' as "TargetGroup"
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 '%My Company\XEN-Systems%'
AND
SC.BIOS_VERSION IN (
    select BIOS_VERSION
    from dbo.V_SEM_COMPUTER
    where BIOS_VERSION LIKE '%XEN%'
)
order by SC.COMPUTER_NAME
;

Domain name and ID

EssKay wanted to know how to extract the name of the SEP Domain form the database.

I put this together for him:

SELECT [ID]
      ,[TYPE]
      ,[DOMAIN_ID]
      ,[NAME]
      ,[DESCRIPTION]
FROM [Antivirus_SEM5].[dbo].[BASIC_METADATA]
WHERE [Type] = 'SemDomain'

Please mark the post that best solves your problem as the answer to this thread.

I found one that is probably the most missed feature in SEPM by every admin.

When you need to get all logs for a client or for a group currently you may need to waste like >5 mins to get all your logs related to System, Security, Behavior and Traffic Log

Here the lifechanging SQL that will deliver all logs right away... Just put your part of the groupname or hostname into the declaration and execute the statement.

DECLARE @Hostname nvarchar(512)         

SELECT @Hostname  = '%%'         

DECLARE @Groupname nvarchar(2000)       

SELECT @Groupname  = '%%'        

             

SELECT       

       DATEADD(HOUR,+2,DATEADD(SECOND,ASL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",

       ASL.HOST_NAME        AS "COMPUTER",

       S.NAME        AS "SEPM",

       G.NAME        AS "GROUP",

       ASL.EVENT_SOURCE     AS "EVENT-SOURCE",

       ASL.EVENT_DESC       AS "DESCRIPTION",

       ASL.EVENT_DATA       AS "RULENAME",

       'N/A'  AS "LOCAL-IP",

       'N/A'  AS "REMOTE-IP",

       'N/A'  AS "APPLICATION",

       'N/A'  AS "LOCATION",

       'N/A'  AS "INTRUSION URL",

       NULL   AS "LOCAL-PORT",

       NULL   AS "REMOTE-PORT"

             

FROM dbo.V_AGENT_SYSTEM_LOG ASL         

       JOIN dbo.V_GROUPS G 

       ON ASL.GROUP_ID = G.ID    

       JOIN dbo.V_SERVERS S

       ON ASL.SERVER_ID = S.ID   

WHERE G.NAME like @Groupname            

AND ASL.HOST_NAME like @Hostname        

UNION ALL           

SELECT       

       DATEADD(HOUR,+2,DATEADD(SECOND,ASL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",

       ASL.HOST_NAME AS "COMPUTER",

       S.NAME AS "SEPM",

       G.NAME AS "GROUP",

       'N/A'  AS "EVENT-SOURCE",

       ASL.EVENT_DESC       AS "DESCRIPTION",

       'N/A'  AS "RULENAME",

       ASL.LOCAL_HOST_IP_TEXT     AS "LOCAL-IP",

       ASL.REMOTE_HOST_IP_TEXT    AS "REMOTE-IP",

       ASL.APP_NAME  AS "APPLICATION",

       ASL.LOCATION_NAME    AS "LOCATION",

       (ASL.INTRUSION_URL + '  _Payload URL/' + ASL.INTRUSION_PAYLOAD_URL)  AS "INTRUSION URL",

       NULL AS "LOCAL-PORT",

       NULL   AS "REMOTE-PORT"

             

FROM dbo.V_AGENT_SECURITY_LOG ASL       

       JOIN dbo.V_GROUPS G 

       ON ASL.GROUP_ID = G.ID    

       JOIN dbo.V_SERVERS S

       ON ASL.SERVER_ID = S.ID   

WHERE G.NAME like @Groupname            

AND ASL.HOST_NAME like @Hostname        

UNION ALL                 

SELECT       

       DATEADD(HOUR,+2,DATEADD(SECOND,ABL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",

       ABL.HOST_NAME AS "COMPUTER",

       S.NAME AS "SEPM",

       G.NAME AS "GROUP",

       CASE  

       WHEN ABL.VAPI_NAME is NULL THEN 'Tamper Protection'   

       ELSE ABL.VAPI_NAME  

       END AS "EVENT-SOURCE",

       ABL.DESCRIPTION      AS "DESCRIPTION",

       ABL.RULE_NAME AS "RULENAME",

       'N/A'  AS "LOCAL-IP",

       'N/A'  AS "REMOTE-IP",

       ABL.CALLER_PROCESS_NAME    AS "APPLICATION",

       'N/A'  AS "LOCATION",

       'N/A'  AS "INTRUSION URL",

       NULL   AS "LOCAL-PORT",

       NULL   AS "REMOTE-PORT"

FROM dbo.V_AGENT_BEHAVIOR_LOG ABL       

       JOIN dbo.V_GROUPS G 

       ON ABL.GROUP_ID = G.ID    

       JOIN dbo.V_SERVERS S

       ON ABL.SERVER_ID = S.ID   

WHERE G.NAME like @Groupname            

AND ABL.HOST_NAME like @Hostname 


UNION ALL           


SELECT       

       DATEADD(HOUR,+2,DATEADD(SECOND,ATL.EVENT_TIME / 1000, '1970/01/01 00:00')) AS "EVENT-TIME",

       ATL.HOST_NAME AS "COMPUTER",

       S.NAME AS "SEPM",

       G.NAME AS "GROUP",

       'N/A'  AS "EVENT-SOURCE",

       ('Action: '+  CASE  

       WHEN ATL.BLOCKED = 1 THEN 'BLOCKED'     

       WHEN ATL.BLOCKED = 0 THEN 'ALLOWED'

       END   

        +' - Protcol: '+   

       CASE  

       WHEN ATL.NETWORK_PROTOCOL = 1 THEN 'IP, Ethernet'     

       WHEN ATL.NETWORK_PROTOCOL = 2 THEN 'TCP'

       WHEN ATL.NETWORK_PROTOCOL = 3 THEN 'UDP'

       WHEN ATL.NETWORK_PROTOCOL = 4 THEN 'ICMP'      

       END

        +' - Direction: '+ 

       CASE  

       WHEN ATL.TRAFFIC_DIRECTION = 1 THEN 'IN'

       WHEN ATL.TRAFFIC_DIRECTION = 2 THEN 'OUT'      

       WHEN ATL.TRAFFIC_DIRECTION = 0 THEN 'Unknown'

       END )AS "DESCRIPTION",

       ATL.RULE_NAME AS "RULENAME",

       ATL.LOCAL_HOST_IP_TEXT     AS "LOCAL-IP",

       ATL.REMOTE_HOST_IP_TEXT    AS "REMOTE-IP",

       ATL.APP_NAME  AS "APPLICATION",

       ATL.LOCATION_NAME    AS "LOCATION",

       'N/A'  AS "INTRUSION URL",

       ATL.LOCAL_PORT       AS "LOCAL-PORT",

       ATL.REMOTE_PORT      AS "REMOTE-PORT"

FROM dbo.V_AGENT_TRAFFIC_LOG ATL        

       JOIN dbo.V_GROUPS G 

       ON ATL.GROUP_ID = G.ID    

       JOIN dbo.V_SERVERS S

       ON ATL.SERVER_ID = S.ID   

WHERE G.NAME like @Groupname            

AND ATL.HOST_NAME like @Hostname               

ORDER BY "EVENT-TIME" DESC, "COMPUTER"

;

Find objects in Usermode

Thanks to AravindKM in this post about how to find Usermode objects in the DB. I added the computer and user names.

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

Please mark the post that best solves your problem as the answer to this thread.

In February 2012, J.Bonner asked how to enumerate the deleted groups in the SQL database.

Landon Manning responded with this query:

DECLARE @xmlDoc varchar(max)
DECLARE @handle INT;
SET @xmlDoc = ( select convert(varchar(max), convert(varbinary(max), content)) from basic_metadata where type='SemClientGroupTree' );

EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
select ID,Name from identity_map where type='SemClientGroup' and Id not in(
SELECT * FROM OPENXML (@handle, '//*', 1) WITH("Id" VARCHAR(64)) where Name != 'NULL' and Id != 'NULL'
)
EXEC sp_xml_removedocument @handle;

This is supposed to work on MS-SQL 2005. My SQL 2008 installation reports an error about more than one result for the subquery.

Please mark the post that best solves your problem as the answer to this thread.

Hi,

New to SEP DB and need help with a query,

I am trying to find the list of machines that have synced with SEP in the last 30 days.

In other words I only want active machines or machines that SEP thinks are active in the last 30 days

Here's what I have so far. 

You can use IT analytics for report please refer following documant

https://www-secure.symantec.com/connect/blogs/it-analytics-now-available-sep-customers

thank you Riya31 is that for free or paid application addon ?

There are 4 Mac Address fields in the sepadmin.SEM_COMPUTER, does anybody know which one to use in a query to identify a machine ?

Apparently, if you have Application & Device policies enabled, you can read the recorded USB activities from the AGENT_BEHAVIOUR_LOG tables. We currently don't use ADC, so this is just a simplistic sample.

SELECT * FROM AGENT_BEHAVIOUR_LOG_1
UNION
SELECT * FROM AGENT_BEHAVIOUR_LOG_2
ORDER BY TIME_STAMP

Thanks to Vikram Kumar.

Please mark the post that best solves your problem as the answer to this thread.

For those using the embedded database, all of these queries should be valid. Ashish Sharma posts that you can use

"C:\Program Files\Symantec\Symantec Endpoint Protection Manager\ASA\win32\DBISQLC.EXE"

to connect and log in with the recorded username & password.

Please mark the post that best solves your problem as the answer to this thread.

Hi all,

I'm looking for a query, which will show me detailed information about SONAR events (e.g. Risk Type of event).

I already access the table "ALERTS" and found the logged event about my tracked machine. But, the table ALERTS don't offers me as much as information like the "Proactive Threat Protection Log" on the machine does.

The log on the machines shows me information like "Risk = Hosts File Change" and "Risk Type = System Change Risk".

Where in the SEP database is the information stored, to check if a logged Alert has such an Risk Type?

Thanks in Advance!