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

SQL Querys to the database

Created: 12 Jan 2012 • Updated: 04 Sep 2012 | 51 comments

Hi, 

Some time (more often) there are request of specific reports from the SEPM.

Also often the reports from SEPM dont have all the criterias we need.

So I have started to create a "portal" outside the SEPM interface.

A portal that are "asking" question direct to the SEPM SQL database and then show the result on the portal.

The portal is then use for easy and fast get clean report just what they ask for.

Is there anyone that has been involed to this time of report issues, and if you maybe have writen a sql query that show good/important information.

Maybe we can share some sql querys, the sql querys I am working on right now is to have SEM_AGENT, SEM_COMPUTER and SEM_CLIENT , translated with the help of "inner/outer/ joins etc.

 

Anyone that are doing the same job?

Comments 51 CommentsJump to latest comment

Ian_C.'s picture

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.
John Santana's picture

Hi Ian,

Doesthat SQL command still applicable for SEPM v 12.1 RU1 ?

Kind regards,

John Santana
IT Professional

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

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

Ian_C.'s picture

Hi John.

Not sure which of the two queries you refer to. Additionally, I only have an internal DB, not SQL for v12; i.e. I can't test right now.

For the second query about PTP definitions, reviewing the DB schema docs, I would say that it will work, no problem.

For the first query, again after reviewing the schema docs, it must work. I did notice one thing here. In my query, I simply state

, OPERATING_SYSTEM

For completeness sake, this should be

, i.OPERATING_SYSTEM

Bummer, I can't edit my previous post anymore. Please send me your error if this does not fix your problem.

Please mark the post that best solves your problem as the answer to this thread.
Pray4u's picture

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

Ian_C.'s picture

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.
cus000's picture

This one would be helpful in clients migration.

 

Thanks

Vikram Kumar-SAV to SEP's picture

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

Vikram Kumar

Symantec Consultant

The most helpful part of entire Symantec connect is the Search button..do use it.

Ian_C.'s picture

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.
Ian_C.'s picture

What a pity?

Please mark the post that best solves your problem as the answer to this thread.
greg12's picture

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'
thatdude's picture

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

Ian_C.'s picture

IT Analytics has some pretty hefty hardware requirements. Plus software license. For some, that can be a big problem.

And sometimes you just need something quick & dirty. Like the previous post about DB sizes.

If you can get your hands on it, you should definitely go for IT Analitics. It works with more than just SEP, so it can be leveraged across several solutions.

Please mark the post that best solves your problem as the answer to this thread.
rscovel's picture

I know this is a little late, but I just wanted to remind people that ITA is "free" when you have a SEP license. This was a more recent development then this thread. There are still the hardware and software requirements to fulfill, but the license issue should be easily resolved. You simply download the software from the fileconnect site when you download your SEP installation files.

Regards

Russ Scovel
Inside Systems Engineer

Altiris SOS – Endpoint Management and Mobility
Symantec Corporation 
www.symantec.com

thatdude's picture

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.

Ian_C.'s picture

Using the database schema found here, you could fairly easily build your own query.

You will see there is a table called COMPUTER_APPLICATION that could contain what you are looking for. HPP_APPLICATION seems to be related to infections.

Have a look at this:

SELECT *
FROM SEM_APPLICATION

 

Please mark the post that best solves your problem as the answer to this thread.
Mick2009's picture

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

Symantec™ Endpoint Protection Manager Database Schema Reference 12.1
Article: DOC4324   |  Created: 2011-06-27   |  Updated: 2012-04-23   | 
Article URL http://www.symantec.com/docs/DOC4324

 

 

With thanks and best regards,

Mick

Ian_C.'s picture

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.
Ian_C.'s picture

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.
Ian_C.'s picture

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.
Ian_C.'s picture

As a header to your query, place the following two lines before your select statement

DECLARE @TimeZoneDiff int    
SELECT @TimeZoneDiff = datediff(minute, getutcdate(), getdate())

The first two lines declare the variable called @TimeZoneDiff and give it a value in minutes the offset from UTC. I don't know if this is based on the TZ of your work station or the TZ of the server where the DB is hosted.

 

