Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

What is the SQL Script to query the SEM5 database to find duplicate SEP client ?

Created: 23 Jul 2012 • Updated: 24 Jul 2012 | 10 comments
This issue has been solved. See solution.

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

Comments 10 CommentsJump to latest comment

Dushan Gomez's picture

Yes in the SEPM database ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

pete_4u2002's picture

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

Dushan Gomez's picture

When I execute the following SQL script:

 

select computer_name, computer_domain_name, current_login_user
from SEM_COMPUTER
where computer_name in (
select computer_name 
from SEM_COMPUTER 
group by computer_name 
having (
count(computer_name)>1
)
)
and computer_name not like '%WorkStation%'
 
there is no way to differentiate which record is currently unique or in use by the actual SEPM, any chance to know which one is the latest record ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

Dushan Gomez's picture

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 ?

Dushan Gomez
IT Manager
VCP 4 and 5 | MCITP Exchange Server | MCTS SharePoint Server | MCP Windows XP

 

pete_4u2002's picture

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

pete_4u2002's picture

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

Ian_C.'s picture

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.
Please mark the post that best solves your problem as the answer to this thread.
Ian_C.'s picture

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.

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