Endpoint Protection

Hi,

I believe that there were issues with duplicate Server name in my SEPM database which was  caused by deploying VM from templates with SEP active in it, so I wonder if anyone can assist me with the SQL script to query the database SEM5 so that I can delete it manually from the SQL Server DB ?

This is the Tech Note about the Database schema http://www.symantec.com/business/support/index?page=content&id=TECH105256

Does Installing SEP v11.0.6 in a server (VM) and then clone the server creates duplicate entry in the DB ?

Thanks

do you mean the sep client have duplicate entries?

Yes in the SEPM database ?

select computer_name from SEM_COMPUTER where computer_name in (select computer_name from SEM_COMPUTER group by computer_name having (count(computer_name)>1))

Thanks for the reply Pete,

One question, why is the IP address and the Timestamp is in decimal ?

any reason and way to convert it into its correct format ?

When I execute the following SQL script:

the data inserted in the same format..yes there is way to calculate , i need to check..

i tried to get these columns computername, timestamp and Ip address from teh table, hope this helps

select computer_name, dateadd(s,convert(bigint,[TIME_STAMP])/1000,'01-01-1970 00:00:00') as [Time Stamp],CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256 / 256) & 0xFF as VARCHAR) + '.' + CAST((case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end / 256) & 0xFF as VARCHAR) + '.' + CAST( case when IP_ADDR1 < 0 then 0xFFFFFFFF + IP_ADDR1 else IP_ADDR1 end & 0xFF as VARCHAR) as LOCAL_IP_ADDRESS from SEM_COMPUTER where computer_name in (select computer_name from SEM_COMPUTER group by computer_name having (count(computer_name)>1))

https://www-secure.symantec.com/connect/forums/generate-report-about-virus-definition-installed-all-sep-clients-managed-sepm#comment-6155491

Hi Pete.

Thanks for the calculation to convert the IP address to text. Here is your query, using the V_SEM_COMPUTER view making yours query a little bit easier to read.

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

SELECT computer_name
 , dateadd(minute, @TimeZoneDiff, dateadd(second, [TIME_STAMP]/1000, '01-01-1970 00:00:00')) as [Time Stamp]
 , IP_ADDR1_TEXT
FROM V_SEM_COMPUTER
WHERE computer_name in (
 SELECT computer_name
 FROM V_SEM_COMPUTER
 GROUP BY computer_name
 HAVING (count(computer_name)>1)
 )



PS

• Can you tell us why the IP is stored in that format?
• Can you tell us why in the V_SEM_COMPUTER view, the IP addresses are converted to human readable format, but not the time stamp?
• I have adjusted your time stamp conversion to display current local time.

Dushan, we meet again.

 so I wonder if anyone can assist me with the SQL script to query the database SEM5

There are various scripts scattered throughout the forums. I try and keep this thread up to date.

Try this query first. If that fails, try the next three comments listed below.

My comment from 17th Jan is the same as what Mohan posted above. That will give you the last contact time. 18th Jan comment will help you find the duplicate computers and know which one to delete. You can also try this one from the 9th of Feb.

so that I can delete it manually from the SQL Server DB

Definitely not supported by Symantec. You really should only delete computer objects using the console. Once you have found the computer objects, keep the one with the green dot.

Does Installing SEP v11.0.6 in a server (VM) and then clone the server creates duplicate entry in the DB

With out preperation, YES. At the very least, you have to delete the HWID.XML file and delete the Registry keys as laid out in the article #102815 by Symantec. For SEP v12 use article 54706. You might also be interested in this article by Mithun which has been converted into tech article 173650.