Then, where ever you select a time field from the database, replace [TIME_STAMP] with the name of the field in this line:

dateadd(minute, @TimeZoneDiff, dateadd(second, [TIME_STAMP]/1000, '01/01/1970')) AS [Time Stamp]

This last line adds the TZ offset in minutes to the [TIME_STAMP] field to show the value in your local time.

Yip, this means several queries will have to be updated.

 

Please mark the post that best solves your problem as the answer to this thread.
John Q.'s picture

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

 

Please remember to mark the proper comment as SOLUTION:
 - to identify threads that do not require further assistance
 - to let other visitors know how to fix such issue

Ian_C.'s picture

Related to which machines are 'Unmanaged Detectors', thanks to greg12 from here, we have a list of devices that are currently not managed. Also look at LAN_DEVICE_EXCLUDED to combine these.

SELECT  CL.COMPUTER_NAME AS "Unmanaged Detector"
  , DD.DELETED
  , DD.IP_ADDRESS_TEXT
FROM V_LAN_DEVICE_DETECTED AS DD
INNER JOIN SEM_CLIENT AS CL ON DD.COMPUTER_ID = CL.COMPUTER_ID
Please mark the post that best solves your problem as the answer to this thread.
neb2886's picture

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

John Q.'s picture

The term of "out-of-date" is indefinite: Do you consider clients with 1 days, 2 days, x days old definition as out-of-date?

Here is a query I used to show current AV definitions of all clients, you may adapt it to query specific revisions/groups/etc.:

 

 

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)'

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

ORDER BY SEM_COMPUTER.COMPUTER_NAME ASC, SEM_AGENT.LAST_UPDATE_TIME DESC;

Please remember to mark the proper comment as SOLUTION:
 - to identify threads that do not require further assistance
 - to let other visitors know how to fix such issue

LGL's picture

Hi,

Thanks for this script!

What we didnt manage to get out from the SEPM since we got a time out it only took 6 sec. with your script.

As Im not a scripting guy I would like your help to also include the SEP version in the script.

I have been asked for a report including SEPversion, defs status and computername and my SEPM is just timing out and My IT Anlytic is not in place right now. So an update of the script including that would help me a lot.

Thanks // LGL

 

Ian_C.'s picture

Add the following line twice

, SEM_AGENT.AGENT_VERSION

!! Watch out for the comma at the beginning of the line !!

Here are the two places that you need to add this to:

  1. After the dateadd( ... ) line before the FROM SEM_COMPUTER
  2. At the end of the GROUP BY line

Then you will get which version of SEP is running on the client.

Please mark the post that best solves your problem as the answer to this thread.
Prakash Kamalakannan's picture

Hi All,

The table ALERTS contains informations about security alerts

Thanks and Regards

Prakash Kamalakannan

 

Ian_C.'s picture

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.
Ian_C.'s picture

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.
toby's picture

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
;

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

Best regards!

toby

CISSP / STS / MCP 

Ian_C.'s picture

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.
toby's picture

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"

;

 

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

Best regards!

toby

CISSP / STS / MCP 

Ian_C.'s picture

Hi Toby. Thanks for this long query. Unfortunately, it doesn't run for me.

I get errors about Line 23

JOIN dbo.V_GROUPS G

and I'm sure it will complain about Line 25 as well.

JOIN dbo.V_SERVERS S

Are you using a SEP12 database or SEP11? I can't find those two views in the schema reference guides or in the actual database. 

Please mark the post that best solves your problem as the answer to this thread.
toby's picture

yes its for sep12.

Most likely for sep 11 it should be similar. I dont have the schema reference hands on, but I would think you would need to replace the Views with the according tables and maybe also some column names if its different.

 

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

Best regards!

toby

CISSP / STS / MCP 

John Santana's picture

many thanks for the script Toby !

Kind regards,

John Santana
IT Professional

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

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

Ian_C.'s picture

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.
iamadmin's picture

Hi Ian,

Thanks for the SQL but I believe your query will list those objects in Computer mode, not User mode.

Unless I'm mistaken:

POLICY_MODE 0 = User mode

POLICY_MODE 1 = Computer mode

For those who might benefit, here are my slight mods to your query.

 

To find objects in Computer_Mode:

SELECT UPPER([COMPUTER_NAME]) as COMPUTER_NAME

, [USER_NAME]

, [POLICY_MODE]

FROM [SEM_CLIENT]

WHERE [POLICY_MODE] = 1

ORDER BY COMPUTER_NAME

 

To find objects in User_Mode:

SELECT UPPER([COMPUTER_NAME]) as COMPUTER_NAME

, [USER_NAME]

, [POLICY_MODE]

FROM [SEM_CLIENT]

WHERE [POLICY_MODE] = 0

ORDER BY COMPUTER_NAME

 

-Mike

Ian_C.'s picture

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.
RDsa's picture

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. 

 

<pre>
Select Top 10 s.Computer_ID, s.Computer_Name, 
dateadd(s,convert(bigint,s.TIME_STAMP)/1000,'01-01-1970 00:00:00') as LastTimeStamp,
dateadd(s,convert(bigint,sa.LAST_UPDATE_TIME)/1000,'01-01-1970 00:00:00') LastUpdateTimeAgent,
dateadd(s,convert(bigint,sa.TIME_STAMP)/1000,'01-01-1970 00:00:00') LastTimeStampAgent
from sepadmin.SEM_AGENT sa
join sepadmin.SEM_COMPUTER s on s.COMPUTER_ID=sa.COMPUTER_ID
</pre>
 
I don't have the 30 days feature I can build that.
I am not sure which column or db object to use to get this info.
 
Can someone help ? Thank you.
RDsa's picture

I think I got it 

 
Select s.COMPUTER_NAME, dateadd(s,convert(bigint,MAX(sa.LAST_UPDATE_TIME) )/1000,'01-01-1970 00:00:00') as lastupdatetime  from 
sepadmin.SEM_AGENT sa
join sepadmin.SEM_COMPUTER s on s.COMPUTER_ID = sa.COMPUTER_ID
where dateadd(s,convert(bigint,sa.LAST_UPDATE_TIME)/1000,'01-01-1970 00:00:00') >= DATEADD(d,-30,GetDate())
group by s.COMPUTER_NAME
Order by s.COMPUTER_NAME
 
Can someone help me confirm this ?
Ian_C.'s picture

@RDsa

Doing a comparison between the results of your query and my query further up shows that we get the same results. So yes, you are getting the correct results.

Please mark the post that best solves your problem as the answer to this thread.
John Santana's picture

thanks for sharing this script !

Kind regards,

John Santana
IT Professional

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

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

John Santana's picture

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

Kind regards,

John Santana
IT Professional

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

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

RDsa's picture

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 ?

Ian_C.'s picture

@RDsa

You have to remember that machines can have multiple NICs. Thus there is a requirement to store multiple IP addresses, subnet masks and IP addresses.

These are the results of a generic HP laptop.

COMPUTER_NAME OPERATION_SYSTEM MAC_ADDR1         IP_ADDR1  MAC_ADDR2         IP_ADDR2   MAC_ADDR3         IP_ADDR3   MAC_ADDR4
8440PSGH----- Windows XP Pro   00-05-9a-3c-78-00 171962382 00-24-d7-ab-bf-e4 2852028396 b4-99-ba-ed-79-e2 3232235877 NULL

LAN, WiFi and VMware NICs quickly add up.

IP_ADDR1 will always be populated. IP_ADDR4 will be the least populated.

 

Please mark the post that best solves your problem as the answer to this thread.
Ian_C.'s picture

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.
Ian_C.'s picture

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.
BYIT's picture

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!

 

 

Ian_C.'s picture

@BYIT

Sorry, I currently don't have a SEP v12 DB available. Waiting for RU2 which was supposed to be released two days ago.

For now, have you reviewed the SCHEMA reference guide?

  1. SEM_AGENT has a field called LAST_HEURISTIC_THREAT_TIME recording the last time SONAR detected a risk.
  2. THREATREPORT seems to have info as well.

Can't find much else for now.

Please mark the post that best solves your problem as the answer to this thread.
Valera's picture

 

Hello All,

As a SQL query to the database SEPM can get a list of hardware devices included in the policy of "Application and Device Control Policies"

I cannot find a way to do it.. Any help would be really great.